Click here to monitor SSC
  • Av rating:
  • Total votes: 29
  • Total comments: 11
Thomas LaRock

Monitoring SQL Server Virtual Log File Fragmentation

06 January 2010

One of the delights of PASS is to be able to pick up ideas from some of the presentations and recombine them in new and interesting ways.  Tom recounts how he used two different insights to solve a problem of monitoring a large number of servers for signs of Virtual Log-file Fragmentation.

During the most recent PASS Summit in Seattle I attended a handful of sessions from some of the legends in the SQL Community. One talk was by Paul Randal on logging and recovery where he spent time discussing virtual log files (VLFs) and how you should be mindful of them and  their performance implications. Another talk was by Buck Woody: He was showing off a handful of new features in SQL 2008 and he quickly demonstrated a Powershell script that populated an Excel chart to quickly give a visual representation of the row count in your database tables.

About a week later I found myself wondering if there was a way to incorporate both of those ideas into something slightly novel: I wanted to create a report to quickly display the current state of the number of virtual log files for every database I administer on a particular instance.

Why the interest in VLFs? In MS SQL Server, your transaction log may look like one file on disk, but inside that file there exist these virtual log files, splitting your physical file into virtual chunks. For an example of this, run the following:

DBCC LOGINFO;

The output will be the number of VLFs for the current database. You can see the size of the virtual chunks, the sequence number, the status, and even some LSN information. For a wonderful discussion on VLFs and transaction log throughput see Kimberly L. Tripp’s blog post at http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx.

Getting details on the number of VLFs for your database is an easy way for you to quickly tune a piece of your environment. Knowing this could be a “quick win” I set out to gather metrics so that I could form a plan of action.

Gathering the Details

In the past, when I have wanted to find out details about all of my servers I have resorted to running a multi-server query using a Central management Server. As good as this can be for me there are two drawbacks for me with the use of CMS. First, I can only select an entire group of servers; it is not possible for me to select a distinct subset of servers defined in the CMS. If I have the need for a query against such a subset then I need to define a new group, which can be tedious at best. Second, the results would need to be massaged manually should you want to put them into Excel (or something similar).

About a year ago I started to become romantically involved with Policy Based Management. Knowing that PBM could also be a possible solution I set about trying to configure a policy that would go out and capture information of all of the VLFs in our shop. It took me very little time to get the policy built and it worked very well. In a matter of a few minutes I was able to quickly see exactly what databases had more than 100 VLFs. The only problem I had was that it was very difficult for me to share this information with anyone else unless I invited them into my cube and showed them my screen after the policy was evaluated. Sure, I can save it as XML, but that was not very helpful. What I really wanted was what Buck Woody had; a bar chart graph in Excel.

Knowing the Buck had used Powershell (POSH) in order to achieve those results I decided to roll up my sleeves and dive into POSH to see what I could accomplish. I wanted to keep things simple for now. My requirements were to create a report against a single instance that would display the top ten databases with more than 50 VLFs and display the results in a bar chart graph.

My First Time with POSH

After seeing Buck Woody perform his magic at PASS I asked him if he would post the details of his script to his blog (you can see the post at http://blogs.msdn.com/buckwoody/archive/2009/11/09/create-an-excel-graph-of-your-big-tables-with-powershell.aspx). I told him how his demo was inspiring me to create something similar for VLFs. So, I had an example to review to help me get started with POSH, but what next?

For me the next step was to download PowerGUI (http://www.powergui.org/index.jspa). I like having GUIs to interact with and would always prefer a GUI to working strictly through a command line. PowerGui also has a Script Editor which allows for me to have a quick syntax checker, a valuable tool for someone new to POSH such as myself. After downloading the tools I was ready to get started.

The first thing I needed to do was to figure out a way to get all the VLFs for the current instance. I tried to find VLF details inside of the POSH structure for a SQL instance but could not, so I had to find a way to get the job done in T-SQL and then use that inside of the POSH script. The only details I had were

  1.  I knew the DBCC command

  2.  I knew I needed to do this for each database independently, and  …

  3. I needed to limit my result set otherwise the graph in Excel would look congested.

The script I came up with is as follows:

/*Create two temp tables, one for current db VLF and one for the total VLFs collected*/

CREATE TABLE #VLF_temp

                  (FileID varchar(3), FileSize numeric(20,0),

                  StartOffset bigint, FSeqNo bigint, Status char(1),

                  Parity varchar(4), CreateLSN numeric(25,0))

 

 

CREATE TABLE #VLF_db_total_temp

                  (name sysname, vlf_count int)

 

/*Declare a cursor to loop through all current databases*/

DECLARE db_cursor CURSOR

READ_ONLY

FOR SELECT name FROM master.dbo.sysdatabases

 

DECLARE @name sysname, @stmt varchar(40)

OPEN db_cursor

 

FETCH NEXT FROM db_cursor INTO @name

WHILE (@@fetch_status <> -1)

BEGIN

      IF (@@fetch_status <> -2)

      BEGIN

            /*insert the results into the first temp table*/           

            INSERT INTO #VLF_temp

            EXEC ('DBCC LOGINFO ([' + @name + ']) WITH NO_INFOMSGS')

            /*insert the db name and count into the second temp table*/

            INSERT INTO #VLF_db_total_temp

                  SELECT @name, COUNT(*) FROM #VLF_temp

            /*truncate the first table so we can get the count for the next db*/

            TRUNCATE TABLE #VLF_temp

           

      END

      FETCH NEXT FROM db_cursor INTO @name

END

/*close and deallocate cursor*/

CLOSE db_cursor

DEALLOCATE db_cursor

 

/*we are only interested in the top ten rows because having more could look funky in an Excel graph*/

/*we are currently only interested in db's with more than 50 VLFs*/

SELECT TOP 10

@@servername as [ServerName], name as [DBName], vlf_count as [VLFCount]

FROM #VLF_db_total_temp

WHERE vlf_count > 50

ORDER BY vlf_count DESC

 

/*drop the tables*/

DROP TABLE [#VLF_temp]

DROP TABLE [#VLF_db_total_temp]

The next thing I did was to modify Buck’s script so that it would open a connection to a SQL Server. I did this so that in the future I would be able to easily pass a parameter to my script to have it connect to any instance I want. I could also pass in a parameter for the number of VLFs to check for, if I so desired.

The beginning of my POSH script now looked like this:

# Number of VLFs to Excel Chart

 

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$DataSet = New-Object System.Data.DataSet

$SqlConnection.ConnectionString =

    "Server=$servername;Database=master;Integrated Security=True"

Replace ‘servername’ with the name of your target server and the script should connect as long as you are running the script with credentials that can access the instance. The next part of the script defines the command text and populates a dataset:

$SqlCmd.CommandText = "

CREATE TABLE #VLF_temp

                  (FileID varchar(3), FileSize numeric(20,0),

                  StartOffset bigint, FSeqNo bigint, Status char(1),

                  Parity varchar(4), CreateLSN numeric(25,0))

 

 

CREATE TABLE #VLF_db_total_temp

                  (name sysname, vlf_count int)

 

 

 

DECLARE db_cursor CURSOR

READ_ONLY

FOR SELECT name FROM master.dbo.sysdatabases

 

DECLARE @name sysname, @stmt varchar(40)

OPEN db_cursor

 

FETCH NEXT FROM db_cursor INTO @name

WHILE (@@fetch_status <> -1)

BEGIN

      IF (@@fetch_status <> -2)

      BEGIN

                       

            INSERT INTO #VLF_temp

            EXEC ('DBCC LOGINFO ([' + @name + ']) WITH NO_INFOMSGS')

           

            INSERT INTO #VLF_db_total_temp

                  SELECT @name, COUNT(*) FROM #VLF_temp

           

            TRUNCATE TABLE #VLF_temp

           

      END

      FETCH NEXT FROM db_cursor INTO @name

END

 

CLOSE db_cursor

DEALLOCATE db_cursor

 

 

SELECT TOP 10

@@servername as [ServerName], name as [DBName], vlf_count as [VLFCount]

FROM #VLF_db_total_temp

WHERE vlf_count > 50

ORDER BY vlf_count DESC

 

DROP TABLE [#VLF_temp]

DROP TABLE [#VLF_db_total_temp]

"

$SqlCmd.Connection = $SqlConnection

$SqlAdapter.SelectCommand = $SqlCmd

$SqlAdapter.Fill($DataSet)

$VLFCount =$DataSet.Tables[0]

Now I wanted to change things up a bit from Buck’s demo. I am no stranger to bars, but I like pie as well. I wanted to have Excel show the results in a pie chart. This took a little digging to find out how to get it to work, but it was not very difficult in the end.

$range = $sheet.usedRange

$range.EntireColumn.AutoFit()

$workbook.charts.add()

$workbook.ActiveChart.chartType = $chartType::xlPie

$workbook.ActiveChart.SetSourceData($range)

When you put the script together you will notice that, upon execution, the initial graph displayed is a bar graph which is then followed by the pie chart. It may take a few seconds for the pie to be displayed on your screen. And if you want to have more fun, then change ::xlPie to ::xl3DPieExploded and add the following lines to the end of the script:

For($i = 15 ; $i -le 360 ; $i +=15)

{

 $workbook.ActiveChart.rotation = $i

}

This will create a 3D exploded pie chart that rotates.

Action Plan

So, you have a report LISTING out the VLFs for the databases. What actions are you to take next? How do you go about correcting the issue? And I do not mean just correcting it at the moment, but correcting it so as to reduce the chances of it happening again?

Most of the time excessive VLF fragmentation is brought about by excessive file growth at small intervals. For example, a database that is set to grow a transaction log file by 5mb at a time is going to have a large number of VLFs should the log decide to grow. The following chart explains how many VLFs are added based upon the size of the growth.

Growth

Number of VLFs created

<= 64Mb

4

>64 but <=1Gb

8

>1Gb

16

 

This means that if your log grew by 5Mb at a time, internally the physical log files would create four virtual log files, each 1.25Mb in size. Now, if your log grew by 100Mb at 5Mb at a time, you would have 80 VLFs, each at 1.25Mb in size. However, if you grew the log by 100Mb at a time instead of 5Mb, you would have created 8 VLFs each at 12.5Mb in size. Your VLFs would be ten times larger in size and you would have only grown your log file once instead of twenty times.

While you may not always know exactly how large your transaction log file should be, if you keep the above numbers in mind you can make a concerted effort to keep your VLF count to a minimum. Simply review the autogrowth settings for your transaction log and make certain it is sized appropriately.

If you come across a database with more than 50-100 VLFs then you should look to take action in order to increase the transaction log throughput. Kimberly Tripp does a great job of detailing what needs to be done in the above blog post and I will summarize again.

  1.  Backup your transaction log, even if you are in simple mode, in order to clear all activity.

    BACKUP
    LOG [AdventureWorks]
    TO
      DISK = N'G:\db.bak'
    GO

  • Shrink the transaction log to as small as possible.

    USE
    [AdventureWorks]
    GO

    DBCC
    SHRINKFILE ('AdventureWorks_Log', TRUNCATEONLY)
    GO

  • Alter the database to modify the size of the transaction log and configure your autogrowth, keeping in mind the above chart.

    USE
    [master]
    GO

    ALTER
    DATABASE [AdventureWorks]
       
    MODIFY FILE ( NAME = N'AdventureWorks_Log', SIZE = 1024000KB, FILEGROWTH = 1024000KB)
    GO

  • Summary

    Good ideas come from anywhere at any time. They often sneak up on you when you least expect it and can be sitting in front of you without you even noticing they are there. Such is the case with VLFs and using POSH to build charts in Excel. I have known about POSH for years but never dived into just how useful it could be for me until I saw Paul and Buck at PASS last month. Sometimes it is a piece of info from here and there that allow for you to develop solutions that are customized for your needs at the time.

    The idea that I can be using POSH to quickly create graphs that I can then use to give to my customers makes me excited to learn more about how I can utilize POSH in other ways. I find myself thinking about a lot of different ways I can incorporate POSH into just about everything I administer. Disk space usage, I/O throughput, missing indexes, just about anything I have a script for in my toolbox I could be putting into reports quickly for others to understand a little bit easier.

    And then things should evolve from there.

    Thomas LaRock

    Author profile:

    Thomas LaRock is a seasoned IT professional with over a decade of technical and management experience. Currently serving as a Senior Database Administrator manager for Confio Software, Thomas has progressed through several roles including programmer, analyst, and DBA. Prior to that, he worked at several software and consulting companies, working at customer sites in the United States and abroad. Thomas holds a MS degree in Mathematics from Washington State University and is a member of the Usability Professional’s Association. Thomas is also a member of Quest Software’s Association of SQL Server Experts, currently serves on the Board of Directors for the Professional Association for SQL Server (PASS), and is a SQL Server MVP. Thomas can also be found blogging at http://thomaslarock.com and is the author of DBA Survivor: Become a Rock Star DBA (http://dbasurvivor.com).

    Search for other articles by Thomas LaRock

    Rate this article:   Avg rating: from a total of 29 votes.


    Poor

    OK

    Good

    Great

    Must read
    Have Your Say
    Do you have an opinion on this article? Then add your comment below:
    You must be logged in to post to this forum

    Click here to log in.


    Subject: Worderfull !!!!!
    Posted by: laerte (view profile)
    Posted on: Wednesday, January 06, 2010 at 11:22 AM
    Message: I have worked with powershell to automate processes in the sql server and frankly the sky is the limit.t Congrats! Excellent example...

    Subject: A little bit more complex than needed
    Posted by: Stephen Mills (not signed in)
    Posted on: Thursday, January 07, 2010 at 10:39 AM
    Message: I actually find using PowerShell more and TSQL less to work better in many situations. This is one of those cases. Here's an example I threw together to get the VLF in one line. It would require the SQL 2008 PowerShell Snapin, but then you don't have to deal with the stinking temp tables that TSQL makes you muck around with.

    Invoke-SQLcmd -Server mills4\sqlexpress "select name from sysdatabases" | % { $Database= $_.Name; Invoke-SQLcmd -Server mills4\sqlexpress "dbcc loginfo([$($Database)])" } | select @{n='Database';e={ $Database}}, * | Group-Object -Property Database -NoElement | select @{n='Database';e={ $_.Name }}, @{n='VLF';e={ $_.Count}}

    Subject: Wish I could edit
    Posted by: Stephen Mills (not signed in)
    Posted on: Thursday, January 07, 2010 at 10:40 AM
    Message: Forgot to make it more generic and included the name of my computer by accident.

    Subject: Re: wish I could edit.
    Posted by: Phil Factor (view profile)
    Posted on: Thursday, January 07, 2010 at 11:32 AM
    Message: You can always ask the editor on Editor@Simple-Talk.com and he'll change it for you! It is a great bit of Powershell though, Stephen. I like both versions equally.

    Subject: A little bit more complex than needed
    Posted by: Rockstar (view profile)
    Posted on: Thursday, January 07, 2010 at 3:04 PM
    Message: Stephen,

    Thanks! I had been trying to find a way to do it all in POSH, but could not get it to work. Thanks for the help.

    Subject: Great Article - and another way to do things to multiple DB's
    Posted by: Buck Woody (not signed in)
    Posted on: Saturday, January 09, 2010 at 11:41 AM
    Message: As usual, a fantastic job by the Rockstar. And Stephen Mills - great submission. You might find this a bit easier to iterate through the databases using the SQL Server 2008 Provider. Just open the SQLPS.EXE, then navigate to the Instance Name. Then you just need to run this:


    DIR databases | foreach-object -process {invoke-sqlcmd -query "SELECT db_name()" -database $_.name -surpressproviderconentxtwarning } | format-table -autosize

    Subject: Great Article
    Posted by: toddd (view profile)
    Posted on: Monday, January 18, 2010 at 3:00 PM
    Message: Great article. I'm just using the SQL.

    use of set nocount on and set nocount off cleans up the output.

    Subject: Excellent article
    Posted by: Fraz Malik (view profile)
    Posted on: Wednesday, February 10, 2010 at 3:09 PM
    Message: Thanks for sharing the excellent that you have done.

    Subject: Thanks - One Question on Excel
    Posted by: JohnStafford (view profile)
    Posted on: Tuesday, May 25, 2010 at 7:09 AM
    Message: Would it be possible to share the part of the script that outouts to Excel? I've tried to build based on Buck Woody's article but don't seem to have stumbeld across the right syntax yet!

    Subject: Syntax for JohnStafford
    Posted by: Christo Pretorius (view profile)
    Posted on: Monday, January 24, 2011 at 9:57 AM
    Message: $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)
    $VLFCount = $DataSet.Tables[0]

    $excel = new-object -comobject excel.application
    $excel.visible = $true
    $chartType = "microsoft.office.interop.excel.xlChartType" -as [type]
    $workbook = $excel.workbooks.add()
    $workbook.WorkSheets.item(1).Name = "VLF"
    $sheet = $workbook.WorkSheets.Item("VLF")
    $x = 2
    $sheet.cells.item(1,1) = "ServerName"
    $sheet.cells.item(1,2) = "DBName"
    $sheet.cells.item(1,3) = "VLFCount"

    Foreach($VLFCount in $VLFCount)
    {
    $sheet.cells.item($x,1) = $VLFCount.ServerName
    $sheet.cells.item($x,2) = $VLFCount.DBName
    $sheet.cells.item($x,3) = $VLFCount.VLFCount
    $x++
    }
    $range = $sheet.usedRange
    $range.EntireColumn.AutoFit()
    $workbook.charts.add()
    # $workbook.ActiveChart.chartType = $chartType::xlPie
    $workbook.ActiveChart.SetSourceData($range)

    Subject: Nice article. Small correction.
    Posted by: StanleyJohns (view profile)
    Posted on: Wednesday, August 03, 2011 at 8:10 AM
    Message: Great article Thomas.
    Just to point out, you had mentioned: "Backup your transaction log, even if you are in simple mode, in order to clear all activity.". You will not be able to backup the transaction log in simple mode. A checkpoint can be used to flush the log activity.
    I was looking around for the syntax to get SQL result sets with POSH.
    Thanks.

     

    Phil Factor
    Searching for Strings in SQL Server Databases

    Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

     View the blog

    Top Rated

    SQL Server XML Questions You Were Too Shy To Ask
     Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

    Continuous Delivery and the Database
     Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

    The SQL Server Sqlio Utility
     If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

    The PoSh DBA - Reading and Filtering Errors
     DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

    MySQL Compare: The Manual That Time Forgot, Part 1
     Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

    Most Viewed

    Beginning SQL Server 2005 Reporting Services Part 1
     Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

    Ten Common Database Design Mistakes
     If database design is done right, then the development, deployment and subsequent performance in... Read more...

    SQL Server Index Basics
     Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

    Reading and Writing Files in SQL Server using T-SQL
     SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

    Concatenating Row Values in Transact-SQL
     It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

    Why Join

    Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.