2

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.

  • What does it mean "initial size is set to 1000 GB"? Is it the size of a particular database file or something? – Denis Rubashkin Jun 18 '18 at 10:14
  • Yes size of a particular database file. By initial size i meant : Right click on the database , click properties ---> Files ---> Initial Size (MB) – Sayantani Nath Jun 18 '18 at 10:23
1

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.

  • Hi, Can i know what is dbo.KickDBAToAwakeHim ? any separate stored procedure ? – Sayantani Nath Jun 19 '18 at 7:24
  • Hi, it's just some procedure which you may need to create to notify your DBA about the issue – Denis Rubashkin Jun 19 '18 at 10:28
1

Cloudwatch can allow you to set up alarms to monitor such things. Here are the steps I followed on a test instance I just spun up:

  1. Go to the Cloudwatch service
  2. Select Alarms on the left sidebar
  3. Search for RDS in the search box
  4. Select the row with "FreeStorageSpace" for the instance you are concerned about
  5. Click Next
  6. Set the threshold to be <= whatever cutoff you want
  7. Choose what action you want to occur at the bottom of the Define Alarm Page. This includes being able to send an email to whatever address you would like.
  8. Click Create Alarm
  • But This cloudwatch will show only about instance level free space if we set an alert isnt it ? It wont show the free space left out of the 1000gb file of a particular database. By file i meant in SSMS, right click properties on a database --> files --> Initial file size (MB) for mdf file. – Sayantani Nath Jun 20 '18 at 6:17
1

Use CloudWatch to set an alert on the FreeStorageSpace metric.

Steps:

  1. Click Metrics in the left hand navigation.
  2. In the All metrics tab Click RDS under the AWS NameSpaces
  3. Click Per-Database Metric
  4. Select the FreeStorageSpace
  5. Click the Graphed metrics tab
  6. Click the bell under the Actions column Set the alert to be less than a certain amount of Bytes
  7. Set how many consecutive data points need to be below your is value
  8. In the actions section choose or create an SNS group to send the alert to.
  • But This cloudwatch will show only about instance level free space if we set an alert isnt it ? It wont show the free space left out of the 1000gb file of a particular database. By file i meant in SSMS, right click properties on a database --> files --> Initial file size (MB) for mdf file. – Sayantani Nath Jun 20 '18 at 6:16
  • That’s correct. If you are looking for file level sizes you will need to write a query using the dvm objects to get each files size and run it on an agent job. Then you will need to alert off of that result. You can still use cloud watch to alert if you send the results to a custom cloud watch metric for each file. I’m not sure if the rds instances have the aws cli or tool installed. If it doesn’t you can also just send a message to sns for the alert if you don’t care about historical values – Aaron Jun 20 '18 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.