SQL SERVER – PowerShell to Count Number of VLFs in SQL Server

June 23, 2015

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:

SQL SERVER - PowerShell to Count Number of VLFs in SQL Server PS_VLF_Count_01

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)

Powershell, SQL Scripts, SQL Server, VLF

SQL SERVER – Evaluation Period Has Expired – How to Activate SQL Server?

SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

2 Comments. Leave new

Hany Helmy
July 1, 2015 4:47 pm
Check this out:
DECLARE @query varchar(1000),
@dbname varchar(1000),
@count int
SET NOCOUNT ON
DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE state = 0
CREATE TABLE ##loginfo
(
dbname varchar(100),
num_of_rows int)
OPEN csr
FETCH NEXT FROM csr INTO @dbname
WHILE (@@fetch_status -1)
BEGIN
–drop TABLE #log_info
CREATE TABLE #log_info
(
RecoveryUnitId tinyint,
fileid tinyint,
file_size bigint,
start_offset bigint,
FSeqNo int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)
SET @query = ‘DBCC loginfo (‘ + ”” + @dbname + ”’) ‘
INSERT INTO #log_info
EXEC (@query)
SET @count = @@rowcount
DROP TABLE #log_info
INSERT ##loginfo
VALUES(@dbname, @count)
FETCH NEXT FROM csr INTO @dbname
END
CLOSE csr
DEALLOCATE csr
SELECT dbname,
num_of_rows
FROM ##loginfo
WHERE num_of_rows >= 50 –My rule of thumb is 50 VLFs. Your mileage may vary.
ORDER BY num_of_rows DESC
DROP TABLE ##loginfo
Reply
PAdmasree
May 30, 2018 1:37 pm
Can someone help me powershell script to check vlf count on a single DB
Reply