Overview
One of the first things I want to do when I look at a new SQL Server is to get an idea of space usage details for each database, as well as total.
sp_dbinfo.sql (doesn't support secondary databases in AG)
sp_dbinfo_AG.sql (excludes non-readable secondary databases in AG, requires SQL 2012)
Versions
See comment block in procedure source code for version history of the procedure.
Details
The procedure returns a row for each database plus one row for all databases. You get the name of the database, data space allocated, data space used, log space allocated and log space used. All units are MB. The procedure accepts two parameters, both optional.
@sort is used for sorting the result. The value is a single letter:
| value | meaning |
| 'n' | database name (default) |
| 'd' | data space allocated |
| 'l' | log space allocated |
| 'r' | rollup information only |
@include_instance_name ('y' or 'n') specified whether to include the instance name in the result set.
Usage examples
EXEC sp_dbinfo
EXEC sp_dbinfo 'n'
EXEC sp_dbinfo 'd'
EXEC sp_dbinfo 'l'
EXEC sp_dbinfo 'l', 'y'
Example result
| database_name | data_allocated | data_used | log_allocated | log_used |
| Adventureworks2008 | 180 | 179 | 1 | 0 |
| Credit | 26 | 24 | 3 | 0 |
| master | 4 | 2 | 1 | 0 |
| Utilities | 3 | 1 | 1 | 0 |
| [ALL] | 247 | 234 | 38 | 7 |