You might argue once you’re finished reading this post that it belongs more in a SQL-focused blog rather than a SharePoint one; however it was SharePoint that brought it about, so I thought there might be other SharePoint administrators out there who will find it useful…
It all began when a customer of mine asked me for advice on measuring the rate of growth of the transaction logs of their content databases – which I found rather difficult to provide off the top of my head. Really, it is only easy and obvious if a transaction log file is configured for autogrowth and the log is never truncated, in which case you can just monitor the size of the .ldf file – but honestly, if your databases are configured this way, measurement should (and will!) be the last thing on your mind! And in any case, the customer in question had pre-sized transaction log files, with logs truncated hourly.
After some musing, we arrived at a solution involving use of the relevant SQL performance counter and a PowerShell script to parse it – thanks to the rather useful Import-Counter cmdlet available in PowerShell V2. So, here’s what we had to do:
1) On the target SQL server, set up a performance log with an instance of the 'Log File(s) Used Size (KB)' counter for each database we were interested in.
2) Run the log for an hour (could be any period of time), then copy it over to a machine running Windows 7 – only because we had one, alternatively could use any machine with the CTP of PowerShell V2 installed.
3) Run the following script, specifying the log file as the value for the input parameter:
param ([uri]$logfile = $(throw "Path to logfile not specified!"))
# Use the Import-Counter cmdlet to import perf log data into an array of data samples
# and store it in a variable
$logdata = Import-Counter $logfile.LocalPath;
# Iinitialize an empty array that will hold an object for each dynamically discovered instance
$counters = @();
# Analyze the first data sample to discover counter instances
$logdata[0].CounterSamples |
Foreach-Object `
{
if ($_.Path -ilike '*log file(s) used size (kb)')
{
# Store the discovered instance as a hastable and add it to the $counters array
$counters += @{path=$_.Path; instance = $_.InstanceName};
}
}
# Process all counter instances, one at a time
$counters |
Foreach-Object `
{
$counter = $_;
# Analyze each data sample
$logdata |
Foreach-Object `
{
# Filter down counter samples by matching with the name and path of the
# currently processed counter instance
$_.CounterSamples |
Where-Object {($_.Path -eq $counter.path) -and ($_.InstanceName -eq $counter.instance)}
} |
Foreach-Object `
{ # This is the Begin-Processing clause, only executed once
# Ensure the necessary variables are nullified before each sample is processed
$first = $null;
$prev = $null;
$total = $null
} `
{ # This is the Process-Object, executed for each data sample
# The following algorithm identifies the first and the last value in
# each consecutive period during which the size was on the increase
# (e.g. from immediately after a truncation to the next truncation)
# and sums up the differences between all such pairs of values
if (-not $first)
{
$first = $_.CookedValue;
}
if (-not $prev)
{
$prev = $_.CookedValue;
}
if ($_.CookedValue -lt $prev)
{
$total += ($prev - $first);
$first = $_.CookedValue;
}
$prev = $_.CookedValue;
} `
{
if ($prev -gt $first)
{
$total += ($prev - $first);
}
# Round the final result for the current counter instance
# and store it in the same hashtable
$counter.total = [math]::Round(($total / 1KB),2)
}
}
Write-Host
Write-Host ("Log start:`t`t`t{0}" -f $logdata[0].TimeStamp);
Write-Host ("Log end:`t`t`t{0}" -f $logdata[-1].TimeStamp);
# Calculate the duration of the logging period
$duration = ($logdata[-1].TimeStamp - $logdata[0].TimeStamp);
Write-Host ("Total duration (hh:mm:ss):`t{0:d2}:{1:d2}:{2:d2}" -f $duration.Hours, $duration.Minutes, $duration.Seconds);
Write-Host ("Number of data samples:`t`t{0}" -f $logdata.Length)
Write-Host ("Sampling interval (sec):`t{0}" -f ($logdata[-1].TimeStamp - $logdata[-2].TimeStamp).Seconds)
$counters |
select @{n="Database Name";e={$_.instance}}, @{n="Log File Growth (Mb)"; e={$_.total}} |
Sort-Object "Log File Growth (Mb)" -descending |
Format-Table –autosize
What we see in the result is this nice report, with the most heavily utilized database appearing on top:
