Using PowerShell to Get Data & Log File Sizes

 

Using PowerShell to Get Data & Log File Sizes

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

Published Saturday, March 21, 2009 2:42 PM by dtjones

Dan's Blog : Using PowerShell to Get Data & Log File Sizes