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

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:

Solarwinds

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Evaluation Period Has Expired – How to Activate SQL Server?
Next Post
SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

Related Posts

2 Comments. Leave new

  • 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
  • Can someone help me powershell script to check vlf count on a single DB

    Reply

Leave a Reply

Menu