SQL Server Performance Forum – Threads Archive
Shrinking of a log file
Hey there everyone. I was hoping someone could give me a hand with this. I have a production database that has a log file that has recently grown from around 1 gig to over 17 gigs. I did the following:‘backup log dbname with no_log’
‘dbcc shrinkfile (dbname_log)’ Then, I was going to do a full db backup and be back in business. The problem is that the dbcc shrinkfile is not shrinking the file even though the backup log with no_log ran fine. At this point I thought there may have been an open transaction so, I did an ‘alter database dbname SET Restricted_User with Rollback Immediate’ and then tried the whole thing again. Still there was no change. Next, I took the database offline and then online. Still no change when I tried to shrink the file again. Has anyone seen anything like this before. Thanks,
Jeff
You need to look into your backup strategy – ideally you should have a full backup of the database at the end of the working day, and backups of the log(s) every X hours. This should keep the file size of the logs in check.
Check what kind of transactions running against that database, as suggested by Adriaan you need a strategy to control the log file growth and handle the transactions in batch modes to reduce the sizing issue. http://sqlserver-qa.net/blogs/tools/archive/2007/06/19/transaction-log-guidelines.aspx &http://msmvps.com/blogs/ssqa/archiv…ransaction-log-shrink-to-be-or-not-to-be.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks I am running full backups every day and tran log backups every hour. Today was my first day back from two weeks vacation and when I logged in I noticed that almost of all the disk space was gone for my log files and backups were failing for the last few days. Now, I’m in reactive mode rather than proactive mode. My hunch is there was a problem with the weekly rebuild of indexes. Regardless, now I’m in the situation where I have to claim back this log file space and I have not been able to. Any help would be appreciated. Again thanks.
Thanks again to both of you guys. I actually understand the importance of the backup stategy and how the tran logs work. I just don’t understand why/what would be causing me not to be able to shrink the file in this specific case. Normally, I do not have to do this as the stategy has been fine up until now. Once I get the space back I plan to run addional tran log backups during the rebuild of the indexes. Thanks again.
Don’t you have any scheduled jobs to take care such activities?
If you can sustaing the downtime then simply take full backup, detach the database and re-attach using SP_ATTACH_SINGLE_FILE_DB to create afresh log file. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
You can also consider moving the log file to a different disk with enough space Satya
Great thanks
]]>