WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Sunday, January 22, 2012

Size Matters, especially when your database is full.

You know you can't trust developers to alert the DBA group when they are about to stuff 50 million orders into a database.  They think disk space is unlimited and the database has no constraints.  If you are in a shop that controls DB size, mostly to catch rogue processes (and developers) before they fill up a drive and affect more applications, you use the max size setting.  Here is the function, it accepts an SMO object for the SQL Server you are monitoring and the threshold you specify.  It returns any database data file that is over 85%(default threshold) full based on the max size.

$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlServerName

## Get Data Files with used space within threshold of max size
Function Get-DatabaseFilesBySpaceAvailable ([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer
, [decimal] $sizeThreshold=0.85)
$sqlServer.Databases | Where-Object{$_.Status -eq "Normal"} `
| Select-Object FileGroups -ExpandProperty FileGroups `
| Select-Object Files -ExpandProperty Files  `
| Where-Object {$_.MaxSize -gt -1} `
| Where-Object {$_.UsedSpace -gt ($_.MaxSize * $sizeThreshold)} `
| Select @{Label="UsedMB";Expression={$_.UsedSpace/1024}},@{Label="SizeMB";Expression={$_.Size/1024}},@{Label="MaxSizeMB";Expression={$_.MaxSize/1024}},FileName

This function is one of many I use for my daily monitoring reports of all servers in the environment.

No comments:

Post a Comment