March 7, 2011 at 9:19 am
Hello
I am having an serious issue with VLF's. I have a OLTP database about 100GB in size, I followed all the instructions that I could find in this forum to reduce the no. of VLF's. I set the initial size of the TranLog file to 16GB and to auto grow in 8GB increments. At that time the no. of VLF's were about 80 which was still ok although my target was under 50.
However, the same night there was a weekly Index maint job which increased the size of the log file to 36 GB and the no. of VLF's now are a mind blowing 112893 :w00t:
This has also affected my log shipping setup and now for some reason the log shipped database is more than 12 hours behind. The error log says:
Message
The log shipping secondary database CU1-ASIDB.ASIDb has restore threshold of 45 minutes and is out of sync. No restore was performed for 18 minutes. Restored latency is 926 minutes. Check agent log and logshipping monitor information.
I tried to do manual restores on the log shipped database and it is complaining about some problems with the last restore on the LOG file.
Please help!!!
Thanks
Anish
March 7, 2011 at 10:08 am
Be specific about the restore error.
As for the VLFs, it's apparent that the log needs to be 36 GB in size to support the maintenance, so shrink it to 0 and then grow it back to 36 GB in reasonable chunks to get the VLFs you want.
Also, 8GB auto grow seems a little large, that's going to take a relatively long tme to grow, and the DB operations wil have to wait until the grow finishes if they need to log. May I suggest a more reasonable autogrow setting?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2011 at 11:16 am
Hello Gail
Thanks for your help on this issue. So, I followed the instructions from the following link:
http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
And shrunk the log file of the said DB and then grew it to 8 GB, then 16, 24, 32 and ultimately 40 GB. The no. of VLF files in the LOG file are now 110. I also reset the autogrowth to 10% instead of a fixed 8GB. I am hoping that the log file does not need to grow but, one never knows.
I am actually running Database Mirroring, as well as Log Shipping on the same database. The mirror lies on the same LAN whereas the log shipping occurs over a WAN. Currently, I am running a script which re-builds (online= on, if possible) all indexes with a fragmentation of 30% or more and reorganizes everything which is more than 10%. This occurs twice a week as per schedule and obviously generates a lot of logging. Is there any way I can minimize the logging while not breaking the Mirror or Log Shipping. If you can guide me to any resources which can help me with above issues, I would be very grateful...
As far as the log shipping end is concerned, the shipping broke around the time when those huge logs were generated during one of the reindexing jobs and it seems it wasn't able to catch up since then. I am taking a look at the 'Log Shipping Monitor' report and although I can see the 'Last Restored Column' of the said database being updated each iteration of LS_Restore (every 15 minutes) its just not catching up. Although, all the tran log files are being copied to the server. Also, the LS Restore job seems to be running continously. I have log shipping enabled for a few other databases also on the same server and they seem to be fine. Any ideas on what I could do?
Once again thanks for your response and sorry for the really long post.
Regards
Anish
March 7, 2011 at 11:50 am
Don't use % growths. Gets you into big trouble if there's unexpected growth as the growth size increases as the file size increases. Chose a fixed value that's sensible, large enough to not get massive numbers of VLFs, small enough not to impact performance if the growth happens
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2011 at 6:39 pm
Thanks for your help on this issue. So, I followed the instructions from the following link:
http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
Did you see the follow-up link on that page, to this: http://sqlskills.com/BLOGS/PAUL/post/Bug-log-file-growth-broken-for-multiples-of-4GB.aspx
Looks like you hit the bug with setting t-log growth to increments of 4 GB.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 8, 2011 at 8:14 am
Hello Folks!
Thank you for your responses. I managed to also solve the issue with the log shipping problem.
Since, there were a lot of VLF's in my Tran Log, the log restore on the remote server is taking almost 18 minutes per restore on the remote server. There are 4 tranlog backups files per hour so, there is a long way to go until I managed to hit the tran log backup after I managed to reduce the VLF's thanks to Gail's post. But, eventually, it will catch up 🙂
Regards
Anish
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply