WIDBA

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

Friday, January 27, 2012

"Trust but Verify" .... Your Backups.

Here is yet another script used in enterprise monitoring of SQL Server.  If you are in a big shop, you likely have a corporate backup solution in place, but what about development, test, etc?  You can use this script to checkout all the other servers in your environment to make sure they are getting a backup at least occasionally.

First up, include the SMO library and create a SQL SMO object.
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance

<#
  This function takes an SMO object [$SmoSqlServer]
  The number of days before alerting the backup is old [$DaysToConsiderOld]
  Array of databases names to ignore.[$DBIgnoreList]
#>
Function Get-DatabasesWithOldFullBackups([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer
, [int] $DaysToConsiderOld, [array]$DBIgnoreList)
{
if($DaysToConsiderOld -gt 0) {$DaysToConsiderOld *= -1;}
$bkupDate = (Get-Date).AddDays($DaysToConsiderOld);

$SmoSqlServer.Databases | Where-Object {$_.IsSystemObject -eq $false -and $_.ReadOnly -eq "False" -and $_.IsAccessible -eq "True" } `
| Where {$DBIgnoreList -notcontains $_.Name}`
| Where {($_.LastBackupDate -le $bkupDate)}`
| Select-Object Name,RecoveryModel,@{Label="LastFull";Expression={$_.LastBackupDate};}`
,@{Label="LastDiff";Expression={$_.LastDifferentialBackupDate};}, Size
}


Make sure you verify those backups!

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.