# encoding: ascii
# api: powershell
# title: Get-SQLFileSize
# description: This function will allow you to query any server hosting SQL and return the file sizes for the each Database file(MDF) and Transaction Log file(LDF). This does not return back the file locations for each database. I have tested this on SQL 2000, 2005 and 2008.
# version: 0.1
# type: function
# author: Boe Prox 
# license: CC0
# function: Get-SQLFileSize
# x-poshcode-id: 2537
# x-archived: 2011-04-15T09:55:08
# x-published: 2011-03-04T19:55:00
#
#
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 
    }                 
}
