sp_storage: A proc to manage disk space in SQL Server

Aug 20, 2014 | SQL Storage Management

Status = Version 1

Download the procedure script from here: sp_storage-procedure-creation-script
If you can use the CLR add this script for the drive_info routine
Download the bare (no procedure creation) script here: sp_storage-non-procedure-script
(the bare script is used where creating a system stored procedure isn’t applicable)

As a developer of database code in T-SQL I often take on the DBA tasks for the development servers. As I work to make databases smaller and more efficient I’m always experimenting with different files and I spend a lot of time managing storage space.  The most common idiom that I type is to get the size of the files in the database that I’m working on and the free space on the server.  It usually looks like this:

exec xp_spaceused
go
select size/128.0, * from sys.database_files.
go

There are always changes from that starting point depending on what other information that I might need.  After doing this 100’s of times with all sorts of variants I decided to pull together a procedure to summarize the information I need as quickly as compactly and as readable as possible.  sp_storage is that routine and I’ve written it as a system stored procedure and a non-procedure script.

sp_storage is a procedure that helps me manage available disk space on a SQL Server.  It does this by returning three result sets:

  • Drive Summary: with a row for each drive letter
  • Database Summary: with a row per database
  • File Summary with a row per file.

Let’s take a look at the output:

Any combination of the three results sets can be returned. The @result_sets parameter governs which result sets are returned.  When saving the results to a table, select only one of the result sets.

Output can be either formatted or unformatted as governed by the @format_output parameter, which is 1 for formatted (the default) or 0 for unformatted.  Formatted output looks best when SSMS’s grid output uses a fixed width font such as Courier New for Lucidia Console.  Unformatted output is available for easy setting to a table.

in either a very readable, formatted way or unformatted as data.