Cannot shrink transaction log,no matter what I do.

Question

I have a database whose tlog has grown to 4.5 GB. The db is in full recovery mode and I have tried several transaction log backups coupled with DBCC shrinkfile. And it will not shrink. Does anyone have any ideas?

There are several transactions which have a status =2, but there are no active transactions in the database. I wonder why they still show up with status=2.

Answer 1

If you're not actually interested in the contents of the transaction log, run the command

BACKUP LOG dbname WITH NO_LOG

and then run DBCC SHRINKFILE

Edit : didn't realise 2008 had removed those - we're only just switching to it. In 2008 you have to temporarily set the recovery model to simple, then run DBCC SHRINKFILE, then put the recovery model back to Full again. Code here :

http://www.uhleeka.com/blog/2009/08/sql-2008-shrink-log-file-size-with-no%5Flo/

Answer 2

You most likely have one of the following:

• an uncommitted transaction
• an orphaned transaction
• a long running operation (like an index defrag/rebuild, create index, checkdb, long running query, etc.)
• if you are using replication, unreplicated transactions

There are some other possibilities as well, but this kb article outlines most/all of the possible reasons and how to determine if/where/what they are, along with some additionally good information from this kb article and this kb article (this last one is a bit outdated, but most still applies).

Answer 3

• Use DBCC OPENTRAN to get open transactions
• How big is the MDF? if it's 5GB or above, I'd leave the log file
• Perhaps the log file needs to be his big
• When it grows, it will fragment again
• Have a look at Paul Randall's site. He wrote a lot of the t-log code...

Finally, I would consider attach/detach to remove the log file if you are really stuck. However, this is only if you're desperate...

Answer 4

If SQL, then take a full backup of the database then backup the transaction log then shrink the database. For some reason it like to have the full backup before truncating the log. You might be able to get away with a differential but see if the first part works.

Answer 5

We had a job writing to the database from another linked server. It was doing some huge deletes. We optimized that job, and were able to shrink the log file successfully down to 100MB.

Thanks!