November 12, 2010 at 7:45 am
Hi.
I want to create a alert in sql server 2005.
I already have a query & i want it to run continuously for 24 hrs if it exceeds the condition it has to raise an alert.
May i know what is the way to create this type of alerts.
Your help is appreciated.
November 12, 2010 at 8:48 am
If the condition is true then you may Raise an Error using Raise Error. You can configure an alert which will send out an email whenever that particular Error Numger is raised in the Errorlog. The sample script of an alert is an below.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'test',
@message_id=100001, --This is the error number raised using Raise Error. Can be changed
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'test', @operator_name=N'Production Support DBAs', @notification_method = 1
GO
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 12, 2010 at 8:59 am
DO i need to create any job for this query to run continuously..?
If Yes, This will effect any performance issues to my server.??
November 12, 2010 at 9:10 am
Since you are planning to run it round the clock, running it through a job is a better option. Running it through SSMS or SQL Agent job will have the same performance impact.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 12, 2010 at 12:34 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply