Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create Custom Alert. Expand / Collapse
Author
Message
Posted Friday, November 12, 2010 7:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:08 AM
Points: 52, Visits: 685
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.
Post #1019935
Posted Friday, November 12, 2010 8:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1020002
Posted Friday, November 12, 2010 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:08 AM
Points: 52, Visits: 685
DO i need to create any job for this query to run continuously..?
If Yes, This will effect any performance issues to my server.??
Post #1020012
Posted Friday, November 12, 2010 9:10 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
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
Post #1020024
Posted Friday, November 12, 2010 12:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, August 31, 2014 1:00 AM
Points: 464, Visits: 1,036
what is the alert you want to get:?


--SQLFRNDZ
Post #1020159
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse