How to Set Space Usage Alert for SQL Server 2012 on Amazon RDS

alertsamazon-rdsdisk-spacesql serversql-server-2012

I have a database whose initial size is set to 1000 GB.
Is there a way where i can set an alert when the max space is used and database size is nearing to 1000 GB, for eg when it reaches maybe 995 or 994 GB , so that we get to know about it and take actions for increasing the disk space before hand. without sudden downtime.

We are using SQL Server 2012 with Amazon RDS as managed service.

Thanks in advance.

Best Answer

You could create a SQL Server Agent Job which will be run daily and check the free space by using a query like below:

USE [YourDatabaseName]

/* Size in MB */
DECLARE @Info TABLE (
    [Size]          decimal(12,2),
    [SpaceUsed]     decimal(12,2),
    [SpaceFree]     decimal(12,2)
)

insert @Info ([Size], [SpaceUsed], [SpaceFree])
SELECT
    [Size] = ISNULL(CONVERT(decimal(12,2),round(f.size/128.000,2)),0)
    ,[SpaceUsed] = ISNULL(CONVERT(decimal(12,2),round(fileproperty(f.name,'SpaceUsed')/128.000,2)),0)
    ,[SpaceFree] = ISNULL(CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,'SpaceUsed'))/128.000,2)),0)
    --,GETDATE() as DT
FROM sys.database_files f
    CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id) vs
WHERE f.name = 'YourFileLogicalName'

/* Free space less than  5GB */
If (SELECT TOP 1 [SpaceFree] FROM @Info) < 5120 BEGIN
    /*
        Anything is suitable for you:
        - send email
        - raiserror
        - write log
        .......

    */
    EXECUTE dbo.KickDBAToAwakeHim
END

Maybe, it has sense to create some table and write the values there every day to have information about how the space is changed.

Related Question