This is a nice little script that I used to find the size of all of the SQL server database (MDF) and Transaction Log (LDF) files on each SQL server that we have. This stems from a task I had to write a script that could find out just what the sizes are of each database and each Transaction Log file we had on our network.
At first I thought about using the SMO assemblies into PowerShell and then creating my own objects to query each SQL server for the information. But then I thought that not every client in my office has the SQL Administrator tools loaded on each machine in order to run the script. Instead, I did some research on using performance counters to possibly pull this information. And with that, I was able to find exactly what I was looking for. Using the Get-Counter cmdlet, I was able to to query for a select counter and the parse the information to display the file sizes. Since I am using performance counters, the actual location of the files will not be shown. But for me, this was OK as all I needed was the sizes.
The Advanced Function that I wrote is Get-SQLFileSize and has an option to query one or more computers and display the MDF, LDF or both types of files along with their size in Megabytes.
Get-SQLFileSize -comp dc1
Computer Database FileType Size_MB
——– ——– ——– ——-
dc1 susdb MDF 391.36
dc1 tempdb MDF 8.192
dc1 msdb MDF 4.992
dc1 model MDF 1.216
dc1 mssqlsystemresource MDF 39.36
dc1 master MDF 4.096
dc1 susdb LDF 5.176
dc1 tempdb LDF 7.608
dc1 msdb LDF 3.448
dc1 model LDF 0.504
dc1 mssqlsystemresource LDF 0.504
dc1 master LDF 1.016
You can easily send the output to a CSV file using the Export-CSV cmdlet and send it via email as a report.
Note that there are possibilities where the counters used are inaccessible for one reason or another. If this is the case, then the function will not work as designed.
Code
Function Get-SQLFileSize {
<#
.SYNOPSIS
Retrieves the file size of a MDF or LDF file for a SQL Server
.DESCRIPTION
Retrieves the file size of a MDF or LDF file for a SQL Server
.PARAMETER Computer
Computer hosting a SQL Server
.NOTES
Name: Get-SQLFileSize
Author: Boe Prox
DateCreated: 17Feb2011
.EXAMPLE
Get-SQLFileSize -Computer Server1
Description
-----------
This command will return both the MDF and LDF file size for each database on Server1
.EXAMPLE
Get-SQLFileSize -Computer Server1 -LDF
Description
-----------
This command will return LDF file size for each database on Server1
Description
-----------
This command will return both the MDF and LDF file size for each database on Server1
.EXAMPLE
Get-SQLFileSize -Computer Server1 -MDF
Description
-----------
This command will return MDF file size for each database on Server1
#>
[cmdletbinding(
DefaultParameterSetName = 'Default',
ConfirmImpact = 'low'
)]
Param(
[Parameter(
Mandatory = $True,
Position = 0,
ParameterSetName = '',
ValueFromPipeline = $True)]
[string[]]$Computer,
[Parameter(
Mandatory = $False,
Position = 1,
ParameterSetName = '',
ValueFromPipeline = $False)]
[switch]$Mdf,
[Parameter(
Mandatory = $False,
Position = 2,
ParameterSetName = '',
ValueFromPipeline = $False)]
[switch]$Ldf
)
Begin {
If (!($PSBoundParameters.ContainsKey('Mdf')) -AND !($PSBoundParameters.ContainsKey('Ldf'))) {
Write-Verbose "MDF or LDF not selected, scanning for both file types"
$FileFlag = $True
$Flag = $False
}
#Create holder for data
Write-Verbose "Creating holder for data"
$report = @()
}
Process {
ForEach ($comp in $Computer) {
#Check for server connection
Write-Verbose "Testing server connection"
If (Test-Connection -count 1 -comp $comp -quiet) {
If ($PSBoundParameters.ContainsKey('Mdf') -OR $FileFlag) {
Write-Verbose "Looking for MDF file sizes"
Try {
Write-Verbose "Attempting to retrieve counters from server"
$DBDataFile = Get-Counter -Counter '\SQLServer:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
$DBDataFile.CounterSamples | % {
If ($_.InstanceName -ne "_total") {
$temp = "" | Select Computer, Database, FileType, Size_MB
$temp.Computer = $comp
$temp.Database = $_.InstanceName
$temp.FileType = 'MDF'
$temp.Size_MB = $_.CookedValue/1000
$report += $temp
}
}
}
Catch {
$Flag = $True
}
If ($Flag) {
Try {
Write-Verbose "Attempting to retrieve counters from server"
$DBDataFile = Get-Counter -Counter '\MSSQL$MICROSOFT##SSEE:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
$DBDataFile.CounterSamples | % {
If ($_.InstanceName -ne "_total") {
$temp = "" | Select Computer, Database, FileType, Size_MB
$temp.Computer = $comp
$temp.Database = $_.InstanceName
$temp.FileType = 'MDF'
$temp.Size_MB = $_.CookedValue/1000
$report += $temp
}
}
}
Catch {
Write-Warning "$($Comp): Unable to locate Database Counters or Database does not exist on this server"
Break
}
}
Else {
Write-Warning "$($Comp): Unable to locate Database Counters or Database does not exist on this server"
Break
}
}
If ($PSBoundParameters.ContainsKey('Ldf') -OR $FileFlag) {
Write-Verbose "Looking for LDF file sizes"
Try {
Write-Verbose "Attempting to retrieve counters from server"
$DBDataFile = Get-Counter -Counter '\SQLServer:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
$DBDataFile.CounterSamples | % {
If ($_.InstanceName -ne "_total") {
$temp = "" | Select Computer, Database, FileType, Size_MB
$temp.Computer = $comp
$temp.Database = $_.InstanceName
$temp.FileType = 'LDF'
$temp.Size_MB = $_.CookedValue/1000
$report += $temp
}
}
}
Catch {
$Flag = $True
}
If ($flag) {
Try {
Write-Verbose "Attempting to retrieve counters from server"
$DBDataFile = Get-Counter -Counter '\MSSQL$MICROSOFT##SSEE:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
$DBDataFile.CounterSamples | % {
If ($_.InstanceName -ne "_total") {
$temp = "" | Select Computer, Database, FileType, Size_MB
$temp.Computer = $comp
$temp.Database = $_.InstanceName
$temp.FileType = 'LDF'
$temp.Size_MB = $_.CookedValue/1000
$report += $temp
}
}
}
Catch {
Write-Warning "$($Comp): Unable to locate Database Counters or Database does not exist on this server"
Break
}
}
Else {
Write-Warning "$($Comp): Unable to locate Transaction Log Counters or Database does not exist on this server"
Break
}
}
}
Else {
Write-Warning "$($Comp) not found!"
}
}
}
End {
Write-Verbose "Displaying output"
$report
}
}
Learn Powershell | Achieve More 