Finding space usage of database files

DBA's are required to watch the space usage of database files in order to take preventive measures of future failures with respect to database full issues.
This will be usually required when there is a bulk activity happening on a database.
To do this make use of the below stored procedure.
Create this SP in a database and execute it in regular intervals to get the latest status of the database files.
This can be used to view the space usage of all the database files or for a particular threshold value.

Use master GO Create proc sp_SpaceUsageReport (@Threshold int=80) as CREATE TABLE tempdb..SpaceUsage (   DatabaseName nvarchar(100)  ,LogicalFileName nvarchar(500)  ,FileType nvarchar(10)  ,PhysicalFileLocation nvarchar(500)  ,[FileSize (MB)] float  ,[SpaceUsed (MB)] float  ,[FreeSpace (MB)] float  ,[% Used] AS 100-(([FileSize (MB)]-[SpaceUsed (MB)])/[FileSize (MB)])*(100)  ,[% Free]  AS (([FileSize (MB)]-[SpaceUsed (MB)])/[FileSize (MB)])*(100) ) If (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion')))) = 8 BEGIN EXEC sp_MSforeachdb 'USE [?]; INSERT INTO tempdb..SpaceUsage(DatabaseName            ,LogicalFileName            ,FileType            ,PhysicalFileLocation               ,[FileSize (MB)]            ,[SpaceUsed (MB)]            ,[FreeSpace (MB)]) SELECT DB_NAME() AS DatabaseName        ,name AS LogicalFileName        ,FileType = CASE WHEN FILEPROPERTY(name,''IsLogFile'')=0 THEN ''Data File'' WHEN FILEPROPERTY(name,''IsLogFile'')=1 THEN ''Log File'' END        ,filename AS PhysicalFileLocation        ,CONVERT(float,ROUND(size/128.000,2)) AS [FileSize (MB)]        ,CONVERT(float,ROUND(FILEPROPERTY(name,''SpaceUsed'')/128.000,2)) AS [SpaceUsed (MB)]        ,CONVERT(float,ROUND((size-FILEPROPERTY(name,''SpaceUsed''))/128.000,2)) AS [FreeSpace (MB)] FROM dbo.sysfiles ORDER BY FileType ' END If (SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<> 8 BEGIN EXEC sp_MSforeachdb 'USE [?]; INSERT INTO tempdb..SpaceUsage(DatabaseName            ,LogicalFileName            ,FileType            ,PhysicalFileLocation            ,[FileSize (MB)]            ,[SpaceUsed (MB)]            ,[FreeSpace (MB)]) SELECT DB_NAME() AS DatabaseName             ,name AS LogicalFileName             ,FileType = CASE WHEN type_desc =''Rows'' THEN ''Data File'' WHEN type_desc =''LOG'' THEN ''Log File'' END             ,physical_name AS PhysicalFileLocation             ,CONVERT(float,ROUND(size/128.000,2)) AS [FileSize (MB)]             ,CONVERT(float,ROUND(FILEPROPERTY(name,''SpaceUsed'')/128.000,2)) AS [SpaceUsed (MB)]             ,CONVERT(float,ROUND((size-FILEPROPERTY(name,''SpaceUsed''))/128.000,2)) AS [FreeSpace (MB)] FROM sys.database_files ORDER BY FileType' END if (Select COUNT(*) from tempdb..SpaceUsage where [% Used]>@Threshold)>0 Begin /* -- Enable this Content if you want to send email. DECLARE @table  NVARCHAR(MAX),@Subject Nvarchar(500) ; Set @Subject='[SQLAlert] Database Files Space Threshold exceeded Report from ' + CAST(@@SERVERNAME as nvarchar) SET @table =     N'<H1>Threshold Value for this Report is '+CAST(@Threshold AS nvarchar)+' Percentage. </H1>' +     N'<table border="1">' +     N'<tr><th>DatabaseName</th><th>FileType</th><th>PhysicalFileLocation</th><th>FileSize (MB)</th><th>% Used</th></tr> ' +     CAST ( ( Select td=DatabaseName, '',td=FileType, '',td=PhysicalFileLocation,'',td=CAST([FileSize (MB)] as nvarchar),'',td=CAST([% Used] AS nvarchar) from tempdb..SpaceUsage where [% Used]>@Threshold               FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) )    +     N'</table>' ; EXEC msdb.dbo.sp_send_dbmail @profile_name='DatabaseMail', --Change to your Profile Name       @recipients='sandeshsegu@SansSQL.com', --Put the email address of those who want to receive the e-mail     @subject = @Subject,     @body = @table,     @body_format = 'HTML' ;   */    select * from  tempdb..SpaceUsage where [% Used]>@Threshold                                  End DROP TABLE tempdb..SpaceUsage

To send an email of this report you need to

1. Configure Database Mail option. To configure Database mail option, follow this post.
2. Uncomment the below content in the SP.

/* -- Enable this Content if you want to send email. DECLARE @table  NVARCHAR(MAX),@Subject Nvarchar(500) ; Set @Subject='[SQLAlert] Database Files Space Threshold exceeded Report from ' + CAST(@@SERVERNAME as nvarchar) SET @table =     N'<H1>Threshold Value for this Report is '+CAST(@Threshold AS nvarchar)+' Percentage. </H1>' +     N'<table border="1">' +     N'<tr><th>DatabaseName</th><th>FileType</th><th>PhysicalFileLocation</th><th>FileSize (MB)</th><th>% Used</th></tr> ' +     CAST ( ( Select td=DatabaseName, '',td=FileType, '',td=PhysicalFileLocation,'',td=CAST([FileSize (MB)] as nvarchar),'',td=CAST([% Used] AS nvarchar) from tempdb..SpaceUsage where [% Used]>@Threshold               FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) )    +     N'</table>' ; EXEC msdb.dbo.sp_send_dbmail @profile_name='DatabaseMail', --Change to your Profile Name       @recipients='sandeshsegu@SansSQL.com', --Put the email address of those who want to receive the e-mail     @subject = @Subject,     @body = @table,     @body_format = 'HTML' ;   */

Usage of this SP:
This SP expects a parameter called @Threshold
If you specify the @Threshold=0 then, this SP will give space usage details of all the database files.

Exec sp_SpaceUsageReport @Threshold=0

If you specify the @Threshold=80 then, this SP will give space usage details of those database files which exceeds the threshold 80 percent.

Exec sp_SpaceUsageReport @Threshold=80