As a DBA, you get alerts many times regarding the database file space issue. To address the issue, you may be following the steps.

• Which is the particular database file in the database which is having less space?
• How much disk space available on the physical disk where the file resides?

The below script will provide you SQL database file wise disk space utilization details.

SELECT
       DB_NAME()DatabaseName,
       f.[file_id],
       f.name AS [File Name] ,
       f.physical_name AS [Physical Name],
       CAST((f.size/128.0) AS DECIMAL(15,2)) AS [File - Total Size In MB],
       CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [File - Available Space In MB],
       fg.name AS [Filegroup Name],
       volume_mount_point [Disk Mount Point],
       CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Disk Total Size in GB], ---1GB = 1073741824 bytes
       CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Disk Available Size in GB], 
       CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Disk Free Space %],
       f.is_percent_growth,
       file_system_type [File System Type]
FROM sys.database_files AS f WITH (NOLOCK)
INNER JOIN sys.master_files m on f.file_id = m.file_id
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id)
WHERE m.database_id = DB_ID()
ORDER BY 6 OPTION (RECOMPILE)

The output of the above query:

Click on the Image to zoom it

In the query output, if you follow the columns “Disk Mount Point”, “Disk Total Size in GB”, “Disk Available Size in GB”, and “Disk space Free %”,  you may see the columns values are repeating many times. It is because of many database files kept on the same disk. Kindly don’t get confused that there are many disks with the same name.

Create Stored Procedure

To make things easier for DBAs, Let’s put the T-SQL into a stored procedure so that they can quickly refer the stored procedure when there is a need.

Note: When you are deploying the stored procedure, you have to replace the <DATABASENAME> with the database name where you want to keep this SP.

USE <DATABASENAME> 
go
CREATE PROCEDURE dbo.SQL_Database_File_Wise_Disk_Space @DatabaseName sysname
AS
BEGIN
SET NOCOUNT ON
DECLARE @sql NVARCHAR (MAX)
set @sql = 'USE [' + @DatabaseName + ']'+'select 
			'''+ @DatabaseName +''' DatabaseName,
			f.[file_id],
			f.name AS [File Name] ,
			f.physical_name AS [Physical Name],
			CAST((f.size/128.0) AS DECIMAL(15,2)) AS [File - Total Size In MB],
			CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(15,2)) AS [File - Available Space In MB],
			 fg.name AS [Filegroup Name],
			 volume_mount_point [Disk Mount Point],
			 CONVERT(DECIMAL(18,2),total_bytes/1073741824.0) AS [Disk Total Size in GB], ---1GB = 1073741824 bytes
			 CONVERT(DECIMAL(18,2),available_bytes/1073741824.0) AS [Disk Available Size in GB], 
			 CAST(CAST(available_bytes AS FLOAT)/ CAST(total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Disk Free Space %],
			 f.is_percent_growth,
			 file_system_type [File System Type]
			FROM ' + @DatabaseName + '.sys.database_files f
			INNER JOIN '+ @DatabaseName+'.sys.master_files m on f.file_id = m.file_id
			LEFT OUTER JOIN '+ @DatabaseName+'.sys.data_spaces AS fg WITH (NOLOCK) ON f.data_space_id = fg.data_space_id
			CROSS APPLY '+ @DatabaseName+'.sys.dm_os_volume_stats(database_id, m.file_id)
			WHERE m.database_id = DB_ID('''+@DatabaseName+''')
			ORDER BY 6 OPTION (RECOMPILE)'
EXEC (@sql)
END

EXEC <DATABASENAME>.dbo.SQL_Database_File_Wise_Disk_Space 'DATABASENAME'

In my recent blog, I discussed about Disk Space Monitoring using SQL Server DMV. you might find it useful.

Happy Learning!