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.
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.