Room for Backups?

This script checks your backup drive to see if you have the room for SQL backups, and emails you a nice little message if you don't. It requires the xp_smtp_sendmail extended stored procedure, found at http://www.sqldev.net/xp/xpsmtp.htm.

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO

-- Free space checking for SQL backups to disk.

-- Returns an error if the free space on the disk is 

-- less than 2x the MDF total file size.

-- 

-- Parameters: @letter = Drive letter that the backups are on.

-- 

-- Example: exec proc_DiskSpace @letter = 'D'

--

-- D Maxwell

-- June 2007

CREATE  procedure proc_DiskSpace 

@letter char(1)  -- The letter of the backup drive.

AS

-- Get the drive space available first.

create table ##drivespace (

    letter char(1) not null,

    MB_free int not null

)

insert into ##drivespace exec master..xp_fixeddrives

declare @available int

set @available = (select (MB_free * 1024) from ##drivespace where letter = @letter)

-- Determine how much space we need (twice as much as the total MDF file size.)

declare @required int

set @required = (

    select ((sum(size) * 8) * 2)

    from master..sysaltfiles 

    where groupid = 1 and fileid = 1

    )

if @required < @available  -- Do we have the space we need?

    begin

        drop table ##drivespace

        return(0) -- Plenty of room for backups.

    end

else

    begin  -- Not enough room.

   -- For the log files.

    raiserror ('Drive space for backups is low.  Please investigate.', 16, 1)  

    declare @srvname nvarchar(15)

    declare @subj nvarchar(250)

    declare @msg nvarchar(1000)

    set @srvname = (select convert(nvarchar(15), serverproperty('ServerName')))

    set @subj = N'Disk Space Warning on server ' + @srvname + '.'

    set @msg = N'Disk space is low on server ' + @srvname + '.  Please investigate'

    declare @rc int

    exec @rc = master.dbo.xp_smtp_sendmail

        @FROM            = N'<FROM_ADDRESS>',

        @TO                = N'<TO_ADDRESS>',

        @subject        = @subj,

        @message        = @msg,

        @server         = N'<SMTP_SERVER_ADDRESS>'

    select RC = @rc 

    drop table ##drivespace

    return(1)

end

GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO