question

Neel2012 avatar image
Neel2012 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!
shrink-log-file
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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!
1 Like 1 ·
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.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you SirSQL. Yes, infact we do have CDC enabled on this database. So, if it is just like replication for log reader, how do I now deal with this outrageous tran log growth and shrink it back to meaningful size? Regards.
0 Likes 0 ·
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
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
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/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.