Well the good news is that the issue causing SQL Server 2005 to restart itself is fixed in SP3. The bad news is that I do not have a test server to test the upgrade on. While working to get a test server setup, I decided to create some SQL Server Alerts. At least then I will know just a few moments after the event happens. Currently I would find out about the error if users reported a problem or during my daily review of the SQL Server Logs.
Here is how I setup SQL Server 2005 alerts.
1. Setup Database Mail. Just follow the wizard it will lead you to safety. When finished you can right click on Database Mail and Choose Send Test Email to make sure you have configured it properly.
2. Right Click on Database Mail and click Configure. Choose the Manage Profile Security option and click next. Make sure your public profile is set as the Default Profile.
3. Setup some operators. Give the operator a name and an email address at minimum.
4. Create your new alert. Right click alerts under SQL Server Agent and pick new alert.
- Give your alert a name.
- Under the Alerts will be raised based on section choose the error number.\
- In my instance the following error message would signal the service restart: Error: 3449, Severity: 21, State: 1 So for error number I typed: 3449
- On the response tab check the Notify Operators and choose who gets notified.
- On the Options tab check the include alert error text in Email option. Click OK. You have now created the alert.
5. Right click on the SQL Server Agent and choose Properties. Click on the Alert System option and enable the Mail Profile. Choose Database Mail for your mail system and then choose the profile you setup in the step above. Click OK. You will then need to restart your SQL Server Agent Service.
There are tons of performance conditions and error messages you can monitor using the method above. You can also set alerts up to fire on you SQL Server Jobs. Handy if you have a job that has started failing and you want to keep a close eye on it. However if you have 100s of jobs to monitor you probably want to setup some kind of Centralized Management Server to monitor that. You do not want to go add alerts to every single one of your jobs.
Once you have the Database Mail, Operator, and Alert system setup you can explore the space and use it for all sorts of monitoring scenarios. Run wild.
Update: You can use the RAISERROR system function to test your alerts.
JustSQL 