asked
Transaction Log not shrinking
Hello Everyone: I am a DBA. And, having issues with Transaction Log not shrinking. My user db that has issues, is with Full recovery model and I have full, dif and log backups running on schedule for it. It won't let me manually shrink the transaction log either. We are on SQL Server 2008 R2, what do I need to do to manage these transaction logs so that they shrink to an minimum size. Thank you for your feedback in advance!
It turned out that our cdc capture job failed and never got restarted. The cleanup job was still running on daily schedule. It caused the log to keep growing and not freeing up any space. I am currently running the cdc and the log is freeing up space now. Thank you again for all your wisdom and feedback. Have a great weekend!
Thanks for your feedback. Here is more information. On this DB, file data file size is 51200 MB and Log is 45962 MB (98.1% used and only 873 MB is shown free). Since the DB model is full and it is heavy transaction environment, I have tran log backups running every 30 min. I found out that the log_reuse_wait_desc is "Replication" and we don't have any replication setup on this database/server. Is there a way to find out when the replication was setup and any more information on it? Also, how to deal with this situation now. This growing tran log is becoming a more serious issue now. I currently don't have any thing scheduled for any auto-shrinking the log, currently it won't even let me manually shrink the log. Since the log backups are running every 30 minutes, my understanding is that it should reuse/reset all that space without letting the tran log keep growing. Again, your feedback and wisdom is greatly appreciate. Thank you.
First check that you don't have CDC enabled (Change Data Capture uses the log reader just like replication). Then double check that replication isn't happening. If you are 100% sure you are good then you can run [sp_removedbreplication][1] to get rid of replication on that database. That should clear things up allowing you to get that (extremely) large transaction backup job done allowing you to shrink the log. [1]:
http://msdn.microsoft.com/en-us/library/ms188734.aspx
Check to see that your change data capture cleanup jobs are running, and check to see what the oldest CDC record you have is. If it's less than three days old then you'll need to either adjust your cleanup time, or deal with the larger log. Check this reference
http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx
Before you go and start shrinking the transaction log I would ask as to why you are going to shrink it. It will just grow again, causing a performance hit, and you'll be right back where you started. If you truly want to shrink it, take a look at sys.databases and see what the log_reuse_desc value is. If you are waiting for replication or a log backup then you won't be able to perform the shrink. Perform a couple of log backups and then try to shrink the log by a small amount (it would be useful to know what command you are using for the shrink). Another alternative is to find a nice quiet period for your system and alter the recovery model to simple, shrink the log, and then put it back to full followed by a full or differential backup. All in all though I wouldn't shrink the log unless it's absolutely necessary.
I'd absolutely second that. The only time I'd normally shrink the log is if I'd carried out a particularly large transaction as a one-off
please do check for open transaction on the user database, and these can be acheived by running DBCC OPENTRAN on a database. check my blog for best practices to shrink a log file
http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/
Not an answer, but a book recommendation - Tony Davis & Gail Shaw's excellent (and free from Red Gate) ["SQL Server Transaction Log Management".][1] Chapter 7 is where you probably ought to start... [1]:
http://www.red-gate.com/community/books/sql-server-transaction-log-management
17 People are following this question.
