Check SQL Server Virtual Log Files Using PowerShell

By:   |   Updated: 2012-10-09   |   Comments (8)   |   Related: 1 | 2 | 3 | 4 | > PowerShell


Problem

In a previous tip on Monitor Your SQL Server Virtual Log Files with Policy Based Management, we have seen how we can use Policy Based Management to monitor the number of virtual log files (VLFs) in our SQL Server databases. However, even with that most of the solutions I see online involve the creation of temporary tables and/or a combination of using cursors to get the total number of VLFs in a transaction log file. Is there a much easier solution?

Solution

We SQL Server DBAs know the importance of monitoring the number of VLFs in our transaction log files and the need to keep them well maintained. All of the approaches provided online use the undocumented command DBCC LOGINFO to check the number of VLFs in a transaction log file. But the combination of temporary tables and/or cursors just to get this information is sometimes overwhelming. Take, for instance, this sample code that consists of 50 lines of T-SQL code, including the comments and spacing. Thankfully, we have PowerShell available for us to use with SQL Management Objects (SMO) to make this task a bit simpler. While we do not have an equivalent method in SMO for the DBCC LOGINFO command (after all, it is still undocumented), we can take advantage of the ExecuteWithResults method of the Database class.

Let's see how we can simplify this task by using PowerShell and SMO. We'll start by loading the appropriate .NET assemblies used by SMO as defined in this previous tip. 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

Next, we'll create an instance of the Server class to represent the SQL Server instance that you will be connecting to, passing the SQL Server instance name. I will be assigning the results to a variable named $serverInstance so we can access the different properties and methods of the Server class. 

$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "InstanceName"

We will, then, iterate thru all of the databases in the SQL Server instance by accessing the Databases property of the Server class. This property represents a collection of database objects defined in the SQL Server instance we've specified. While iterating thru the collection of databases, we will use the ExecuteWithResults method of the Database class to execute the DBCC LOGINFO command in the context of the current database. The results of this method is a .NET object type called a DataSet which is commonly used in ADO.NET. A DataSet is an in-memory cache of data with a collection of DataTable objects. Think of it as an in-memory version of a database with different tables, columns, relationships, etc. If there are multiple tables in the resultset, we can access each table using an index in an array. However, since the results of the DBCC LOGINFO command is a single resultset that mimics a table, think of the results of the ExecuteWithResults method as an in-memory table.

The PowerShell code to accomplish all of these tasks is shown below. 

foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC LOGINFO").Tables[0]
}

In order to access the table results, I used the zero index since I did not explicitly define the name of the DataTable that will hold the results of the DBCC LOGINFO command. The results of running the script are shown below.

Check SQL Server Virtual Log Files Using PowerShell

Notice that the results are similar to running DBCC LOGINFO using T-SQL. In order to find the number of VLFs, we need to count the number of VLFs per transaction log file. This is where the temporary tables and/or cursors start appearing in most of the solutions we see online. Fortunately, PowerShell has the Measure-Object cmdlet to generate statistics (sum, average, count, minimum and maximum values) for an object. We can use the Measure-Object cmdlet to count the number of items returned by the ExecuteWithResults method for a particular database. To make it easier, we will pipe the results of the ExecuteWithResults method to the Measure-Object cmdlet. 

foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC LOGINFO").Tables[0] | Measure-Object
}

We can use the Measure-Object cmdlet to count the number of items returned by the ExecuteWithResults method

If you look at the new output from the script, you can now see a property named Count that maps to the result returned by the ExecuteWithResults method of the Database class. This refers to the number of VLFs for a specific database. However, we still don't know the database for which this value is associated with. In order to do that, we will define the name of the database as part of the result using the Select-Object cmdlet, again, piping the results to this cmdlet. 

foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC LOGINFO").Tables[0] | Measure-Object | Select-Object $db.Name, Count
}

you can now see a property named Count that maps to the result returned by the ExecuteWithResults method of the Database class

The result now displays what I want - the name of the database and the number of VLFs for that particular database - in just 7 lines of PowerShell code (I can even write this in 3 lines and have the entire foreach loop in a single line but that would be annoying and hard to read) that does not use temporary tables and/or cursors. You can even format the results in a tabular format using the Format-Table cmdlet, save the results in a text file using the Out-File cmdlet, query the results if there are any values higher than 50 using the Select-String cmdlet, send an email alert if it finds one using the Send-MailMessage cmdlet, etc. I could go on and on and list the possibilities that you can do with Windows PowerShell and SMO.

Here is the complete code listing:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "InstanceName"

foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC LOGINFO").Tables[0] | Measure-Object | Select-Object $db.Name, Count
}
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-10-09

Comments For This Article




Tuesday, January 6, 2015 - 6:10:28 PM - Darek Back To Top (35848)

I'm a bit late here but... I'll explain why some people don't get the name of the database in the column. It's because Select-Object selects PROPERTY VALUE from an object that's piped to it and does it by using the PROPERTY'S NAME. There is, for instance, no property named 'model' on the object that Measure-Object (alias 'measure') produces. Hope this helps. Programming is like mathematics - you have to be really strict and rigorous to understand.

Wednesday, September 18, 2013 - 5:48:55 PM - dbaNick Back To Top (26849)

All,

 

I too was having problems with the formatting, especially when piping in a list of instances. Just list the servers in a text file (mine is C:\storelist.txt) then run this and watch it fly. Here is the script I came up with, this is the first powershell script I have ever modified, let me know if you have any input or suggestions.  Thanks Edwin for the script!

 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
cls
ForEach ($instance in Get-Content "C:\storelist.txt")
{

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

forEach ($db in $s.Databases)
{
$results = $db.ExecuteWithResults("DBCC LOGINFO").Tables[0].rows.count
$db | Select @{Name="InstanceName";Expression={$s.Name}}, @{Name="DBName";Expression={$db.Name}}, @{Name="Count";Expression={$results}}#Select-Object $s.Name as instanceName, $db.Name
}

}

Thursday, October 11, 2012 - 10:23:41 AM - bass_player Back To Top (19876)

Johan,

I'm like you - a lazy DBA - so I try to find ways to make my life easier :-)

Thursday, October 11, 2012 - 3:48:24 AM - alzdba Back To Top (19866)

Always nice to see there's more than one way to skin a kat :-)

 

Until recent I've always used the T-SQL approach, just running the actual script collecting data for all db and then returning the set to PoSh.

What you know is what you use, isn't it.

It's only recent since I switched to using the SMO approach, which IMHO is way more easy, once you find stuff is covered by SMO.

Remarkably the same code, trying to put it all in the fewest lines of code.

 

Thank you for sharing your version.

 

Johan

Wednesday, October 10, 2012 - 12:37:50 PM - bass_player Back To Top (19856)

Can you try it with just one database and see what you get? Replace the foreach loop with this code. 

$db=$serverInstance.Databases["Northwind"]

$db.ExecuteWithResults("DBCC LOGINFO").Tables[0] | Measure-Object | Select-Object $db.Name, Count

The result should look something like this

Northwind                                                                                                                            Count

------                                                                                                                            -----

                                                                                                                                    113

where the first column is the name of the database and the second column is the VLF count

Tuesday, October 9, 2012 - 11:20:17 AM - Ralph Back To Top (19833)

I am seeing the same results as Ameena.  My output is:

databaseName                                                                                                        Count
---------------                                                                                                          ------
                                                                                                                             24
                                                                                                                             14
                                                                                                                              4
                                                                                                                             71
                                                                                                                            168
 
Where "databaseName" is the actual name of the first database ($serverInstance.databases[0].name).  Only one database name is displayed and it is displayed as a header.  There is no "name" value for master, model, msdb, or tempdb.

Tuesday, October 9, 2012 - 10:39:37 AM - bass_player Back To Top (19831)

Hi Ameena,

If you look at the graphic, the results appear in this format

databaseName:

Count: NumberofVLFs

It's the same result I am getting when running the script on another environment I am managing. From the example provided, the AdventureWorksDWDenali database has 3 VLFs, the master database has 7, the model database has 4 and the msdb database has 16.

Tuesday, October 9, 2012 - 7:36:22 AM - Ameena Back To Top (19829)

Hi Edwin,

When I tried to run the completed script (just changing the instancename), I got the output with 2 columns. One on the left listed the first databases name as the column header and nothing under it. The second column named count has all the counts. But still I did not know which database has which count. Could you please check the code. It is very useful script and I can sure use it in my environment. Thanks for sharing and writing this post.

Thanks

Ameena

 

 














get free sql tips
agree to terms