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:
select size/128.0, * from sys.database_files.
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.