Using PowerShell To Record SQL Server Virtual Log File (VLF) Growth

By:   |   Updated: 2016-01-27   |   Comments (2)   |   Related: More > PowerShell

Problem

We have several hundred servers and want to know if there is a simple way in PowerShell to obtain the VLF (virtual log file) counts in all of the databases on our servers. We want to keep a record of these counts and track over time, especially after we make configuration changes.

Solution

To obtain the VLF count for a log, we can run DBCC LOGINFO and return the count of VLFs from the output, and from there save the records in a file, which we can automatically import into a table if we choose (or by using the third function provided in this tip and directly save the count to a table). Since the VLF information can change due to log growth and changes, the best approach would be to keep records of this information periodically, especially on high use servers. If performance is impacted, the history will help us ascertain how much it may have contributed to the issue.

In the first script, we'll loop through the databases on each server using the management objects (smo library), and call the second function which reads the count of records from DBCC LOGINFO, saving the output to a text file, delimited by commas.

Function Loop-Databases {
    Param(
        [ValidateScript({Test-Path $_})][string]$smolibrary
        , [ValidateLength(4,25)][string]$server
    )
    Process
    {
        Add-Type -Path $smolibrary

        $srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
		
		### Note that I don't apply a filter here using Where-Object, but could apply one to remove databases that I may not want to check
        foreach ($database in $srv.Databases)
        {
            Get-VLFCount -vlfserver $server -vlfdatabase $database.Name -vlffile "C:\files\vlfs.txt"
        }
    }
}


Function Get-VLFCount {
    Param(
        [ValidateLength(4,25)][string]$vlfserver
        , [ValidateLength(4,25)][string]$vlfdatabase
        , [ValidateLength(12,55)][string]$vlffile
    )
    Process
    {
        $vlf_con = New-Object System.Data.SqlClient.SqlConnection
        $vlf_con.ConnectionString = "Data Source=$vlfserver;Initial Catalog=$vlfdatabase;Integrated Security=true;"
        
        $vlf_cmd = New-Object System.Data.SqlClient.SqlCommand
        $vlf_cmd.Connection = $vlf_con
        $vlf_cmd.CommandText = "DBCC LOGINFO"
        $vlf_cmd.CommandTimeout = 0

        [int]$vlfloop = 0
        
        try
        {
            $vlf_con.Open()
            $countvlfs = $vlf_cmd.ExecuteReader()
    
            while ($countvlfs.Read())
            {
                $vlfloop++
            }
        }
        catch [Exception]
        {
            Write-Warning "Get-VLFCount"
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $vlf_cmd.Dispose()
            $vlf_con.Dispose()
        }

        if ((Test-Path $vlffile) -eq $false)
        {
            New-Item $vlffile -ItemType File
        }

        Add-Content $vlffile "$vlfserver,$vlfdatabase,$vlfloop"
    }
}

### The SMO library may differ in location, relative to the SQL Server version
$smo = "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

Loop-Databases -smolibrary $smo -server ""

When checking for the VLF count, we may want to look at servers with a number over (or under) a certain amount, so we can wrap some if logic around adding the number to the file to filter out the log files with fewer (or too many) VLFs. In the below example, I filter out all the logs that have fifty or fewer VLFs:

### Give me all the databases that have more than 50 VLFs in their log
if ($vlfloop -gt 50)
{
	Add-Content $vlffile "$vlfserver,$vlfdatabase,$vlfloop"
}

Finally, if we want to save the information directly to a logging database on another server with a time stamp, the below functions will do this:

Function Loop-Databases {
    Param(
        [ValidateScript({Test-Path $_})][string]$smolibrary
        , [ValidateLength(4,25)][string]$server
        , [ValidateLength(4,25)][string]$logging
    )
    Process
    {
        Add-Type -Path $smolibrary

        $srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
		
		### Note that I don't apply a filter here using Where-Object, but could apply one to remove databases that I may not want to check
        foreach ($database in $srv.Databases)
        {
            Get-VLFCount -vlfserver $server -saveserver $logging -vlfdatabase $database.Name
        }
    }
}
	
Function Execute-Sql {
    Param(
        [ValidateLength(4,25)][string]$server
        , [ValidateLength(4,1000)][string]$command
    )
    Process
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "Data Source=$server;Initial Catalog=master;Integrated Security=true;Connection Timeout=0;"
        
        $cmd = New-Object System.Data.SqlClient.SqlCommand
        $cmd.Connection = $scon
        $cmd.CommandTimeout = 0
        $cmd.CommandText = $command

        try
        {
            $scon.Open()
            $cmd.ExecuteNonQuery()
        }
        catch [Exception]
        {
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $scon.Dispose()
            $cmd.Dispose()
        }
    }
}


Function Get-VLFCount {
    Param(
        [ValidateLength(4,25)][string]$vlfserver
        , [ValidateLength(4,25)][string]$saveserver
        , [ValidateLength(4,25)][string]$vlfdatabase
    )
    Process
    {
        $vlf_con = New-Object System.Data.SqlClient.SqlConnection
        $vlf_con.ConnectionString = "Data Source=$vlfserver;Initial Catalog=$vlfdatabase;Integrated Security=true;"
        
        $vlf_cmd = New-Object System.Data.SqlClient.SqlCommand
        $vlf_cmd.Connection = $vlf_con
        $vlf_cmd.CommandText = "DBCC LOGINFO"
        $vlf_cmd.CommandTimeout = 0

        [int]$vlfloop = 0
        
        try
        {
            $vlf_con.Open()
            $countvlfs = $vlf_cmd.ExecuteReader()
    
            while ($countvlfs.Read())
            {
                $vlfloop++
            }
        }
        catch [Exception]
        {
            Write-Warning "Get-VLFCount"
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $vlf_cmd.Dispose()
            $vlf_con.Dispose()
        }

        $command = "INSERT INTO OurDatabase.dbo.tb_OurVLFTable VALUES ('$vlfserver','$vlfdatabase',$vlfloop,GETDATE())"

        Execute-Sql -server $saveserver -command $command
    }
}

Provided that you're not facing an immediate issue, you may want to save the information over a period of time and adjust your log settings as necessary, depending on what you find. After you resolve the issue, I suggest measuring this from time to time to make sure that you don't have another spike in VLFs, or that the settings you've configured remain the way you wanted. This isn't a strong enough category to alert on, but for maintaining a set architecture, it's useful to review on a report.

Next Steps
  • For one time checks, run the above script to output the VLF information to a file.
  • If you want to store historic information in a table, you can use the script here to import the file that is created.


Last Updated: 2016-01-27


get scripts

next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, January 27, 2016 - 6:49:23 PM - Tim Back To Top

Excellent question.

Automating the next step to correct it will heavily depend on the environment and set up.  Some companies might be extra cautious on any production change, so automating a development solution would be one thing, but not for production.  In general, I like to keep a strong eye on my growth settings when I look at this.  Normally (not always), correcting that resolves the issue.  Since I prefer to keep a history (I know it uses some resources), I can cross-check my history to see if what I choose for growth works.


Wednesday, January 27, 2016 - 12:28:10 PM - Tony Santangelo Back To Top

 
This is very cool and thanks for sharing. We do something similar via a custom SP and SQL Agent job which sends an email notificaiton once a week. So my quesiton to you is, have you ever seen anyone automate VLF remediaitons for any DB reporitng > 50 VLFs? I ask because this topic came up recently in our weekly DBA meeting since doing this manually requires it to be best done during off hours or over a weekend since you ultimately need to clear and shrink the log file.

 

Thanks,

Tony



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools