I was playing around with PowerShell today (yes I’m was geeking out on Saturday afternoon) to learn how to access Performance Counters using PowerShell. My mission was to write a script that would return the Data File and Log File sizes for each database and a total for the instance. The good news is SQL Server already provides a bunch of fun performance counters and PowerShell has built-in support for accessing performance counters. I thought this was pretty cool so I decided to share it. In addition, I hadn’t blogged in a while and I was feeling bad about that.
There are two groups of scripts and sample output below: 1) Data File Space and 2) Log File Space.
Sorry about the formatting of the output I’m just being lazy today – after all it is Saturday.
Have fun with it!
Oh, btw: I was doing this on a Win7 machine (which I also need to give an update on) so I had to launch PS as administrator so it had access to the perf counters (same should be true on Vista and Windows Server 2K8). If you don’t launch as admin you’ll get an error that looks like this:
Get-Counter : The specified instance is not present.
At line:1 char:26
+ $DBDataFile = Get-Counter <<<< -Counter '\MSSQL$SQL2K8_01:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1
+ CategoryInfo : InvalidResult: (:) [Get-Counter], Exception
+ FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand
Data File Space
#Displays the total Data File size for each database (including system databases)
$DBDataFile = Get-Counter -Counter '\MSSQL$SQL2K8_01:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1
$DBDataFile.CounterSamples | where-object -FilterScript {($_.InstanceName -ne "_total")} | sort-object -Property InstanceName | format-table @{Label = "Database"; Expression={$_.InstanceName}}, @{Label = "Total Data File(s) Size (MB)"; Express={$_.CookedValue/1000}} -AutoSize
Database Total Data File(s) Size (MB)
-------- ----------------------------
ansicheck 2.304
master 4.096
model 1.28
msdb 10.752
mssqlsystemresource 61.696
pbm-prototype 2.048
tempdb 8.192
We can also just grab the total Data File space used by the instance:
$DBDataFile = Get-Counter -Counter '\MSSQL$SQL2K8_01:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1
$DBDataFile.CounterSamples | where-object -FilterScript {($_.InstanceName -eq "_total")} | sort-object -Property InstanceName | format-table @{Label = "Database"; Expression={$_.InstanceName}}, @{Label = "Total Data File(s) Size (MB)"; Express={$_.CookedValue/1000}} -AutoSize
Database Total Data File(s) Size (MB)
-------- ----------------------------
_total 90.368
Log File Space
#Displays the total Log File size for each database (including system databases)
$DBLogFile = Get-Counter -Counter '\MSSQL$SQL2K8_01:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1
$DBLogFile.CounterSamples | where-object -FilterScript {($_.InstanceName -ne "_total")} | sort-object -Property InstanceName | format-table @{Label = "Database"; Expression={$_.InstanceName}}, @{Label = "Total Log File(s) Size (MB)"; Express={$_.CookedValue/1000}} -AutoSize
Database Total Log File(s) Size (MB)
-------- ---------------------------
ansicheck 0.496
master 1.016
model 0.504
msdb 0.504
mssqlsystemresource 0.504
pbm-prototype 1.016
tempdb 0.504
We can also just grab the total Log File space used by the instance:
$DBLogFile = Get-Counter -Counter '\MSSQL$SQL2K8_01:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1
$DBLogFile.CounterSamples | where-object -FilterScript {($_.InstanceName -eq "_total")} | sort-object -Property InstanceName | format-table @{Label = "Database"; Expression={$_.InstanceName}}, @{Label = "Total Log File(s) Size (MB)"; Express={$_.CookedValue/1000}} –AutoSize
Database Total Log File(s) Size (MB)
-------- ---------------------------
_total 4.544
