Home » SQL Transaction Log » Checking SQL Server Virtual Log Files Performance Via PowerShell

Checking SQL Server Virtual Log Files Performance Via PowerShell

Andrew Jackson ~ Modified: August 6th, 2015 ~ SQL Transaction Log ~ 3 Minutes Reading

Introduction

Each transaction log file in SQL Server is divided into multiple segments refereed as VLF or Virtual Log Files. Virtual Log Files hold a lot of importance in SQL Server. These are the basic units of truncation for the transactions done in the Server. Due to this very reason these VLFs need to be well maintained. The PowerShell commands can be used to view number of Virtual Log files currently present in the transaction log file. Read on to know more about this. For Checking SQL Server Virtual Log Files Performance Via PowerShell we need to apply these following steps.

Procedure To Check SQL Server Virtual Log Files Performance

PowerShell commands with the assistance of SQL Management Options (SMO) make the task of checking the Virtual Log Files a bit easier. In order to check the VLF using PowerShell and SMO,follow the below mentioned steps:

  1. Load the required .NET assemblies that are used by SMO.

    2. load with partial

  2. The next step is the creation of instance of a class of the Server. This instance will represent the instance of SQL Server to which you will be connecting.
    Server Instance
    In this $Serverinstance is the variable and Server is the class.
  3. After this we need to run through all the databases of SQL Server and need to access the database property of the class.While running through the database collection, we need to use ExecuteWithResults method for executing DBCC LOGINFO command in the context of the current database.We will use below mentioned PowerShell command:

    4. Execute With Result

  4. For accessing the table results, zero index should be used as we did not define the database name that will hold the DBCC LOGINFO command results.

Powershell

The result after running the above commands will be as follows:

Powershell Identification

To find the number of Virtual Log Files we need to know the total count of VLF in all the transaction log files. In PowerShell, Measure-Object command is used to know the statistics of an object like sum, average, minimum values, etc.Therefore, in order to count the number of items that are returned by ExecuteWithResults, we can use the Measure-Object command.

Measure Objects

The result which is obtained after the above commands are run as is:

Powershell Count

In the above screenshot, you can see that the Count parameter shows the results are returned by ExecuteWithResults method. This is the number of the Virtual Log Files for a particular database.

But we still don’t know the name of the database to which these Virtual Log File count is associated. Therefore, in order to know the database, we need to add Select-Object $db.Name, Count.

The complete PowerShell script for knowing the name of the database and its Virtual Log Files count is shown below.

System Resembly

Conclusion

We can see that with the help of a few PowerShell script commands, we were able to get the information regarding the Virtual Log Files.