asked
Shrink log file in transaction log process
Hi, I am doing maintenance on my primary server, rebuilding and reorganizing tables. I am doing it parts. Last night I rebuilt an index took an hour. The ldf file grew from 20gb to 70gb and is currently 99% empty. The database is in transaction log-shipping mode. I can see that since then the backup of the log files takes much longer and I am afraid it's because the ldf file is bigger even though it's empty. I want to shrink the ldf on the primary server. Can I do it or will it destroy the transaction log shipping? If I can do it just with TruncateOnly or NoTruncate, from what I found, it seems I can shrink but just with NoTruncate, but would this help me at all?
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
In my opinion, and there are several opinions around on this, the answer is no. Leave it alone. The T-Log size should be adequate to handle what is thrown at it. What is more evident to me is that you are probably not performing regular maintenance on the database such as index defragmentation. The indexes need enough space in the T-Log to be able to rebuild the full index which is about 1 1/2 times the size of the index. This may not always be needed but the catch is that when you shrink the T-Log and it needs more space things slow down while the T-Log grows by the amount specified in the properties for the files. In this case you grew by 50GB. Lets say that you are set to grow by 10% each time that the file needs to autogrow. The first time it grew by 2GB. That filled up and needed to grow again, by 2.2GB. This continued until the job either finished or you run out of disk space. Additionally, every time the TT-Log grows, the number of VLF's increases. This too becomes a performance issue and has an impact (here's a great blog on VLF's:
http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/ or
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/). Now this will at some point need to be cleaned up, and could be painful. In the meantime review your maintenance policies. Here are a couple of links to help you with that:
http://www.sqlskills.com/blogs/kimberly/database-maintenance-best-practices-part-i-clarifying-ambiguous-recommendations-for-sharepoint/
http://justgeeks.blogspot.com/2012/07/sql-server-maintenance-plan-best.html
I would avoid constantly messing about with the log size but, once you've finished these unusual operations, I say shrink the log back to its normal size. The shrink will be log shipped over to the standby database and will cause the log over there to shrink too. (I couldn't find any details about this when I had the same issue so I just tried it and it worked perfectly.)
Hi Bernard I did it like this: USE [database_name]; GO DBCC SHRINKFILE (N'logical_log_file_name' , 2048); GO
21 People are following this question.
