After PowerShell one of my favourite Microsoft technologies is SQL Server. I have been working with it since version 6.5 and each new version only gets better. The introduction of SMO with SQL Server 2005 allows me to combine the two. This script will interrogate a SQL Server instance and retrieve information on the amount of space used by the databases. This could be used for growth trending as well as general monitoring of the databases.
## get-sqlinfo
## This uses the SMO assemblies to retrieve database size information
##
## Richard Siddaway June 2006
## load SMO assemblies
## use $null to prevent display of assembly load information
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
clear-host
## set SMO variable
$Server = new-object Microsoft.SqlServer.Management.Smo.Server("SQL1")
## get the database information
" {0,20} {1,15} {2,15} {3,15} {4,15}" -f "Name", "Size", "Available", "Log size", "Log Used"
foreach ($db in $Server.Databases) {
" {0,20} {1,15:n} {2,15:n} {3,15:n} {4,15:n}" -f $db.Name, $db.Size, $($db.SpaceAvailable/1KB), $($db.LogFiles[0].Size/1KB), $($db.LogFiles[0].UsedSpace/1KB)
}
As in previous scripts the SMO objects are loaded and a connection to the server is obtained. The collection of databases on the server is interrogated and for each database a number of pieces of data are output:
- Database name
- Database size
- Database space available in allocated files
- Log file sizes
- Log files size used
The data is output using a .NET formatted string. It’s worth looking up how these work as they are very powerful for formatting output.
It would be straightforward to alter the script to make the information consistent in terms of both database and logs show either space available or space used but I will leave that as an exercise for the interested student.
