Disks are one of the
valuable resources in any database system, simply they are containing the
databases and log files that hold our data and transactions and that are why we
need to keep it available and have enough free space for normal database
operations.
We will demonstrate here
some alternatives for collecting and reporting the SQL Server server’s disks
and its current free space.
In fact, the way to collect
disk space information is differ from one SQL Server version to another, the
reason simply is that the objects used for collecting these figures are not
available on all SQL Server version.
Then if the targeted
version is SQL Server 2008 R2 SP 1 and above then we can use the new DMF
“Dynamic Management Function” sys.dm_os_volume_stats() that requires two parameters (the database_id and
file_id), by querying this DMF beside the catalog view sys.master_files to get the required result.
In the below script we
simply will go through the following steps:
1- Querying sys.dm_os_volume_stats() and catalog view sys.master_files and inserting the result set into a temp tables.
-- Step 1
SELECT DISTINCT SUBSTRING(volume_mount_point,1,1) AS [Drive], total_bytes/1048576 AS [Total_MB], available_bytes/1048576 AS [Available_MB],
(total_bytes/1048576)-(available_bytes/1048576) AS [Used_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),available_bytes/1048576)/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Available_Space_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(total_bytes/1048576)-(available_bytes/1048576))/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Used_Space_%]
INTO #SpaceUsed
FROM sys.master_files F cross apply sys.dm_os_volume_stats (F.database_id,F.file_id) V
ORDER BY SUBSTRING(volume_mount_point,1,1)
GO
2- Getting the used space
by databases for each drive into a common table expression “CTE”.
-- Step 2
WITH dbs_size (dbs_size_MB,Drive) AS(
SELECT SUM((size * 8)/1024)AS [dbs_size_MB],SUBSTRING(physical_name,1,1) AS [Drive] FROM sys.master_files
GROUP BY SUBSTRING(physical_name,1,1))
3- Querying the temp table #SpaceUsed and the common table expression dbs_size and adding the current date for the final result
set.
-- Step 3
SELECT S.Drive, S.Total_MB, S.Available_MB, S.Used_MB, S.[Available_Space_%],S.[Used_Space_%],D.DBs_Size_MB,
S.Used_MB-D.DBs_Size_MB AS [Other_Files_Size_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),D.DBs_Size_MB)/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [DB_Size_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(S.Used_MB-D.DBs_Size_MB))/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [Other_Files_Size_%],
CONVERT(DATE,GETDATE()) AS [date]
FROM #SpaceUsed S join dbs_size D ON S.Drive=D.Drive
GO
4- Dropping the temp table #SpaceUsed.
-- Step 4
DROP
TABLE #SpaceUsed
GO
The complete script:
-- SQL Server version: SQL Server 2008 R2 SP1 and above
USE
MASTER
GO
SELECT DISTINCT SUBSTRING(volume_mount_point,1,1) AS [Drive], total_bytes/1048576 AS [Total_MB], available_bytes/1048576 AS [Available_MB],
(total_bytes/1048576)-(available_bytes/1048576) AS [Used_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),available_bytes/1048576)/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Available_Space_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(total_bytes/1048576)-(available_bytes/1048576))/CONVERT(DECIMAL(18,2),total_bytes/1048576)*100 )AS [Used_Space_%]
INTO #SpaceUsed
FROM sys.master_files F cross apply sys.dm_os_volume_stats (F.database_id,F.file_id) V
ORDER BY SUBSTRING(volume_mount_point,1,1)
GO
WITH dbs_size (dbs_size_MB,Drive) AS(
SELECT SUM((size * 8)/1024)AS [dbs_size_MB],SUBSTRING(physical_name,1,1) AS [Drive]
FROM sys.master_files GROUP BY SUBSTRING(physical_name,1,1))
SELECT S.Drive, S.Total_MB, S.Available_MB, S.Used_MB, S.[Available_Space_%],S.[Used_Space_%],D.DBs_Size_MB,
S.Used_MB-D.DBs_Size_MB AS [Other_Files_Size_MB],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),D.DBs_Size_MB)/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [DB_Size_%],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),(S.Used_MB-D.DBs_Size_MB))/CONVERT(DECIMAL(18,2),S.Used_MB)*100) AS [Other_Files_Size_%],
CONVERT(DATE,GETDATE()) AS [date]
FROM #SpaceUsed S join dbs_size D ON S.Drive=D.Drive
GO
DROP
TABLE #SpaceUsed
GO