By: Tim Smith | Comments (2) | Related: > 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.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
