Submitted By: Stephen Merkel

Shows used space, available space, and maximum space for a SQL Server's log files and data files.

PowerShell
Edit|Remove
#
#######################################################################################
#          
#          Author: Stephen Merkel
#          Date:    08/24/2007
#          Script: dbfilestats.ps1
#          Purpose: Shows space used, available and max for a database's logfile and datafiles.
#          
#          Parameters: server_name, database_name
#
#          Usage: .\dbfilestats.ps1 <server_name> <database_name>
#
########################################################################################

param ( 
            [string] $Server = $(throw "Please provide the server name"),
            [string] $db = $(throw "Please provide the database name")
            )
# load required assemblies

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo");
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum");
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
cls

# create server and database objects using SMO

$smo = "Microsoft.SqlServer.Management.Smo."
$objServer = new-object ($smo + 'server') "$Server"
$database = $objServer.databases["$db"]
$db.toupper()+" on "+ $Server.toupper()

# Display logfile information

$database.get_logfiles() | Format-Table  @{Label="Log File"; Expression={$_.FileName}},`
            @{Label="Size(MB)"; Expression={[math]::round($_.Size/1KB,2)}},`
            @{Label="Used Space(MB)"; Expression={[math]::round($_.UsedSpace/1KB,2)}},`
            @{Label="Max Size(MB)"; Expression={[math]::round($_.MaxSize/1KB,2)}} -a

# Display data file information

foreach ($g in $database.get_Filegroups()) {
            $g.name
            $g.Files | Format-Table  @{Label="Data File"; Expression={$_.FileName}},`
            @{Label="Size(MB)"; Expression={[math]::round($_.Size/1KB,2)}},`
            @{Label="Used Space(MB)"; Expression={[math]::round($_.UsedSpace/1KB,2)}},`
            @{Label="Max Size(MB)"; Expression={[math]::round($_.MaxSize/1KB,2)}} -a
}