Month: September 2013

How to limit sql-job notifications by time

Rolling my own Notifications – I wanted a sql-job that ran every 5 minutes to Notify me when it failed – but not to “spam” me every 5 minutes until I fixed it!

Here’s the simplified last-job-step that would email me the first time the job failed, but then NOT again for half an hour.

declare @email varchar(500), @operator varchar(50)
set @operator = 'DBA'

if (select datediff(mi, max(send_request_date), getdate()) 
 from [msdb].[dbo].[sysmail_mailitems]
 where subject like 'ALERT%') > 30
begin
 set @email = 'execute msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''ALERT: [XX] failed '',
 @body=N''The [XX] sql-job on server [YY] has failed!'''
 exec(@email)
 select 'email sent'
end

Backup Status

Here’s my script that estimates when a running backup will finish …

--backup_status.sql
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle))) [CommandText]
FROM sys.dm_exec_requests r WHERE r.session_id > 50
--AND command IN ('RESTORE DATABASE','BACKUP DATABASE', 'KILLED/ROLLBACK')
--AND CONVERT(NUMERIC(6,2),r.percent_complete) > 0

Alert on low disk space

Here’s a script I wrote for SQL2008R2, to run hourly in a sql-job, and email me if the percentage of free disk-space is getting low.

-- space_alert.sql

     DECLARE @trig int = 40, -- percentage free
		@operator varchar(50) = 'DBA', -- operator with email address
		@drive varchar(50), @free varchar(50), @email varchar(300)

-- capture free-space to temp-table

	DROP TABLE #space ------------testing
     select volume_mount_point drive, cast(sum(available_bytes)*100 / sum(total_bytes) as int) Free
     into #space
     from sys.master_files f
     cross apply sys.dm_os_volume_stats(f.database_id, f.[file_id])
     group by volume_mount_point

-- loop through the table row-by-row

     while (select count(*) from #space) > 0
     begin
          set @drive = (select top 1 drive from #space order by drive)
          set @free = (select top 1 free from #space order by drive)

-- and send email if space low

          if @free < @trig
          begin
               set @email = 'EXECUTE msdb.dbo.sp_notify_operator @name=N''' + @operator + ''', @subject=N''Space Alert (' + @@servername + @drive + @free + '%)'',@body=N''On '+ @@servername  + ' the Volume ' + @drive + ' has only ' + @free + '% free space.'''
               exec(@email)
          end

-- then remove current line from table

          delete from #space where drive = @drive
     END

NOTE: I am using @@servername as there are only default instances here.