SQL Alerts | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Alerts

hi there I’m going crazy over this one, i cant get Alerts to fire in SQL 2005, theres nothing wrong with dbmail or the operator set up as Ive tested that, but no alerts are being sent to the applications log and the Alerts are not firing, I’m trying to test out Alerts for deadlocks and already existing DB errors (message_ids 1205 and 1801). Ive set these up as per the MS documentation, alerts are enabled and the SQL Agent is going. Do I have to enable something else first, Ive already opened up basically all the options in Surface Config. Have tried it out on different Servers but no joy any help would be great thanks
are you seeing the errors in the application log? have you tried to RAISEERROR with log to force an error that would trigger the alert? Wes
thanks for the reply the errors are not raised in the app log how do i use the raiseerror with log? cant find too much on it on the web, but thats what i want to do is test the other alerts ie severity as well, will this do that?
have checked that out, and everything works fine doing that, the nofication comes through Still cant get the system ones ie 1205 etc to work though, but thanks
You have had 1205 errors come through since you setup the alerts?
The only info i could get logged to the sql error log were when i put a trace on to do so, but even then the alerts were not firing, the deadlocks were occuring and showing in the trace info. nothing is or has been logging to the app log which is were the issue is i think In sys.messages the column ‘is_event_logged’ is set to 0, is there any way of changing this so it will log? Ive tried sp_altermessage but no joy, It doesnt make sense to me why these system messages cant be logged I dont have to have service broker set on the msdb and user db do I? as that is only for wmi events cheers
You will only get an alert if it is set to log in the event log I do belive I’ll see if I can’t get a better answer for you. Wes
SQL Server 2005 Books Online Behavior Changes to Database Engine Features in SQL Server 2005
SQL 2000 behaviour
sp_altermessage
sp_altermessage can be used to specify whether or not a system message (a message with Message ID < 50000) is to be written to the Windows Application log.
SQL 2005 behaviour
sp_altermessage cannot be used to change the logging behavior of system messages (messages with Message ID < 50000). To audit system messages, use SQL Trace and the User Error Message Event Class. For more information, see Introducing SQL Trace. Did you try using WMI alerts??? check the following and also did you enable mail profile in SQL Agent properties… SQL Server 2005 Books Online
Sample: Creating a SQL Server Agent Alert by Using the WMI Provider for Server Events
http://msdn2.microsoft.com/en-us/library/ms186385.aspx
MohammedU.
Moderator
SQL-Server-Performance.com
– With sql2000 you needed to stop/start sqlagent when you activated your first alert. – If you want to capture deadlock info, just startup sqlserver with the startupparameters
-T1204 (or the new 1222 for sql2005)
-T3605 or start it dbcc traceon(1204,3605,-1)
thanks for the replies I’m developing an in house DBA admin toolset which will go across a number of servers, so i’m not wanting to put a trace on or use profiler due to the overhead, so there is no actual deadlocking issue at present, I’m just wanting to get notified if they start coming up, in 2000 this could be done, 2005 makes it harder, I’ll have a look at WMI alerts and see if it can be done thanks again
Check the link provided in my post… SQL Server 2005 Books Online
Sample: Creating a SQL Server Agent Alert by Using the WMI Provider for Server Events
http://msdn2.microsoft.com/en-us/library/ms186385.aspx
MohammedU.
Moderator
SQL-Server-Performance.com
]]>