Post reply

Can't shrink transaction log on sql server 2005 Standard edition, 64 bit

  • July 3, 2014 at 1:22 pm

    #287571

    Hi. We have a DB on a sql server 2005 server that is 600MB data and 242 GB log, but I can't shrink the log, even after doing a backup tran of the log.

    The dbcc shrinkfile(logfilename) doesn't give an error, just doesn't work.

    This is a production server, so I don't want to set the recovery to simple, unless there is no other way.

    The DB options are:

    Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics

    Please advise on any suggestions to free up space. Thanks.

  • July 3, 2014 at 1:34 pm

    #1726949

    What does log_reuse_wait_desc say?

    Can you run the below stmnt and see what it says?

    SELECT log_reuse_wait_Desc FROM sys.databases

    WHERE name = 'Your_DB_name'

  • July 7, 2014 at 6:19 am

    #1727324

    Hi, Sreekanth.

    It's set to LOG_BACKUP.

  • July 7, 2014 at 6:44 am

    #1727327

    Thanks very much, Sreekanth.

    I was told by the SAs that they were doing Arcserve backups on the DB, but after you replied, I checked further, and found they were only doing full backups, so the log has been growing for a very long time.

    I set the DB to simple, cleared the log, set it to full, and did a full backup, and now everything is ok.

  • July 10, 2014 at 8:37 am

    #1728416

    np...So after changing it back to FULL, have you shceduled T-Log backups?

    Make sure you have got regular T-Log Backups scheduled for any database which is in "FULL" recovery model. If not, the same issue(gigantic log file) will re-occur.

  • July 10, 2014 at 12:43 pm

    #1728493

    Hi, Sreekanth.

    Yes, the backup I said worked was the tran log backup job.

    Thanks for your help, and for verifying I didn't forget to turn on the tran log backups after clearing the log.

Viewing 6 posts - 1 through 5 (of 5 total)

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