06 January 2010

Monitoring SQL Server Virtual Log File Fragmentation

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:

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:

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:

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:

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.

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:

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.

  2. Shrink the transaction log to as small as possible.

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

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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 37495 times – thanks for reading.

Tags: , , , ,

  • Rate
    [Total: 30    Average: 4.5/5]
  • Share

Thomas LaRock

View all articles by Thomas LaRock

  • laerte

    Worderfull !!!!!
    I have worked with powershell to automate processes in the sql server and frankly the sky is the limit.t Congrats! Excellent example…

  • Stephen Mills

    A little bit more complex than needed
    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 mills4sqlexpress “select name from sysdatabases” | % { $Database= $_.Name; Invoke-SQLcmd -Server mills4sqlexpress “dbcc loginfo([$($Database)])” } | select @{n=’Database’;e={ $Database}}, * | Group-Object -Property Database -NoElement | select @{n=’Database’;e={ $_.Name }}, @{n=’VLF’;e={ $_.Count}}

  • Stephen Mills

    Wish I could edit
    Forgot to make it more generic and included the name of my computer by accident.

  • Phil Factor

    Re: wish I could edit.
    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.

  • Rockstar

    A little bit more complex than needed
    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.

  • Buck Woody

    Great Article – and another way to do things to multiple DB’s
    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

  • toddd

    Great Article
    Great article. I’m just using the SQL.

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

  • Fraz Malik

    Excellent article
    Thanks for sharing the excellent that you have done.

  • JohnStafford

    Thanks – One Question on Excel
    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!

  • Christo Pretorius

    Syntax for JohnStafford
    $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)

  • StanleyJohns

    Nice article. Small correction.
    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.