# Thursday, 26 March 2015

Quite often a DBA will get questions from an application administrator regarding the growth possibilities for databases or the possibilities for adding additional databases. In general, the administrators for applications like Lync, SharePoint, Microsoft Dynamics or System Center will have (and require) some higher level privileges on the SQL Server anyway. So chances are, they won’t even require additional permissions the query the information they need and on top of that, their applications may even have the permissions to create these databases without the intervention of a DBA. So here’s the query to let them answer the question themselves (provided they have enough permissions on the server);

/* Works for SQL Server 2008 R2 SP1 and above */
WITH volume_info
AS
(
SELECT vs.volume_mount_point, mf.type_desc
, COUNT(DISTINCT mf.database_id) AS Database_Count
, COUNT(mf.database_id) AS File_Count
, MAX(CAST(vs.Total_Bytes AS decimal(38,4))) AS Total_Bytes
, SUM(CAST(mf.size AS decimal(38,4))) * 8192 AS Database_Used_Bytes
, MIN(CAST(vs.available_bytes AS decimal(38,4))) AS Available_Bytes
FROM sys.master_files mf /* requires VIEW ANY DEFINITION (or CREATE DATABASE or ALTER ANY DATABASE) */
CROSS APPLY sys.dm_os_volume_stats(mf.database_id,mf.file_id) vs /* requires VIEW SERVER STATE */
GROUP BY mf.type_desc, vs.volume_mount_point
)
SELECT volume_mount_point, type_desc, Database_Count, File_Count
, CAST(Total_Bytes/(1024*1024*1024) AS decimal(38,4)) AS Total_Volume_GB
, CAST(Database_Used_Bytes/(1024*1024*1024) AS decimal(38,4)) AS Database_Used_GB
, CAST(Available_Bytes/(1024*1024*1024) AS decimal(38,4)) AS Available_GB
, CAST(100 * Database_Used_Bytes / Total_Bytes AS decimal(7,4)) AS Database_Used_Pct
, CAST(NULL AS decimal(7,4)) AS Available_Space_Pct
, CAST(NULL AS decimal(7,4)) AS Other_Usage_Pct
FROM volume_info
UNION
SELECT
volume_mount_point, NULL, NULL, SUM(File_Count)
,
MIN(CAST(Total_Bytes/(1024*1024*1024) AS decimal(38,4)))
,
SUM(CAST(Database_Used_Bytes/(1024*1024*1024) AS decimal(38,4)))
,
MIN(CAST(Available_Bytes/(1024*1024*1024) AS decimal(38,4)))
,
CAST(100 * SUM(Database_Used_Bytes) / MIN(Total_Bytes) AS decimal(7,4))
,
CAST(100 * MIN(Available_Bytes) / MIN(Total_Bytes) AS decimal(7,4))
,
CAST(100 * (MIN(Total_Bytes) - SUM(Database_Used_Bytes) - MIN(Available_Bytes)) / MIN(Total_Bytes) AS decimal(7,4))
FROM volume_info
GROUP BY volume_mount_point;

Required permissions at SQL Server level to execute this query.

As for the output, you will receive a list containing an aggregated line for each volume that hosts data and/or Tlog files for databases hosted by the SQL Server instance you query. Note that if you have multiple instances of SQL Server storing data on the same volumes, this query will only show the data consumed by the databases of this instance; the data consumed by other instances will be part of Other_Usage_Pct. The type_desc for the aggregated line will be NULL. If applicable you will also receive a row regarding data files (type_desc = ROWS) and log files (type_desc = LOG). This particular output is for a Fail over Cluster Instance with a 250 GB data volume and a 35 GB Tlog volume.

volume_mount_point type_desc Database_Count File_Count Total_Volume_GB Database_Used_GB Available_GB Database_Used_Pct Available_Space_Pct Other_Usage_Pct
E:\ NULL NULL 80 249.9980 214.0164 13.8435 85.6073 5.5374 8.8553
E:\ LOG 4 4 249.9980 0.0146 13.8435 0.0059 NULL NULL
E:\ ROWS 65 76 249.9980 214.0018 13.8435 85.6014 NULL NULL
E:\tlog_mount NULL NULL 61 34.9971 13.5620 21.0367 38.7517 60.1100 1.1383
E:\tlog_mount LOG 61 61 34.9971 13.5620 21.0367 38.7517 NULL NULL