Geeks With Blogs

News My Blog has been MOVED to
Michael Freidgeim's OLD Blog My Blog has been MOVED to
 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

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 on Saturday, July 4, 2009 7:07 AM SQL Server | Back to top

Comments on this post: Custom alert on the SQL Database

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Michael Freidgeim | Powered by: