24.6.04

Shrink MSSQL server log file

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
at

5 comments:

  1. Anonymous1/28/2005 11:59 PM

    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

    ReplyDelete
  2. Anonymous8/25/2005 7:11 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.

    ReplyDelete
  3. Anonymous10/20/2005 9:05 PM

    Great tip!!
    It sure did the trick for me, reducing my log from 10GB+ to 2MB.
    :D very happy. Thank you very much!

    ReplyDelete
  4. Anonymous11/16/2005 7:03 AM

    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.

    ReplyDelete
  5. Anonymous11/16/2006 3:24 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.

    ReplyDelete