Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2002
    Location
    Westfield, NJ, USA
    Posts
    23

    Unanswered: Gargantual Transaction Files

    Can I delete them or otherwise make them smaller?
    DB is to be read only, no deletions, no additions.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    open up Enterprise Manager, right click on the db name "All Tasks" -> "Srink Database" -> Check "Move pages to beginning" Click on the "OK" button.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Apr 2002
    Location
    USA
    Posts
    4

    Transaction Logs

    The Shrink DB taks in Enterprise Manager does work, but it does not give you much control. Also, it tends to be less than effective with regard to log files.

    there are scripts out there that you can run through query analyzer that will work very effectively in shrinking the log file.

    essentially, what you need to do is generate several small transactions on the log file. It may not be shrinking properly if there are some large transactions there. As you perform the small transactions, the larger ones will me moved down and eventually they will reside in a place where the log file will allow for them to be removed.

  4. #4
    Join Date
    Mar 2002
    Location
    Westfield, NJ, USA
    Posts
    23

    Who wants my transaction files?? FREE!

    Paul and BkBlitz2,
    thanks for your reply, but "Shrink" did not work" at all.

    First: it took some 4-5 min. to post nice message that "Database was shrinked successfully" which is less than 1/4 of the time it takes to create one lousy index on the same file which in turn tells me that nothing worth mentioning really happened.
    Second: close to 40 GB of transaction files - which is more than data itself - is still close to 40 GB.
    I don't need any of the transaction files. SQL Server assumes that I do but I am sure that I don't.
    I need the space it occupies and time it takes to access it. The DB really is "read only" and a backup of if is good enough.

    So, my (ignorant) question remains: how to shrink, delete, sell or give away my truckload of transaction files?

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I believe that when you issue a DBCC SHRINKDB only the non-active or unused protion will be shrunk. To see how much of the log can be shrunk issue DBCC SQLPERF ( LOGSPACE ) . This will return a recordset of all databases with their log file size and percentage used, so from here you should get an idea of how much it will shrink. Now you stated that this is a READ ONLY database, if true than issue BACKUP LOG <database_name> WITH TRUNCATE_ONLY. This will clean out the log. Only do this for readonly, since you won't be able to recover the database up to the minute. You would issue a BACKUP DATABASE after if this was a production database. Now try the DBCC SHRINKDB. Again since this is readonly you should then use sp_dboption with trunc. log on chkpt., so that the log will periodically truncate itself.
    MCDBA

  6. #6
    Join Date
    May 2002
    Posts
    1

    SQL SERVER SHRINK

    The only way you can handle this is to

    --1)
    use <db_name_here>
    go

    --2)
    /* truncation, i.e. cleaning old transactions stuff out of the file*/
    BACKUP LOG <db_name_here> WITH truncate_only
    go

    --3)
    /* real shrinking - does not always work for there might be an active transaction in the file, and it is somewhere at the very end*/
    dbcc shrinkfile(<name_of_logfile_here>,1)
    // note: you can find the log file name by running: select * from sysfiles

    Thanks, this will shrink log file very quickly, and possibly right to 1,024k only!

  7. #7
    Join Date
    Apr 2002
    Posts
    5

    Smile Transaction log

    Hi

    Thx for the tip, of how to backup a transaction logfile within 5 seconds to 1.024K !

    My biggest problem in develloping my data warehouse !

    Greetings

    J

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •