SQL Server 2008, Disk Space and Database Size Alerts

SQL Server 2008, Disk Space and Database Size Alerts stored procedure to check your server

We were struggling with disk space on one of our SQL servers at CSL, and unfortunately we couldn't have alerts set up at the time, so off on the web I went and found a few decent articles, please see links below

MSDN Article
SQL Server Central Article

These pointed me in the right direction and enabled me to pretty much copy and paste the code for the basis of my stored procedure.

This code will send you an alert for low space, a statement of drive space and a list of database sizes, we then scheduled it as part of a job to run at various times of the day.

You will need to set up an email account for your server to use, I will try and add an article for this myself, but in the mean time have a look at this article by Pinal Dave on SQL Authority.com or this MSDN Article

I have added to the original code

SQL Code

USE [msdb]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--Change ALTER TO CREATE if this is new for the system

ALTER PROCEDURE [dbo].[usp_DiskFreeSpaceAlert]

@DriveCBenchmark int= 1024,

@OtherDataDriveBenchmark int= 10240,

@ProfileName sysname='DatabaseEmailProfileName',

@Recipients NVARCHAR(1000)='g.clayton@claytabase.co.uk;'

AS

 

IF EXISTS(SELECT FROM tempdb..sysobjects

WHERE id =object_id(N'[tempdb]..[##disk_free_space]'))

DROP TABLE ##disk_free_space

CREATE TABLE ##disk_free_space(

      DriveLetter CHAR(1) NOT NULL,

      FreeMB INTEGER NOT NULL)

 

DECLARE @DiskFreeSpace INT

DECLARE @DriveLetter CHAR(1)

DECLARE @AlertMessage VARCHAR(500)

DECLARE @MailSubject VARCHAR(100)

 

INSERT INTO ##disk_free_space

EXEC master..xp_fixeddrives

 

SELECT @DiskFreeSpace = FreeMB FROM ##disk_free_space where DriveLetter ='C'

 

SET @MailSubject =''+@@SERVERNAME+' Disk Analysis'

EXEC sp_send_dbmail @profile_name=@ProfileName,

@recipients = @Recipients,

@subject = @MailSubject,

@query=N'SELECT DriveLetter,CAST(CAST(CAST(FreeMB AS DECIMAL(18,2))/CAST(1024 AS DECIMAL(18,2)) AS DECIMAL(18,2)) AS VARCHAR(20)) + '' GB '' SpaceAvailable FROM ##disk_free_space'

 

/* Get db sizes */

DECLARE @dbName sysname

CREATE TABLE ##TempDBNames(

DatabaseName sysname,

DATABASE_SIZE int,

REMARKS varchar(254))

INSERT INTO ##TempDBNames

EXEC sp_databases

 

CREATE TABLE ##DBInfo(

dbName sysname,

name sysname,

filepath sysname,

dbType VARCHAR(10),

dbSize INT,

DataModified DATETIME

)

 

DECLARE dbs CURSOR FOR

SELECT DatabaseName FROM ##TempDBNames

 

open dbs

fetch next from dbs into @dbName

WHILE (@@FETCH_STATUS= 0)

Begin

EXEC ('USE '+ @dbName +' INSERT INTO ##DBInfo SELECT '''+ @dbName +''',name,physical_name,type_desc,size,(SELECT MAX(modify_date) FROM sys.tables) LastModified FROM sys.database_files')

fetch next from dbs into @dbName

End

close dbs

deallocate dbs

 

SET @MailSubject =''+@@SERVERNAME+' Database Analysis'

EXEC sp_send_dbmail @profile_name=@ProfileName,

@recipients = @Recipients,

@subject = @MailSubject,

@query=N'SELECT dbName,dbType,(dbSize*8)/1024 dbSize,DataModified FROM ##DBInfo'

 

DROP TABLE ##DBInfo

DROP TABLE ##TempDBNames

/* End get dbsizes */

 

IF @DiskFreeSpace < @DriveCBenchmark

Begin

SET @MailSubject ='Drive C free space is low on '+@@SERVERNAME

SET @AlertMessage ='Drive C on '+@@SERVERNAME+' has only '+  CAST(@DiskFreeSpace AS VARCHAR)+' MB left. Please free up space on this drive. C drive usually has OS installed on it. Lower space on C could slow down performance of the server'

 

EXEC sp_send_dbmail @profile_name=@ProfileName,

@recipients = @Recipients,

@subject = @MailSubject,

@body = @AlertMessage

End

 

DECLARE DriveSpace CURSOR FAST_FORWARD FOR

select DriveLetter, FreeMB from ##disk_free_space where DriveLetter not in('C')

 

open DriveSpace

fetch next from DriveSpace into @DriveLetter,@DiskFreeSpace

 

WHILE (@@FETCH_STATUS= 0)

Begin

if @DiskFreeSpace < @OtherDataDriveBenchmark

Begin

set @MailSubject ='Drive '+ @DriveLetter +' free space is low on '+@@SERVERNAME

set @AlertMessage = @DriveLetter +' has only '+cast(@DiskFreeSpace as varchar)+' MB left. Please increase free space for this drive immediately to avoid production issues'

 

EXEC sp_send_dbmail @profile_name=@ProfileName,

@recipients = @Recipients,

@subject = @MailSubject,

@body = @AlertMessage

End

fetch next from DriveSpace into @DriveLetter,@DiskFreeSpace

End

close DriveSpace

deallocate DriveSpace

DROP TABLE ##disk_free_space

I hope you find this of use.
Ousia Logo