Wednesday 9 April 2014

SQL Alert for Disk space issues

I believe creating an SQL Alert for Disk space Alerts will definetly be useful for SQL Database Admins,

Especially this is benificail if we doesn't have any monitoring tool and SQL Admins may not have access to RDP server to setup windows level alerts and check for Disk space .

Below query will create SQL Alert to notify us if disk space is less than 1 GB if you need you can change the thresould value as well , please change the highlighted value .

if you want to change it to 2 GB please replace 2 instead of 1...

5 for 5 GB thresould value.

USE [msdb]
GO

/****** Object:  Alert [Disk Less that 1GB]    Script Date: 04/09/2014 01:52:22 ******/
/****** Author:  Hanuman D           ******/

EXEC msdb.dbo.sp_add_alert @name=N'Disk Less that 1GB',
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=14400,
  @include_event_description_in=1,
  @notification_message=N'Hi Team,                                                                                                                                                                                                                                Disk Drive has only 1 GB , please look into PRD server.
    
     Thanks,    
     Hanuman D',
  @category_name=N'[Uncategorized]',
  @wmi_namespace=N'\\.\ROOT\CIMV2',
  @wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 1000000000',
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO


Please Mail me if you need any assitance in setting up the DIsk Space Alert.

No comments:

Post a Comment