Retrieve SQL Database and Transaction Log File Sizes using Get-Counter

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

Script Repository

PoshCode

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 
    }                 
}

About Boe Prox

Microsoft Cloud and Datacenter MVP working as a SQL DBA.
This entry was posted in powershell, scripts and tagged , , . Bookmark the permalink.

3 Responses to Retrieve SQL Database and Transaction Log File Sizes using Get-Counter

  1. Victor says:

    True, it doesn’t seem to work for SQL instances.

  2. Gurpreet Singh Sethi says:

    Below is the error that I am getting:

    Unable to find type [cmdletbinding(
    DefaultParameterSetName = ‘Default’,
    ConfirmImpact = ‘low’
    )]: make sure that the assembly containing this type is loaded.
    At line:5 char:3
    + )] <<<<
    The term 'param' is not recognized as a cmdlet, function, operable program, or
    script file. Verify the term and try again.
    At line:6 char:10
    + Param( <<<<
    The term 'begin' is not recognized as a cmdlet, function, operable program, or
    script file. Verify the term and try again.
    At line:23 char:6
    + Begin <<<< {
    Get-Process : A parameter cannot be found that matches parameter name '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 ser
    ver"
    $DBDataFile = Get-Counter -Counter '\SQLServer:Database
    s(*)\Data File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
    $DBDataFile.CounterSamples | % {
    If ($_.InstanceName -ne "_total") {
    $temp = "" | Select Computer, Database, FileTyp
    e, 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$MICROSOF
    T##SSEE:Databases(*)\Data File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop

    $DBDataFile.CounterSamples | % {
    If ($_.InstanceName -ne "_total") {
    $temp = "" | Select Computer, Database, Fil
    eType, 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 Coun
    ters 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 ser
    ver"
    $DBDataFile = Get-Counter -Counter '\SQLServer:Database
    s(*)\Log File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
    $DBDataFile.CounterSamples | % {
    If ($_.InstanceName -ne "_total") {
    $temp = "" | Select Computer, Database, FileTyp
    e, 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$MICROSOF
    T##SSEE:Databases(*)\Log File(s) Size (KB)' -MaxSamples 1 -comp $comp -ea stop
    $DBDataFile.CounterSamples | % {
    If ($_.InstanceName -ne "_total") {
    $temp = "" | Select Computer, Database, Fil
    eType, 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 L
    og Counters or Database does not exist on this server"
    Break
    }
    }
    }
    Else {
    Write-Warning "$($Comp) not found!"
    }
    }
    '.
    At line:33 char:8
    + Process <<<< {
    The term 'end' is not recognized as a cmdlet, function, operable program, or sc
    ript file. Verify the term and try again.
    At line:132 char:4
    + End <<<< {

  3. Gurpreet Singh Sethi says:

    Hi Boe,

    Thanks for sharing this script. However when I am trying to execute this script I getting error. When i try to provide a named instance of SQL this script is failing.

    Get-SQLFileSize -comp -[Hero\2k5]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s