March 10, 2010 at 8:14 pm
we have a database backup job which backup some DB which has access to me and other team. i want to get the backup status thru an email everyday configured either in other job or other way. the backup job should not be altered in any case.
Kindly help me in this case.
Regards
Durai Nagarajan
March 10, 2010 at 8:59 pm
You have two choices. One is enable email notification on completion of the job. This is changing the job, but not the command, just adding the notification.
The other is that you can write a script to check and see if the job succeeded and then send an email. There system tables in msdb (sys.job_history or something similar) that contain the information you need.
March 10, 2010 at 10:57 pm
I like option 2. The problem with option two is that you will have to figure out the logic to determine when to run the script, which jobs to check (date range etc), and eliminate any false positive reporting that may generate from your script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 7:23 am
Jason,
Did you mean option 1? I tend to agree with you, though I might run a rollup each day looking for an overall look of what happened with jobs.
March 11, 2010 at 12:57 pm
Steve Jones - Editor (3/11/2010)
Jason,Did you mean option 1? I tend to agree with you, though I might run a rollup each day looking for an overall look of what happened with jobs.
My response sounds kind of backwards, but I meant option 2. Option 1 is completely viable and would be the easiest by far.
Option 2 leads to customization, increased knowledge, and avoids modifying the job at all (in case that is a strict rule).
We run a daily rollup in our environment and email the results of job success/fail for each Instance. With the rollup, no need to alter every job for an email alert.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 11, 2010 at 1:16 pm
I am with Steve on this, If you write script to check for rollup every day, what if t-logs jobs has been failing for whole day and you won't know jobs were until end of the day? what if you had to recover database point in time and you don't have t-logs, i would prefer sending an email\alert at that moment when job fails. Jason, i agree customization scripts will sure help us to increase your knowledge.
March 16, 2010 at 5:03 am
Hi sorry for replying late
thanks for your reply with that i have tried this.
select message from sysjobhistory
where step_name = 'Refresh'
and instance_id = ( select max(instance_id) from sysjobhistory
where step_name = 'Refresh'
)
and run_status in (0,3)
This the query i have written in a seperate job but i want the message in the email either in success or failure , is it possible kindly suggest.
or is there any other alternative
Note: i cant change the notification of the backup jon as it is meant to notify the other team.
Regards
Durai Nagarajan
March 16, 2010 at 5:05 am
the backup is bappening only once in a day so i can schedule it after 30 mins after the backup schedule.
Regards
Durai Nagarajan
March 16, 2010 at 10:13 am
durai nagarajan (3/16/2010)
the backup is bappening only once in a day so i can schedule it after 30 mins after the backup schedule.
I might give it a little more time than 30 minutes. Just to be certain that the backup has a fair chance of succeeding.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 16, 2010 at 12:08 pm
You could always set the status notification to run every 5 or 10 or X minutes and check to see if the job status / last run date & time has changed since the last email ran.
Use a table in a DBA Only database. Store the last job status & run time. Check that against what's in sys_Jobs_history (I think that's the table name). If there's a difference, have the job send out an email. If there's no difference, the job silently ends in success and waits another X minutes before it runs.
This way you can see both transaction log backups and regular backups all the time.
March 17, 2010 at 3:24 am
the above query with a job is just specifying it's runnign status succesful or failure is it a way to schedule job email when no data returned for the query or when returned (with certain condition).
Regards
Durai Nagarajan
March 19, 2010 at 9:01 am
Hi,
Kindly help me to get this resolved so that my job is easier.
durai
Regards
Durai Nagarajan
March 19, 2010 at 10:21 am
See my post on page 1 of this thread. Create a brand new job to run every X number of minutes (however often you want), then pull the data from the MSDB system tables. Books Online can give you a list of the tables and columns (with definitions).
Open up the Index and type "sysjob". Then look at the results one by one. Also, look up Database Mail to learn how to send an email via T-SQL.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply