Reports on Disk Space and Space in Files

Previously I have blogged about how to check for free space in a database at a file level; a file for a database can be very large, but the amount of data stored within the file can be very little. When initializing a file for SQL, it is generally best practice to create it to the size you think it is going to be so that auto-grow is not initialized as this can cause a loss in performance. Invariably however, databases do grow, and there is a finite amount of space to grow into, which is why in another blog I wrote bout the “xp_fixeddrives” stored proc which returns the space remaining on the disks which are attached to that instance of SQL.

So instead of checking these at random times, or finding out that we are running out of space when I am told we have run out of space, I have written a script that checks for the size of the remaining space on the disks on our SQL Servers, and if those disks hit a certain threshold then I send an email alert to the DBA Team that the space is running low. This also triggers another email that sends a file space report for each of the databases that are hosted on the disk that is reporting as running low on disk space. Some of the drives don’t host databases, but I still like to send an email as it hints that something is wrong with the server.

The job runs via SQL Agent every 15 minutes. The good thing about these reports is that, if at 2am I see that we are running low on disk space, then I just check the next email that shows how much free space is left, so then I can determine the urgency of the warning. This relies on me to know the workload of each of our databases, but at least I have an opportunity to do something about it before SQL reports errors.

I’m not going to go into how to setup database mail here, because there are plenty of good articles on how to do that on the web. If you don’t have it set up then I recommend it, as you’ll find that it is an effective way of reporting warnings to a team who need to know and can rectify and potential issues before they actually occur.

You’ll need to make some changes to get it working (you’ll have to change the drive letters, or maybe you don’t have that many drives etc, and where it says ‘dbname’, you have to enter the name of your database) but all these are fairly straightforward.

If anyone has any suggestions to improve then let me know and I’ll update the script.

declare @N_MB_Free int
declare @O_MB_Free int
declare @M_MB_Free int
declare @L_MB_Free int
declare @B_MB_Free int
declare @C_MB_Free int
declare @E_MB_Free int
declare @FileSpaceReport bit = 0

Declare @FreeSpace table
( [Drive] char(1),
[MB_Free] int )

insert into @FreeSpace exec xp_fixeddrives

declare @reportCompletion Nvarchar(100)
SELECT @reportCompletion = GETDATE()

DECLARE
@BodyTxt03 VarChar(150),
@tableHTML NVARCHAR(MAX) ;

DECLARE

@BodyBackground NVARCHAR(50) ,
@BodyFont NVARCHAR(50),
@BodyFontColour NVARCHAR(50),
@BodyFontSize TINYINT,
@BodyText NVARCHAR(MAX),
@TableBorderWidth INT,
@TableBorderStyle NVARCHAR(20),
@TableBorderColour NVARCHAR(50),
@TablePadding INT,
@TableHeaderBackground NVARCHAR(50),
@TableHeaderFont NVARCHAR(50),
@TableHeaderFontColour NVARCHAR(50),
@TableHeaderFontSize TINYINT,
@TableDataBackground NVARCHAR(50),
@TableDataFont NVARCHAR(50),
@TableDataFontColour NVARCHAR(50) ,
@TableDataFontSize TINYINT,
@Subjecttext NVARCHAR(200)

--create table to send email

set @BodyBackground = 'light blue'
set @BodyFont = 'arial'
set @BodyFontColour = 'black'
set @BodyFontSize = 12
set @BodyText = NULL
set @TableBorderWidth= 1
set @TableBorderStyle = 'hidden'
set @TableBorderColour = 'black'
set @TablePadding = 1
set @TableHeaderBackground = 'gray'
set @TableHeaderFont = 'arial'
set @TableHeaderFontColour = 'Black'
set @TableHeaderFontSize = 12
set @TableDataBackground = 'light blue'
set @TableDataFont = 'arial'
set @TableDataFontColour = 'black'
set @TableDataFontSize = 12

SET @BodyTxt03 = 'The Report completed at '+ @reportCompletion +'. The following stored proc was executed to discover disk space: exec xp_fixeddrives'

SET @tableHTML =

N'<HEAD>'+
N'<STYLE type="text/css">'+
N'BODY { background-color:' + @BodyBackground + '; font-family:' + @BodyFont + '; font-size:' + CAST(@BodyFontSize AS NVARCHAR) + '; color:' + @BodyFontColour + ' }'+
N'TABLE { border-collapse:collapse }'+
N'TH { background-color:' + @TableHeaderBackground + '; font-size:' + CAST(@TableHeaderFontSize AS NVARCHAR) + '; color:' + @TableHeaderFontColour + ' }'+
N'TD { background-color:' + @TableDataBackground + '; font-size:' + CAST(@TableDataFontSize AS NVARCHAR) + '; color:' + @TableDataFontColour + ' }'+
N'TH, TD { border-width:' + CAST(@TableBorderWidth AS NVARCHAR) + '; border-style:' + @TableBorderStyle + '; border-color:' + @TableBorderColour + '; padding:' + CAST(@TablePadding AS NVARCHAR) + ' }'+
N'HTML { overflow: scroll }'+
N'</STYLE>'+
N'</HEAD>'+
N'<H2>Disk Size Database Report </H2>' +

N'<H4>'+ @BodyTxt03 +'</H4>' +

+ N'<table border="1" align = "Left">' +
N'<tr><th align = "left">Drive</th><th align = "left">MB Free</th>' +
CAST ( (select

td =[Drive],'',
td = [MB_Free],''

from @FreeSpace

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
+N'<table>'
+N'</tr>'
+N'</table>'
;

--check for each of the drives if the threshold has been hit. if so esend an email.
--If drive hosts adatabase we want to monitor then set the bit to 1, which triggers the 2nd part of this script
select @N_MB_Free = MB_Free from @FreeSpace where Drive = 'N'
if @N_MB_Free < 102400
BEGIN
set @Subjecttext = 'N Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @O_MB_Free = MB_Free from @FreeSpace where Drive = 'O'
if @O_MB_Free < 102400
BEGIN
set @Subjecttext = 'O Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @M_MB_Free = MB_Free from @FreeSpace where Drive = 'M'
if @M_MB_Free < 102400
BEGIN
set @Subjecttext = 'M Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @L_MB_Free = MB_Free from @FreeSpace where Drive = 'L'
if @L_MB_Free < 200000
BEGIN
set @Subjecttext = 'L Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @B_MB_Free = MB_Free from @FreeSpace where Drive = 'B'
if @B_MB_Free < 20480
BEGIN
set @Subjecttext = 'B Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @C_MB_Free = MB_Free from @FreeSpace where Drive = 'C'
if @C_MB_Free < 10240
BEGIN
set @Subjecttext = 'C Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @E_MB_Free = MB_Free from @FreeSpace where Drive = 'E'
if @E_MB_Free < 20480
BEGIN
set @Subjecttext = 'E Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

if @FileSpaceReport = 1
BEGIN
--We create table and send our dbname filegroup space report
--sending another email to show how much space we have left in the files
declare
@FileID int, @FileGroupName varchar (50), @FileSizeMB decimal(12,2), @MaxSizeMB decimal (12,2),
@SpaceUsedMB decimal(12,2), @FreeSpaceMB decimal(12,2), @FreeSpacePercentage decimal (12,2), @FileLocation nvarchar (80)

Declare @dbTableVar table(
[File ID] int
,[File Name] varchar(50)
,[File Size in MB] decimal(12,2)
,[Max Size in MB] decimal(12,2)
,[Space Used in MB] decimal(12,2)
,[Free Space in MB] decimal(12,2)
,[Free Space in %] decimal(12,2)
,[File Location] varchar (80)
)

DECLARE DB_cursor CURSOR
FOR SELECT fileID from dbname.dbo.sysfiles
OPEN DB_cursor
FETCH NEXT FROM DB_cursor into @fileID

WHILE @@FETCH_STATUS = 0
BEGIN
select @FileID = MAX (sf.FILEID)
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileGroupName = MAX(left(sf.NAME,50))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileSizeMB = MAX (convert(decimal(12,2),round(sf.size/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @MaxSizeMB = MAX (convert(decimal(12,2),round(sf.maxsize/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @SpaceUsedMB = MAX (convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FreeSpaceMB = MAX (convert(decimal(12,2),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FreeSpacePercentage = MAX (convert(decimal(12,2),round(100*(sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileLocation = MAX(left(sf.NAME,50))
from dbname.dbo.sysfiles sf
where fileid = @FileID

insert into @dbTableVar values (@FileID, @FileGroupName, @FileSizeMB, @MaxSizeMB, @SpaceUsedMB, @FreeSpaceMB, @FreeSpacePercentage, @FileLocation)

FETCH NEXT FROM db_cursor
INTO @fileID
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT @reportCompletion = GETDATE()
DECLARE
@BodyTxt10 VarChar(150)

-- Send Email message stating processing status
--create table to send email

set @Subjecttext = 'Filegroup Report for dbname'

SET @BodyTxt10 = 'The Report completed at '+ @reportCompletion

SET @tableHTML =

N'<HEAD>'+
N'<STYLE type="text/css">'+
N'BODY { background-color:' + @BodyBackground + '; font-family:' + @BodyFont + '; font-size:' + CAST(@BodyFontSize AS NVARCHAR) + '; color:' + @BodyFontColour + ' }'+
N'TABLE { border-collapse:collapse }'+
N'TH { background-color:' + @TableHeaderBackground + '; font-size:' + CAST(@TableHeaderFontSize AS NVARCHAR) + '; color:' + @TableHeaderFontColour + ' }'+
N'TD { background-color:' + @TableDataBackground + '; font-size:' + CAST(@TableDataFontSize AS NVARCHAR) + '; color:' + @TableDataFontColour + ' }'+
N'TH, TD { border-width:' + CAST(@TableBorderWidth AS NVARCHAR) + '; border-style:' + @TableBorderStyle + '; border-color:' + @TableBorderColour + '; padding:' + CAST(@TablePadding AS NVARCHAR) + ' }'+
N'HTML { overflow: scroll }'+
N'</STYLE>'+
N'</HEAD>'+
N'<H2>File Size Database Report </H2>' +

N'<H4>'+ @BodyTxt10 +'</H4>' +

+N'<table border="1" align = "Left">' +
N'<tr>
<th align = "left">File ID</th>
<th align = "left">File Name </th>
<th align = "left">Total File Size (MB)</th>
<th align = "left">Max File Size (MB)</th>
<th align = "left">Space Used (MB)</th>
<th align = "left">Free Space (MB)</th>
<th align = "left">Free Space (%age)</th>
<th align = "left">File Location</th>' +
CAST ( (select
td = [File ID],'',
td = [File Name],'',
td = [File Size in MB],'',
td = [Max Size in MB],'',
td = [Space Used in MB],'',
td = [Free Space in MB],'',
td = [Free Space in %],'',
td = [File Location],''

from @dbTableVar

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
+N'<table>'
+N'</tr>'
+N'</table>'
;
if @tableHTML <> ''
begin
set @tableHTML = @tableHTML
end
else
begin
set @tableHTML = 'No batches processed'
end

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';

END

bzzzt!

What's Going On?

Reports on Disk Space and Space in Files