Shrink MSSQL server log file

24.6.04

My project database log file size is more than 2700MB.
Oh....... my superivor face is in strange color.
So trying to shrink it but always get the error:

"Cannot shrink log file 2 because all logical log files are in use"

Finally, Today I get a hints from google :P

USE pubs
BACKUP LOG pubs WITH TRUNCATE_ONLY
DBCC SHRINKFILE(pubs_log, 2)


More details in Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE


Comments:
Hey Gloria,

Thanks very much for the sql script.
I had exactly the same problem, except the size of the log file... It was 9GB at the moment!
Thanx again!
Paul

posted by Anonymous Anonymous : 1/28/2005 11:59 PM
Gloria,

Thanks for your post! Yours was ranked #1 on ask.com and you were right. Also, thanks for the reference in your blog to the MS site.

posted by Anonymous Anonymous : 8/25/2005 7:11 PM
Great tip!!
It sure did the trick for me, reducing my log from 10GB+ to 2MB.
:D very happy. Thank you very much!

posted by Anonymous Anonymous : 10/20/2005 9:05 PM
Hi Gloria,

Good tip, but failed the first time I tried it. The messages indicated that the log was still open somehow. A little more digging turned up the command

dbcc opentran('pubs')

which lists any open transactions.

We found the process that was holding the log open, stopped it (it was quite old) and then the dbcc shrinkfile worked correctly.

posted by Anonymous Anonymous : 11/16/2005 7:03 AM
Thank you Thank you Thank you!
This is the only place I found this instruction in lots of web searching, and it got me out of serious trouble. My transaction log was 14 gb with free disk space of 4 gb.

posted by Anonymous Anonymous : 11/16/2006 3:24 AM
Post a Comment

<< Home