If you are a seasoned DBA, then the conversations and monitoring VLFs is something you are already doing. To learn the basics of VLF, check my earlier blog at: SQL SERVER – Detect Virtual Log Files (VLF) in LDF. Having a large number of VLFs has a performance impact and there are a number of blogs that discuss the same. As a DBA in this blog, I thought of showing a simple script that will help to identify the number of VLFs available inside a SQL Server instance or a number of instances.
As part of my powershell learning, this is a simple script that you can use in your environment and take corrective action if you have a large number of VLF in your servers.
<#
EXAMPLE
Get-VLFCount -ComputerName ComputerName
EXAMPLE
Get-Content D:\ServerList.txt | Get-VLFCount
If you are using Windows Authentication and have a list of servers you can use this.
Get-VLFCount -ComputerName ComputerName1, ComputerName2
#>
function Get-VLFCount
{
[CmdletBinding()]
Param
(
# SQLInstance is the name(s) of the SQL Instances to scan
[Parameter(Mandatory=$true,
ValueFromPipeline=$True,
ValueFromPipelineByPropertyName=$true,
Position=0)]
$SQLInstance
)
Begin
{
#Load the Assembly to connect to the SQL Instance.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ConnectionInfo') | out-null
}
Process
{
ForEach ($Instance in $SQLInstance)
{
$SrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$SrvConn.ServerInstance=$Instance
#Use Integrated Authentication
$SrvConn.LoginSecure = $true
$SrvConn.ConnectTimeout = 5
Write-Debug "Attempting to check $Instance for VLF Counts"
$srv = new-object Microsoft.SqlServer.Management.SMO.Server($SrvConn)
$dbs = $srv.Databases
try
{
ForEach ($db in $dbs)
{
Write-Debug "Getting VLFInfo for $db"
if ($db.IsAccessible)
{
$VLFs = $db.ExecuteWithResults("DBCC LOGINFO")
$NumVLFs = $VLFs.Tables[0].Rows.count
$VLFinfo = $db | Select @{Name='Instance Name'; expression={$Instance}}, @{Name='Database Name'; Expression = {$_.name}} `
, @{Name='VLFCount()'; Expression={$NumVLFs}}
}
else
{
$VLFInfo = New-Object psobject
$VLFInfo | Add-Member -type NoteProperty -name InstanceName ($Instance)
$VLFInfo | Add-Member -type NoteProperty -name DBName ("$DB is Inaccessible")
$VLFInfo | Add-Member -type NoteProperty -name VLFCount 0
}
Write-Output $VLFinfo
}
}
catch
{
$ex = $_.Exception
Write-Debug "$ex.Message"
Write-Error "Could not pull SQL DB Info on $Instance"
}
}
}
}
#If we had our instances in a file, we can use
Get-VLFCount 'localhost'The above script when run on my server returns the following:
Incase you have hundreds of VLF in your environments, then you can look at SQL SERVER – Reduce the Virtual Log Files (VLFs) from LDF file to reduce the number of VLFs for your databases.
Just out of curiosity, I would love to know what is the highest number of VLF in your databases? How long have these been running in your systems? It would be great if you can share the same with us over comments so that it would a great learning for me too.
Ref:ence : Pinal Dave (https://blog.sqlauthority.com)
