Custom alert on the SQL Database

 I wanted to create a custom alert on the SQL Database when number of records with some values(considered as invalid) exceeds the expected limit. 

First of all you need to Set up Database Mail for SQL 2005 and follow procedure
IMPORTANT: don't forget to Restart SQL Agent to activate settings. 

Similar to the article Define custom error messages in SQL Server 2005
I've defined the error
EXEC sp_addmessage 60001, 1, N'Number of not-processed  tasks %d exceed the limit on %s.'
and SP:
 
ALTER PROCEDURE [dbo].[CheckNotProcessed]
	@limit int = 10
AS

declare @StartDate datetime
declare @EndDate  datetime
declare @NotProcessedCount int
set @EndDate  =GetDate()
set @StartDate =DATEADD (day ,-1, @EndDate) print @StartDate print @EndDate select @NotProcessedCount=count(*) from dbo.[MyTBL] where [ProcessState] <> 99999 and createdDate between @StartDate and @EndDate if( @NotProcessedCount>@limit) begin declare @CurrentDBName varchar(60) set @CurrentDBName=DB_NAME() RAISERROR (60001, 10,1,@NotProcessedCount, @CurrentDBName) WITH LOG END /* -- ============================================= -- Example to execute the stored procedure -- ============================================= EXECUTE DBO.CheckNotProcessed 10 */

Note that if you have more than one similar database on the same server, it's important to specify  DB_NAME()
 
I've also created Job to call the SP on a regular basis.
Then I've created an alert using an error number (Enterprise Manager)

I fould that this approach is very powerful and allow to send monitor regular business pricesses, as well as get notifications about some  data conditions, that required investigation/debugging.
posted @ Saturday, July 4, 2009 7:07 AM
Print

Comments on this entry:

No comments posted yet.

Your comment:



(not displayed)

 
 
 
 

Live Comment Preview:

 
«November»
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456