Transaction Log will not shrink...

  • I have a situation that appeared over the last week or so...

    I have a job that backs up the transaction log of my database and ships the log off to the redundant server.

    After the transaction log backs up, it should decrease in file size significantly. And it was doing this in the past.

    For some reason today when the transaction log backs up, it still has a large transaction file.

    Is there any way I can force it to shrink? What would prevent it from shrinking?

  • See books online about shrinking databases

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_462b.asp

     

    look in books online for

    dbcc shrinkdatabase

    or

    dbcc shrinkfile

  • This is the transaction log file, not the actual database files.

    I know the syntext to shrink a file. I need to know what would prevent the log file from shrinking?

  • About the only thing I can think of is an open transaction.

    shrinkfile pertains to the log also.

    the shrinking of the log file usually only takes the "Free" contiguous pages at the end of the file.

    Sometimes there are not many "Free" pages , so you need to run shrinkfile to force sql to reorganize the pages.

  • I recently had a similar experience where I had made a copy of an 8Gb database for a demonstration and needed to anonymise the data. After a heavy session of updates, I ended up with an 8Gb log file! This wouldn't shrink no matter what I tried, whether using dbcc shrinkfile or shrinkdb. I tried checkpointing, dumping the log but nothing worked. I even detached the database and reattached it to roll back any open transactions that I hadn't detected. Nothing worked.

    I ended up by starting over with a fresh copy, and the problem didn't occur second time around!


    Tony

  • I have also had isues with this. In a pinch I have backedup the database then chenge the database to simple recovery mode then back to full and it cleared the log. THis is not a recomended practise but I was on a test server and in a hurry.


    Stacey W. A. Gregerson

  • I couldn't get it to shrink even after playing around with different settings of the recovery mode!


    Tony

  • Hi,

    try:

    sp_helpfile

    CHECKPOINT

    --declare @cmd varchar(999) select @cmd= 'backup log '+DB_NAME()+' with truncate_only' exec (@cmd)

    dbcc shrinkfile (DB_Log, 3072)

    if that does not work uncomment the backup-line and try again.

    if you need all transactions do a "backup with truncate" instead of a "backup with truncate_only"...

    If that does not work you might use the script in

    http://www.sqlservercentral.com/scripts/contributions/26.asp

    regards karl

    Best regards
    karl

  • I tried backup with truncate_only and backup with nolog (which I think does the same anyway) but neither helped!

     

    Thanks for the suggestion though. I will go and checkout that script now.


    Tony

  • Sometimes you might need to insert few dummy records in your DB before shrinking....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply