MSFT-MSO: Document DBCC LOGINFO() or create new DMVs to view VLF info - by Narayan_Iyer

Status : 


Sign in
to vote
ID 322149 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 1/12/2008 11:28:09 PM
Access Restriction Public


I currently use DBCC LOGINFO() to view the VLF information of my database. I searched through BOL and I neither could find any documentation for this DBCC command nor any documented or officially supported way of listing the VLF information of a database. 

Sign in to post a comment.
Posted by James Anderson DBA on 1/30/2017 at 7:12 AM
This issue forces a choice of:
Giving SYSADMIN access to my monitoring accounts on all of my instances.
Manually logging in with my SYSADMIN account and manually checking each instance.
Ignoring high counts of VLFs in my transaction logs.

We've had some DMVs appear in service packs recently, I hope one appears for this. If it does Nick Craver has it right in his comment below.
Posted by Nick_Craver on 3/12/2016 at 6:37 AM
Is there any update on this? Current state: It's the only way to get the log information. It's undocumented and we have no idea if it's supported. It has changed between versions, requires looping over each database, and most importantly: requires SYSADMIN.

This one missing DMV is the last cause of needing SYSADMIN for several monitoring solutions I'm aware of, including our own Opserver. This information being available in a DMV (hopefully at the instance level) would eliminate the need for SYSADMIN permissions on monitoring accounts and make it easier to use (especially with large numbers of databases). A DMV would inherently increase security with fewer accounts having SYSADMIN; that should increase the priority.

To be clear: very few people need the stream data, if that's the security complication here. That should be ignored; only the count and size of the VLFs need be in the DMV. Those simple numbers are not security related and should be exposed via a DMV accessible with VIEW SERVER STATE permissions.

I think if people need stream data, DBCC LOGINFO() should remain the source and should still be documented. It's been over a decade, why can't we at least get documentation on MSDN for it?
Posted by GP Van Eron 万侨医 on 2/5/2016 at 2:12 PM
Anything planned for 2016 on this one?
Posted by Chris Fradenburg on 10/9/2012 at 6:04 AM
Based on the performance impact that the number of VLFs can have there really should be a documented method of getting that information.
Posted by Arvind Shyamsundar on 12/8/2011 at 12:48 AM
I voted for this, and would also like the product team to also consider incorporating it as a property of the Log File facet in Policy Management.
Posted by Microsoft on 8/4/2009 at 2:04 PM
Thank you for your comment.
We agree that this is the right thing to do for the product.
We have defined this DMV as a DCR to the product, and it is currently a pick-list item for the next release, meaning that after the large features are complete, we will pick from the list of high priority DCRs to implement as we have resources.
Posted by A.Lockwood on 8/4/2009 at 9:56 AM
This really should be visible in a DMV. It plays a role in database performance and is something that we've all got to keep an eye on. Why not help out us DBAs and make this easily viewable/trackable through a DMV?
Posted by Microsoft on 2/28/2008 at 2:19 PM
To reiterate, this functionality will not be part of the Katmai release, but is being kept as an active request for the next version.
Posted by Microsoft on 2/6/2008 at 10:08 AM
Thank you for your suggestion.
Our direction is not to document DBCC commands, but for informational queries such as this to add appropriate DMVs. I will add this to the list of DMV requests. Unfortunately it will not make the Katmai release. We will consider it for a future release.