September 15, 2011 at 10:02 am
A database had a log file about the size of 1.8G
I ran the following script to shrink the log file to 64MB( the database was in SIMPLE recovery mode)
USE MyarchiveDB
GO
DBCC SHRINKFILE (MyarchiveDB_Log,64);
GO
Then I ran the dbcc loginfo command against the MyarchiveDB to see the count of the VLFs and the count went down from more than 200 to 113. I was thinking it should be somewhere around 30 - 50 ( I don't have knowledge on this and that assumption was based on some reading I did.)
Thanks for your help!
September 15, 2011 at 10:48 am
My guess is your log is set to auto-grow in 10% chunks, and it started at 1 Mb. So the first time it grew it went to 1.1 mb, creating 4 VLFs in the process. It the grew to 1.21 (1.1 + 0.11), another 4 VLFs! And so on.
What you need to do is shrink it as small a possible, and then grow it to 64 mb in one step. You will then have 8(?) VLFs. This info is from Kimberly Tripps blog. http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
One thing to ask is why it was 1.8 Gb if it is in simple mode. Don't forget, everything is logged, even in simple mode. It may be that long running transactions need that amount of log space. If so, you may want to grow in the log to 2 Gb in one step, as referenced in the article mentioned before.
HTH
Dave J
September 15, 2011 at 11:26 am
David Jackson (9/15/2011)[/b]
What you need to do is shrink it as small a possible, and then grow it to 64 mb in one step. You will then have 8(?) VLFs. This info is from Kimberly Tripps blog. http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
One thing to ask is why it was 1.8 Gb if it is in simple mode. Don't forget, everything is logged, even in simple mode. It may be that long running transactions need that amount of log space. If so, you may want to grow in the log to 2 Gb in one step, as referenced in the article mentioned before.
HTH
Dave J
Thank you for your response. Yes this is an archive database and end of the month we have heavy INSERTS in this db. However, I think that this log file was never shrunk so it kept on growing to be 1.8GB. I will proceed as you suggested. I will shrink it to the smallest as possible and then grow it to may be 1GB.
September 15, 2011 at 11:32 am
I was able to bring down the count to 12. Now I am keeping the log file size to approx 1GB and used space is .05% . Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply