January 24, 2012 at 6:00 am
Hi - looking at one of our gold systems I can see that the logfile has 767 VLF files int he log file. Reading various articles suggest this may be an issue in SQL server, hoowever our log backups are taking a matter of seconds to complete which to me would indicate we don't have any issues.
What are your opinions? Is there a better metric to measure this performance?
Thanks
January 24, 2012 at 6:05 am
you don't say how big the log file is but 700+ is on the high side.
look at recovery times for the database on a server restart or onlining the database (if its ever offlined).
If those are acceptable you don't have an issue currently
---------------------------------------------------------------------
January 24, 2012 at 6:06 am
Here is a nice article on this.
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
January 24, 2012 at 6:07 am
"Depends" on the size of the database and its logfile. Based on average database size its likely to be on the high side.
January 24, 2012 at 6:52 am
padhis (1/24/2012)
Here is a nice article on this.http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
Hi - Yes this is what I was reading which made me query it. Our DB is 270GB with a 40GB logfile.
The last recover of the DB wasn't an issue, nor have the log backups, in light that I don't know of any other performance metrics I am guessing we don't have an issue.
January 24, 2012 at 6:58 am
just check your growth factors so that it doesn't become a problem if there is any likleyhood the log will grow further.
---------------------------------------------------------------------
January 24, 2012 at 7:38 am
Another thing that can be affected if there internal fragmentation (large number of VLF's) is replication, I'm not sure whether your db is replicated but just another consideration.
January 24, 2012 at 7:48 am
I have checked the space in the log, and there is plenty at present.
This database does get replicated to other sources, what impact could this have on the vlfs in the logfile? Would transactions take longer to replicate due to it waiting for the current vlf to fill up?
January 24, 2012 at 8:24 am
This database does get replicated to other sources, what impact could this have on the vlfs in the logfile? Would transactions take longer to replicate due to it waiting for the current vlf to fill up?
Over simplified explanation but:
The log reader agent reads through the transaction log of the database to find the transactions it needs to replicate to the subscribers. If there is a lot of VLF's then this can cause the log reader performance to suffer and you could see latency among other things.
I saw a best practice (I believe on Kimberly Tripp's blog of less than 50 VLF's unless database is huge in which case each VLF should be no bigger than 512mb)
Here a great article from Kimberly on the subject
http://sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
January 24, 2012 at 8:35 am
Full backups will take longer than they should. Log backups take longer than they should. Large transaction rollbacks may take longer than they should. Restores will take longer than they should. Database recovery may take longer than it should. The log reader will take longer than it should to pick up the replicated commands.
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
January 24, 2012 at 8:53 am
GilaMonster (1/24/2012)
Full backups will take longer than they should. Log backups take longer than they should. Large transaction rollbacks may take longer than they should. Restores will take longer than they should. Database recovery may take longer than it should. The log reader will take longer than it should to pick up the replicated commands.
So what are you insinuating? 😀
January 24, 2012 at 8:59 am
Ninja's_RGR'us (1/24/2012)
GilaMonster (1/24/2012)
Full backups will take longer than they should. Log backups take longer than they should. Large transaction rollbacks may take longer than they should. Restores will take longer than they should. Database recovery may take longer than it should. The log reader will take longer than it should to pick up the replicated commands.So what are you insinuating? 😀
That ignoring a misconfiguration because it does not appear to be causing problems may not be the best approach.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply