January 26, 2009 at 12:45 pm
I am currently working on setting up alerts based on certain errors to send emails immediately.
For testing purposes now, I am monitoring the following error:
14265: The MSSQLServer service terminated unexpectedly
I first ran exec sp_altermessage 14265, 'WITH_LOG', 'true', then created my alert to look for that error number. In the response tab, I have it running a job that sends out an email alert via xp_sendmail (not using SQL Mail for now).
However, when I type: raiserror(14265, 25, 127) WITH LOG, the error is raised and written to the log file. But I get this result:
Server: Msg 14265, Level 25, State 127, Line 1
The MSSQLServer service terminated unexpectedly.
Connection Broken
Any reasons why the job is not going out? When I run the email job on it's own, it works.
Thanks.
Gaby A.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 27, 2009 at 3:00 am
Assuming SQL 2000, you must already have SQL Mail set up anyway ... you also have to initiate the SQL Mail session first using xp_startmail, and terminate the session afterwards with xp_stopmail.
In this case, you might as well just set up SQL Agent Mail and send the email directly from the response action to the alert. The main advantage of using xp_sendmail is that the content can be generated dynamically using a query.
Unfortunately, SQL Agent Mail requires the installation of parts of Outlook on the server and it needs an Exchange (or other MAPI-compliant) server to talk to. I assume the unpopularity of this solution is why Microsoft decided to move to the SMTP-compatible Database Mail in SQL 2005 and later.
January 27, 2009 at 6:16 am
John Reid (1/27/2009)
Assuming SQL 2000, you must already have SQL Mail set up anyway ... you also have to initiate the SQL Mail session first using xp_startmail, and terminate the session afterwards with xp_stopmail.In this case, you might as well just set up SQL Agent Mail and send the email directly from the response action to the alert. The main advantage of using xp_sendmail is that the content can be generated dynamically using a query.
Unfortunately, SQL Agent Mail requires the installation of parts of Outlook on the server and it needs an Exchange (or other MAPI-compliant) server to talk to. I assume the unpopularity of this solution is why Microsoft decided to move to the SMTP-compatible Database Mail in SQL 2005 and later.
Thanks for the advice and it has helped, definitely. I'm trying a more benign message, 17162: SQL Server is starting at priority class '%1'(%2!d! %3 detected), that is generated when the server restarts.
When I run a raiserror(17162, 10, 127) from query analyzer, the alert triggers the job which sends the e-mail. But when I shutdown and restart the server, the custom alert doesn't seem to be triggered, even though the 'SQL Server is starting...' message shows up in the log.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 27, 2009 at 9:34 am
Is it possible that the Agent hasn't yet started at that point?
January 27, 2009 at 11:22 am
John Reid (1/27/2009)
Is it possible that the Agent hasn't yet started at that point?
Hmmm, I was wondering that. Now I'm hunting for a way to trap the last step that occurs in the errorlog when the Agent starts: Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'. If I trap this, then I'll have been able to catch a server restart and the appropriate DBA on duty could get paged immediately.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 28, 2009 at 2:29 am
If your objective is only to send an email alert when SQL starts, and assuming you also have SQL Server Agent set to start automatically, you could set up a job with a schedule of "Start automatically when SQL Server Agent starts" and use the normal SQL Mail to send the notification "Whenever the job completes".
January 28, 2009 at 6:26 am
John Reid (1/28/2009)
If your objective is only to send an email alert when SQL starts, and assuming you also have SQL Server Agent set to start automatically, you could set up a job with a schedule of "Start automatically when SQL Server Agent starts" and use the normal SQL Mail to send the notification "Whenever the job completes".
You know, I should have thought about that. I could tie it in with sp_readerrorlog for example and have the email send out whether it was a normal restart or an unscheduled one.
Thanks.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 28, 2009 at 7:23 am
I merely stand on the shoulders of giants: 🙂
Auto notification for failover of a server and restart of SQL Server services
That describes a solution for SLQ2K5 but the principle is the same ... the job could be anything as long as the notification gets triggered.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply