SQL Server: Database Storage Utilization via sp_SDS (Part 2)
In Part 1 of this series I introduced the extended version of sp_SDS I am using to monitor storage utilization in Microsoft SQL Server. In this article, I present the documentation I wrote that describes how it can be used in production.Note to the Reader: I recognize that the theme I've selected for this blog results in some of the longer tables being cut off. I will look for a solution to this issue.
Execution
sp_SDS can be run with no arguments, or with various arguments, depending on the desired output.No Arguments
exec sp_SDSThis results in output similar to the following (two result sets):
All values are measured in megabytes (MB).
| WEIGHT (%) | DATABASE | DATABASE USED (%) | + | DATABASE FREE (%) | = | DATABASE TOTAL | = | DATA (used, %) | + | LOG (used, %) |
|---|---|---|---|---|---|---|---|---|---|---|
| 0.01 | master | 3.46 (69.20 %) | + | 1.54 (30.80 %) | 5.00 | 4.00 (2.94, 73.50%) | + | 1.00 (0.52, 52.00%) | ||
| 0.00 | model | 1.69 (96.57 %) | + | 0.06 (3.43 % | 1.75 | 1.25 (1.25, 100.00%) | + | 0.50 (0.44, 88.00%) | ||
| 0.09 | msdb | 29.48 (83.35 %) | + | 5.89 (16.65 %) | 35.37 | 30.31 (27.63, 91.16%) | + | 5.06 (1.85, 36.56%) | ||
| 14.79 | poobah | 5440.47 (88.64 %) | + | 697.53 (11.36 %) | 6138.00 | 5525.81 (5426.94, 98.21%) | + | 612.19 (13.53, 2.21%) | ||
| 0.02 | ReportServer | 3.90 (41.58 %) | + | 5.48 (58.42 %) | 9.38 | 3.25 (2.88, 88.62%) | + | 6.13 (1.02, 16.64%) | ||
| 0.01 | ReportServerTempDB | 1.83 (61.00 %) | + | 1.17 (39.00 %) | 3.00 | 2.25 (1.44, 64.00%) | + | 0.75 (0.39, 52.00%) | ||
| 0.13 | tempdb | 4.63 (8.86 %) | + | 47.62 (91.14 %) | 52.25 | 49.75 (2.94, 5.91%) | + | 2.50 (1.69, 67.60%) | ||
| 84.95 | xanadu | 21912.05 (62.15 %) | + | 13345.51 (37.85 %) | 35257.56 | 29236.00 (21845.81, 74.72%) | + | 6021.56 (66.24, 1.10%) |
| SUM | USED | FREE | TOTAL | DATA | LOG |
|---|---|---|---|---|---|
| MB | 27397.51 | 14104.80 | 41502.31 | 34852.62 | 6649.69 |
Argument: @mode = 'simple'
If the default output contains more information than you'd like, pass the argument @mode = 'simple' to sp_SDS:exec sp_SDS @mode = 'simple'This results in truncated output similar to the following (two result sets):
All values are measured in megabytes (MB).
| DATABASE | TOTAL | = | DATA (used, %) | + | LOG (used, %) |
|---|---|---|---|---|---|
| master | 5.00 | 4.00 (2.94, 73.50%) | + | 1.00 (0.52, 52.00%) | |
| model | 1.75 | 1.25 (1.25, 100.00%) | + | 0.50 (0.44, 88.00%) | |
| msdb | 35.37 | 30.31 (27.63, 91.16%) | + | 5.06 (1.85, 36.56%) | |
| poobah | 6138.00 | 5525.81 (5426.94, 98.21%) | + | 612.19 (13.53, 2.21%) | |
| ReportServer | 9.38 | 3.25 (2.88, 88.62%) | + | 6.13 (1.02, 16.64%) | |
| ReportServerTempDB | 3.00 | 2.25 (1.44, 64.00%) | + | 0.75 (0.39, 52.00%) | |
| tempdb | 52.25 | 49.75 (2.94, 5.91%) | + | 2.50 (1.69, 67.60%) | |
| xanadu | 35257.56 | 29236.00 (21845.81, 74.72%) | + | 6021.56 (66.24, 1.10%) |
| SUM | USED | FREE | TOTAL | DATA | LOG |
|---|---|---|---|---|---|
| MB | 27397.51 | 14104.80 | 41502.31 | 34852.62 | 6649.69 |
Argument: <Database Name>
sp_SDS will accept a single database name as an argument:exec sp_SDS poobahThis results in output similar to the following:
All values are measured in megabytes (MB).
| WEIGHT (%) | DATABASE | DATABASE USED (%) | + | DATABASE FREE (%) | = | DATABASE TOTAL | = | DATA (used, %) | + | LOG (used, %) |
|---|---|---|---|---|---|---|---|---|---|---|
| 100.00 | poobah | 5440.77 (88.64 %) | + | 697.23 (11.36 %) | 6138.00 | 5525.81 (5427.06, 98.21%) | + | 612.19 (13.71, 2.24%) |
Alternatively, the
@mode = 'simple' argument can be used in addition to the database name argument:exec sp_SDS poobah, @mode = 'simple'Resulting in:
| DATABASE | DATABASE TOTAL | = | DATA (used, %) | + | LOG (used, %) |
|---|---|---|---|---|---|
| poobah | 6138.00 | 5525.81 (5427.06, 98.21%) | + | 612.19 (13.71, 2.24%) |
Argument: @nagios = 'true'
sp_SDS... Now with Nagios support!The output generated by sp_SDS is great for human consumption; for Nagios lovers, the output needed a little bit of reorganization so that the individual data elements can be easily parsed.
@nagios = 'true'
Calling sp_SDS with @nagios = 'true':exec sp_SDS @nagios = 'true'results in output similar to the following:
All values are measured in megabytes (MB).
| WEIGHT (%) | DATABASE | DATABASE USED | DATABASE FREE | DATABASE TOTAL | DATABASE USED (%) | DATABASE FREE (%) | DATA TOTAL | DATA USED | DATA USED (%) | LOG TOTAL | LOG USED | LOG USED (%) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0.01 | master | 3.59 | 1.41 | 5.00 | 71.80 | 28.20 | 4.00 | 2.94 | 73.50 | 1.00 | 0.65 | 65.00 |
| 0.00 | model | 1.69 | 0.06 | 1.75 | 96.57 | 3.43 | 1.25 | 1.25 | 100.00 | 0.50 | 0.44 | 88.00 |
| 0.10 | msdb | 35.77 | 6.04 | 41.81 | 85.55 | 14.45 | 36.75 | 33.69 | 91.67 | 5.06 | 2.08 | 41.11 |
| 14.79 | poobah | 5462.99 | 675.01 | 6138.00 | 89.00 | 11.00 | 5525.81 | 5456.19 | 98.74 | 612.19 | 6.80 | 1.11 |
| 0.02 | ReportServer | 3.92 | 5.46 | 9.38 | 41.79 | 58.21 | 3.25 | 2.88 | 88.62 | 6.13 | 1.04 | 16.97 |
| 0.01 | ReportServerTempDB | 1.84 | 1.16 | 3.00 | 61.33 | 38.67 | 2.25 | 1.44 | 64.00 | 0.75 | 0.40 | 53.33 |
| 0.13 | tempdb | 5.29 | 46.96 | 52.25 | 10.12 | 89.88 | 49.75 | 3.88 | 7.80 | 2.50 | 1.41 | 56.40 |
| 84.94 | xanadu | 21888.92 | 13368.64 | 35257.56 | 62.08 | 37.92 | 29236.00 | 21845.56 | 74.72 | 6021.56 | 43.36 | 0.72 |
@Mode = 'simple', @nagios = 'true'
sp_SDS can be called with @Mode = 'simple', @nagios = 'true' to limit the data output for Nagios.exec sp_SDS @Mode = 'simple', @nagios = 'true'This results in output similar to the following:
All values are measured in megabytes (MB).
| DATABASE | DATABASE TOTAL | DATA TOTAL | DATA USED | DATA USED (%) | LOG TOTAL | LOG USED | LOG USED (%) |
|---|---|---|---|---|---|---|---|
| master | 5.00 | 4.00 | 2.94 | 73.50 | 1.00 | 0.55 | 55.00 |
| model | 1.75 | 1.25 | 1.25 | 100.00 | 0.50 | 0.44 | 88.00 |
| msdb | 41.81 | 36.75 | 33.69 | 91.67 | 5.06 | 1.95 | 38.54 |
| poobah | 6690.63 | 6078.44 | 5465.69 | 89.92 | 612.19 | 9.00 | 1.47 |
| ReportServer | 9.38 | 3.25 | 2.88 | 88.62 | 6.13 | 1.04 | 16.97 |
| ReportServerTempDB | 3.00 | 2.25 | 1.44 | 64.00 | 0.75 | 0.41 | 54.67 |
| tempdb | 52.25 | 49.75 | 3.31 | 6.65 | 2.50 | 1.09 | 43.60 |
| xanadu | 35257.56 | 29236.00 | 21845.56 | 74.72 | 6021.56 | 43.36 | 0.72 |
<Database Name>, @Mode = 'simple', @nagios = 'true'
To further restrict the output to a specific database, pass <Database Name>, @Mode = 'simple', @nagios = 'true' to sp_SDS:
exec sp_SDS poobah, @Mode = 'simple', @nagios = 'true'This results in output similar to the following:
All values are measured in megabytes (MB).
| DATABASE | DATABASE TOTAL | DATA TOTAL | DATA USED | DATA USED (%) | LOG TOTAL | LOG USED | LOG USED (%) |
|---|---|---|---|---|---|---|---|
| poobah | 6690.63 | 6078.44 | 5465.69 | 89.92 | 612.19 | 9.00 | 1.47 |
Argument: @allocation_stats = 'true'
To find out how much storage is allocated to a given database (including data, indexes, and logs), from the available physical allocation, pass the argument @allocation_stats = 'true' to sp_SDS:exec sp_SDS poobah, @allocation_stats = 'true'This results in output similar to the following:
All values are measured in megabytes (MB).
| database_name | database_size | unallocated space |
|---|---|---|
| poobah | 6138.00 MB | 0.00 MB |
NOTE: Use of the @allocation_stats = 'true' argument REQUIRES that a database name be specified.
Argument: @reserved_stats = 'true'
To determine how much storage is reserved by a database, its data, and indexes, pass the argument @reserved_stats = 'true' to sp_SDS.exec sp_SDS xanadu, @reserved_stats = 'true'This results in output similar to the following:
All values are measured in kilobytes (KB).
| reserved | data | index_size | unused |
|---|---|---|---|
| 5973920 KB | 3450408 KB | 1879920 KB | 643592 KB |
NOTE: Use of the @allocation_stats = 'true' argument REQUIRES that a database name be specified.
TODO
Determine why the results of @allocation_stats = 'true' and @reserved_stats = 'true' don't jibe.
EXAMPLE: @allocation_stats = 'true' shows database_size = 6138.00 MB and unallocated space = 0.00 MB while @reserved_stats = 'true' shows reserved = 5833.90 MB (manually calculated as 5973920 KB / 1024). Where is the remaining ~305 MB?
Tags