Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Check SQL Server Virtual Log Files Using PowerShell

MSSQLTips author Edwin Sarmiento By:   |   Read Comments (7)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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


Last Update: 10/9/2012


About the author
MSSQLTips author Edwin Sarmiento
Edwin Sarmiento works as a SQL Server DBA for The Pythian Group in Ottawa and is a SQL Server MVP.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Tuesday, October 09, 2012 - 7:36:22 AM - Ameena Read The Tip

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

 

 


Tuesday, October 09, 2012 - 10:39:37 AM - bass_player Read The Tip

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 09, 2012 - 11:20:17 AM - Ralph Read The Tip

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.


Wednesday, October 10, 2012 - 12:37:50 PM - bass_player Read The Tip

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


Thursday, October 11, 2012 - 3:48:24 AM - alzdba Read The Tip

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


Thursday, October 11, 2012 - 10:23:41 AM - bass_player Read The Tip

Johan,

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


Wednesday, September 18, 2013 - 5:48:55 PM - dbaNick Read The Tip

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
}

}



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.