Database sizes

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.

About these ads
This entry was posted in Script of the Week. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s