Good Starting Places for SQL Server Alerts/Notifications?

Question

Just recently started having issues with a SQL Server Agent Job that contains a SSIS package to extract production data and summarize it into a separate reporting database.

I think that some of the Alerts/Notifications settings I tried playing with caused the problem as the job had been running to completion unattended for the previous two weeks.

So... Where's a good place to start reading up on SQL Agent Alerts and Notifications? I want to enable some sort of alert/notification so that I'm always informed:

1. That the job completes successfully (as a check to ensure that it's always executed), or
2. That the job ran into some sort of error, which should include enough info (such as error number) that I can diagnose the cause of the error

As always, any help will be greatly appreciated!

Answer 1

In each step of the job click on advanced then from there you can log to a file or to a table, this will have all errorcodes and other things why the job failed You should be able to see this also from the job history. Right click on the job-->view history, click on the + sign to expand, the click on each step and it will be in the lower panel

To set up notifications you need to set up an operator and the in the job on the notification tab you pick it from the email dropdown

Answer 2

Books Online is probably a good place to start (or at least I like it and generally find it useful).

SQLMenace and bofe made some good points. Here's my additional two cents:

I'd recommend configuring Database Mail rather than SQL Mail (i.e. SMTP vs. MAPI, which I think is deprecated anyway). Once you get the mail profile configured, you'll have to also configure the SQL agent to use that mail profile (which is just a page of settings for the agent properties), or else your SSIS job notifications won't actually get sent, even though you can successfully send a test email from Management Studio.

I don't use alerts as often as job notifications, so the only tricky thing I can recall about them is that if you're raising an error and you want the alert to email you when that happens, you have to make sure that the raised error gets written to the log. I think that just boils down to "RAISERROR ... WITH LOG"; here's the BOL link for the syntax details.