RyanFrantz

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_SDS

This results in output similar to the following (two result sets):

All values are measured in megabytes (MB).

WEIGHT (%)DATABASEDATABASE USED (%)+DATABASE FREE (%)=DATABASE TOTAL=DATA (used, %)+LOG (used, %)
0.01master3.46 (69.20 %)+1.54 (30.80 %)5.004.00 (2.94, 73.50%)+1.00 (0.52, 52.00%)
0.00model1.69 (96.57 %)+0.06 (3.43 %1.751.25 (1.25, 100.00%)+0.50 (0.44, 88.00%)
0.09msdb29.48 (83.35 %)+5.89 (16.65 %)35.3730.31 (27.63, 91.16%)+5.06 (1.85, 36.56%)
14.79poobah5440.47 (88.64 %)+697.53 (11.36 %)6138.005525.81 (5426.94, 98.21%)+612.19 (13.53, 2.21%)
0.02ReportServer3.90 (41.58 %)+5.48 (58.42 %)9.383.25 (2.88, 88.62%)+6.13 (1.02, 16.64%)
0.01ReportServerTempDB1.83 (61.00 %)+1.17 (39.00 %)3.002.25 (1.44, 64.00%)+0.75 (0.39, 52.00%)
0.13tempdb4.63 (8.86 %)+47.62 (91.14 %)52.2549.75 (2.94, 5.91%)+2.50 (1.69, 67.60%)
84.95xanadu21912.05 (62.15 %)+13345.51 (37.85 %)35257.5629236.00 (21845.81, 74.72%)+6021.56 (66.24, 1.10%)


SUMUSEDFREETOTALDATALOG
MB27397.5114104.8041502.3134852.626649.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).

DATABASETOTAL=DATA (used, %)+LOG (used, %)
master5.004.00 (2.94, 73.50%)+1.00 (0.52, 52.00%)
model1.751.25 (1.25, 100.00%)+0.50 (0.44, 88.00%)
msdb35.3730.31 (27.63, 91.16%)+5.06 (1.85, 36.56%)
poobah6138.005525.81 (5426.94, 98.21%)+612.19 (13.53, 2.21%)
ReportServer9.383.25 (2.88, 88.62%)+6.13 (1.02, 16.64%)
ReportServerTempDB3.002.25 (1.44, 64.00%)+0.75 (0.39, 52.00%)
tempdb52.2549.75 (2.94, 5.91%)+2.50 (1.69, 67.60%)
xanadu35257.5629236.00 (21845.81, 74.72%)+6021.56 (66.24, 1.10%)


SUMUSEDFREETOTALDATALOG
MB27397.5114104.8041502.3134852.626649.69

Argument: <Database Name>

sp_SDS will accept a single database name as an argument:

exec sp_SDS poobah

This results in output similar to the following:

All values are measured in megabytes (MB).

WEIGHT (%)DATABASEDATABASE USED (%)+DATABASE FREE (%)=DATABASE TOTAL=DATA (used, %)+LOG (used, %)
100.00poobah5440.77 (88.64 %)+697.23 (11.36 %)6138.005525.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:

DATABASEDATABASE TOTAL=DATA (used, %)+LOG (used, %)
poobah6138.005525.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 (%)DATABASEDATABASE USEDDATABASE FREEDATABASE TOTALDATABASE USED (%)DATABASE FREE (%)DATA TOTALDATA USEDDATA USED (%)LOG TOTALLOG USEDLOG USED (%)
0.01master3.591.415.0071.8028.204.002.9473.501.000.6565.00
0.00model1.690.061.7596.573.431.251.25100.000.500.4488.00
0.10msdb35.776.0441.8185.5514.4536.7533.6991.675.062.0841.11
14.79poobah5462.99675.016138.0089.0011.005525.815456.1998.74612.196.801.11
0.02ReportServer3.925.469.3841.7958.213.252.8888.626.131.0416.97
0.01ReportServerTempDB1.841.163.0061.3338.672.251.4464.000.750.4053.33
0.13tempdb5.2946.9652.2510.1289.8849.753.887.802.501.4156.40
84.94xanadu21888.9213368.6435257.5662.0837.9229236.0021845.5674.726021.5643.360.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).

DATABASEDATABASE TOTALDATA TOTALDATA USEDDATA USED (%)LOG TOTALLOG USEDLOG USED (%)
master5.004.002.9473.501.000.5555.00
model1.751.251.25100.000.500.4488.00
msdb41.8136.7533.6991.675.061.9538.54
poobah6690.636078.445465.6989.92612.199.001.47
ReportServer9.383.252.8888.626.131.0416.97
ReportServerTempDB3.002.251.4464.000.750.4154.67
tempdb52.2549.753.316.652.501.0943.60
xanadu35257.5629236.0021845.5674.726021.5643.360.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).

DATABASEDATABASE TOTALDATA TOTALDATA USEDDATA USED (%)LOG TOTALLOG USEDLOG USED (%)
poobah6690.636078.445465.6989.92612.199.001.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_namedatabase_sizeunallocated space
poobah6138.00 MB0.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).

reserveddataindex_sizeunused
5973920 KB3450408 KB1879920 KB643592 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?