It is very common to find a post in a forum about someone not being able to shrink their log file. If you are a regular to the SQL Server Central forums you probably
have seen this quite often too. Not too long ago I found someone with the same question, so I thought “that should be very easy to fix”, right? wrong. I
started by asking in which recovery model was his database and he told me in Full recovery model, so I was quite confident that the problem would be solved by taking a transaction log backup not a full backup, contrary to what many people believe the full backup does not truncate the log file of a database, so if your database is in Full recovery model and you have never taken a transaction log backup, your log file has not being truncate, don’t take my word for it check this link (http://technet.microsoft.com/en-us/library/ms189085.aspx ). After taking the transaction log backup I asked him how much free space he had on his log file, to check the
space used on the log file you can use DBCC SQLPERF (LogSpace). To my surprise after the transaction log backup the log file was not truncated. So, what next?
After unsuccessfully try to truncate the log file by taking a transaction log backup I asked him to execute DBCC Loginfo (‘the_name_of_his_database’), for more info on this check this link(http://sqlserverpedia.com/wiki/Maintenance_DBCC_Commands#DBCC_LOGINFO ). The result of executing DBCC Loginfo will show if the VLFs (Virtual Log Files) were in use. As expected the Virtual Log Files were in use. Next step was to figure out what was happening and for that I asked him to execute SELECT name,log_reuse_wait_desc from sys.databases where name=‘the_name_of_his_database’ to findout why the space in the log could not be reused. For more info about log_resuse_wait_desc check these links (http://msdn.microsoft.com/en-us/library/ms178534.aspx, http://support.microsoft.com/kb/317375, http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/516f5877-e283-4042-968c-6d664ad6ecf8). The results of the query gave me the clue to solve the problem. On the column log_resuse_wait_desc the value was “Replication”. So, what happened?
There was no replication going on, but the log_resuse_wait_desc value was “Replication”, to solve this a new publication was created on the database on question and then the replication was completely removed. After that I ask him to take a transaction log backup and then shrink the file. He was able to shrink the file. Before you just go ahead and start shrinking all your log files there are a few things I would like to say:
1- Shrinking your log file should be done in situations where there are no other options. By shrinking your log file you will create fragmentation on the log file.
2- If you have a database in Full recovery model, make sure you are taking transaction log backups frequently enough to avoid the file to grow.
3- Make sure to check the growth of your files and don’t set your files to grow to an unlimited size. Keep track of the data growth and make sure you give the files a proper
size.
4- Use the instant file initialization on your data files, Log files can’t use this feature. You can find more about this feature in this blog
http://sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx.
5- Please take the time and check these links:
http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
http://technet.microsoft.com/en-us/magazine/2008.08.database.aspx
I hope this helps! Do not hesitate to contact me if you have any comments or issues. 😉
IgnacioSalom.com 