SQL Server disk usage

Since DBA are very busy people, it is helpful to have queries (and reports) that gives info about disk usage on SQL Server instance.

It is quite easy to have insight about disk usage for one database. Run this query on a specific database :

WITH FileByFile AS
(
 SELECT
  v.volume_mount_point AS Volume_MountPoint,
  MAX(v.total_bytes) AS Volume_TotalBytes,
  MAX(v.available_bytes) AS Volume_AvailableBytes,
  f.type_desc AS FileType,
  SUM(ISNULL(f.size, 0) -
   CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS BIGINT))
    * 8 * 1024 AS DB_FreeSpaceBytes,
  SUM(CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS BIGINT))
   * 8 * 1024 AS DB_UsedBytes
 FROM sys.database_files f WITH (NOLOCK)
 CROSS APPLY sys.dm_os_volume_stats(DB_ID(), file_id) AS v
 GROUP BY v.volume_mount_point, f.type_desc
)
SELECT
 f.Volume_MountPoint,
 f.FileType,
 MAX(f.Volume_TotalBytes) AS Volume_TotalBytes,
 MAX(f.Volume_AvailableBytes) AS Volume_AvailableBytes,
 SUM(f.DB_FreeSpaceBytes) AS DB_FreeSpaceBytes,
 SUM(f.DB_UsedBytes) AS DB_UsedBytes
FROM FileByFile f
GROUP BY f.Volume_MountPoint, f.FileType

OK, it says for each volume used by my database :

• The drive letter of the volume
• The file type (is it used by LOGS or ROWS)
• The total size of the volume
• The free space on the volume
• The free space of the selected database
• The space actually used by the selected database

It is interesting, but I would prefer to know the space used by EVERY database of the SQL instance, instead of only one database.
So let's create a cursor, iterate over all databases, and do some dynamic SQL to achieve this.

DECLARE @Stmt NVARCHAR(MAX);
DECLARE @DatabaseID AS INT;
DECLARE @DatabaseName AS NVARCHAR(50);
 
DECLARE @DatabaseCursor AS CURSOR;
 
SET @DatabaseCursor = CURSOR FOR
    SELECT name, database_id FROM sys.databases WHERE HAS_DBACCESS(name) = 1 ;

IF OBJECT_ID('tempdb..##VolumeInfo') IS NOT NULL DROP TABLE ##VolumeInfo ;

CREATE TABLE ##VolumeInfo
(
 DatabaseName SYSNAME NOT NULL,
 Volume_MountPoint SYSNAME NOT NULL,
 FileType SYSNAME NOT NULL,
 Volume_TotalBytes BIGINT NOT NULL,
 Volume_AvailableBytes BIGINT NOT NULL,
 DB_FreeSpaceBytes BIGINT NOT NULL,
 DB_UsedBytes BIGINT NOT NULL
)
 
OPEN @DatabaseCursor;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @Stmt = '
 USE [' + @DatabaseName + '];
 WITH FileByFile AS
 (
  SELECT
   CAST(''' + @DatabaseName + ''' AS SYSNAME)
    COLLATE Latin1_General_CI_AI AS DatabaseName,
   v.volume_mount_point AS Volume_MountPoint,
   MAX(v.total_bytes) AS Volume_TotalBytes,
   MAX(v.available_bytes) AS Volume_AvailableBytes,
   f.type_desc AS FileType,
   SUM(ISNULL(f.size, 0) -
    CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS BIGINT))
    * 8 * 1024 AS DB_FreeSpaceBytes,
   SUM(CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS BIGINT))
    * 8 * 1024 AS DB_UsedBytes
  FROM sys.database_files f WITH (NOLOCK)
  CROSS APPLY sys.dm_os_volume_stats(DB_ID(''' + @DatabaseName + '''), file_id)
    AS v
  GROUP BY v.volume_mount_point, f.type_desc
 )
 INSERT INTO ##VolumeInfo
 SELECT
  f.DatabaseName,
  f.Volume_MountPoint,
  f.FileType,
  MAX(f.Volume_TotalBytes) AS Volume_TotalBytes,
  MAX(f.Volume_AvailableBytes) AS Volume_AvailableBytes,
  SUM(f.DB_FreeSpaceBytes) AS DB_FreeSpaceBytes,
  SUM(f.DB_UsedBytes) AS DB_UsedBytes
 FROM FileByFile f
 GROUP BY DatabaseName, f.Volume_MountPoint, f.FileType'

 EXEC sp_sqlexec @Stmt ;

 FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
 
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

SELECT
 f.DatabaseName, f.Volume_MountPoint, f.FileType,
 'File Used' AS Usage,
 f.DB_UsedBytes AS ValueInByte,
 f.DB_UsedBytes / (1024.0 * 1024 * 1024) AS ValueInGB
 FROM ##VolumeInfo f
 WHERE f.DB_UsedBytes <> 0
UNION ALL
SELECT f.DatabaseName, f.Volume_MountPoint, f.FileType,
 'File Unused' AS Usage,
 f.DB_FreeSpaceBytes AS ValueInByte,
 f.DB_FreeSpaceBytes / (1024.0 * 1024 * 1024) AS ValueInGB
 FROM ##VolumeInfo f
 WHERE f.DB_FreeSpaceBytes <> 0
UNION ALL
SELECT DISTINCT 'Available' AS DatabaseName, f.Volume_MountPoint,
 'Available' AS FileType,
 'Available' AS Usage,
 f.Volume_AvailableBytes AS ValueInByte,
 f.Volume_AvailableBytes / (1024.0 * 1024 * 1024) AS ValueInGB
 FROM ##VolumeInfo f
UNION ALL
SELECT
 'Unknown' AS DatabaseName,
 f.Volume_MountPoint,
 'Unknown' AS FileType,
 'Unknown' AS Usage,
 MAX(f.Volume_TotalBytes)
  - MAX(f.Volume_AvailableBytes)
  - SUM(f.DB_UsedBytes)
  - SUM(f.DB_FreeSpaceBytes) AS ValueInByte,
 (MAX(f.Volume_TotalBytes)
  - MAX(f.Volume_AvailableBytes)
  - SUM(f.DB_UsedBytes)
  - SUM(f.DB_FreeSpaceBytes)) / (1024.0 * 1024 * 1024) AS ValueInGB
FROM ##VolumeInfo f
GROUP BY f.Volume_MountPoint

DROP TABLE ##VolumeInfo

We get, for each drive and database, how many space is used / unused.
In addition, for every drive, we get the available space, and the unknown space... ie the space that is used on the volume but not by a database. This may be system files, backups, etc.

Based on this query, we can easily build some charts (on Reporting Services or Excel, for instance) to display important information. Here is waht I usually report :

Very helpful to know which filegroups to shrink !