SQL Server Agent Question

Westside2004
Westside2004 used Ask the Experts™
on
Hi,

I had a couple of quick questions around SQL Server Agent and Database Mail.  I am using SQL 2008

1. Other than setting up database mail, is there a more preferred way of being notified if a job (SQL Server Agent Job) succeeds?  

2. How can you be notified BOTH if the job succeeds or fails?  It seems like you have to choose one or the other.

3. Right now I have a job on each database server that loops through the databases on a given server and then backs it up to a network share for now.  I have to have each job though on each database server though, is there a way to manage the jobs centrally so I don't have to setup the same job on each server?

Thanks as always..

-ws

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Manager, Database Engineering
Commented:
1.  you can try notification services for sending the alert message which you can customize as per your need.

for sending mail, the database mail is the preferred way.

2. you can write procedure and schedule in job to notify you for job failed/sucess. you can use  sysjobhistory table to get that information about job status (run_status).

3. if you want to manage centrally then you can register all server in your management studio.
  you need to have job on each server.
Regarding #3, have you explored setting up a Master Server: http://msdn.microsoft.com/en-us/library/ms175104.aspx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial