views:

305

answers:

5

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.

+1  A: 

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/

CodeByMoonlight
Both commands WITH NO_LOG and WITH TRUNCATE_ONLY have been removed in SQL Server 2008.
sharadov
Updated with other solution.
CodeByMoonlight
I tried that, did not help.In fact the database was in simple when the log got really large because of a data import.So I switched to full, did a full and tlog backup, and then ran a dbcc shrinkfile, all this did not help.
sharadov
+1  A: 

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).

chadhoc
+2  A: 
  • 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...

gbn
Beware: Detaching the database, deleting the log file, then trying to attach just the MDF and rebuilding the log could leave your database in an inconsistent or even corrupt state.
Aaron Bertrand
@Aaron: I know, but OP seems adamant...
gbn
+1  A: 

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.

Middletone
Do not use DBCC SHRINKDATABASE or the shrink database option in the UI. If you need to shrink an individual file, you are much better off using DBCC SHRINKFILE explicitly. See Tibor's site: http://www.karaszi.com/SQLServer/info_dont_shrink.asp also see the links here and the discussions : http://is.gd/4TnHZ
Aaron Bertrand
A: 

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!

sharadov