February 16, 2016 at 10:59 am
We have a job which runs daily at 8 AM in the morning but sometimes, the job is disabled and for days I don't get notifications and I assume that the job is running successfully. I would like to create a job that runs at 11am on that server that sends us an email if no log restore has been done in the last 3 or 4 hours. Basically i would like to get an alert if the log restore doesn't happen.
February 16, 2016 at 12:41 pm
Chitown (2/16/2016)
We have a job which runs daily at 8 AM in the morning but sometimes, the job is disabled and for days I don't get notifications and I assume that the job is running successfully. I would like to create a job that runs at 11am on that server that sends us an email if no log restore has been done in the last 3 or 4 hours. Basically i would like to get an alert if the log restore doesn't happen.
What happens if the second job gets disabled as well?
Why don't you simply set notifications for successful runs?
February 16, 2016 at 10:01 pm
Take a look at this article maybe this will help you out: https://www.mssqltips.com/sqlservertip/1803/auto-alert-for-sql-agent-jobs-when-they-are-enabled-or-disabled/
February 18, 2016 at 1:24 pm
Luis Cazares (2/16/2016)What happens if the second job gets disabled as well?
Why don't you simply set notifications for successful runs?
I have thought about setting the notification for successful runs but then again, what happens if the job is disabled.
declare @LastFileName VARCHAR(1000), @LastFileTimestamp VARCHAR(1000), @LastDate DATETIME2
SELECT @LastFileName=last_restored_file, @LastDate=last_restored_date FROM msdb.dbo.log_shipping_secondary_databases where secondary_database = 'EDW'
SELECT @LastFileTimestamp = @LastFileName
select @LastFileTimestamp = REVERSE(@LastFileTimestamp)
SELECT @LastFileTimestamp = REVERSE(SUBSTRING(@LastFileTimestamp,0,PATINDEX('%\%',@LastFileTimestamp)))
select @LastFileTimestamp = RIGHT(LEFT(@LastFileTimestamp,LEN(@LastFileTimestamp)-4),14)
select @LastFileTimestamp = STUFF(STUFF(STUFF(STUFF(STUFF(@LastFileTimestamp,5,0,'-'),8,0,'-'),11,0,' '),14,0,':'),17,0,':')
select @LastFilename AS LastFilename, @LastFileTimestamp AS TimestampOnLastFileUTC, @LastDate LastFileRestoredAt
I am trying to schedule a new job which runs every day at 10 and sends out notification if the job at 8 o'clock hasn't run. Can I get some help please 🙂
February 19, 2016 at 1:29 pm
Chitown (2/18/2016)
Luis Cazares (2/16/2016)What happens if the second job gets disabled as well?
Why don't you simply set notifications for successful runs?
I have thought about setting the notification for successful runs but then again, what happens if the job is disabled.
If the job is disabled, you won't get the notification. That's the way you realize it's not working.
For the second job, you might be able to use the sysjobhistory table. Maybe with a step like this:
IF NOT EXISTS(
SELECT *
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE run_date = CONVERT(char(8), GETDATE(), 112) --Today
AND run_time >= 80000 --Run at 8:00am or later
AND name = '') --Job name
RAISERROR(<params>);
Then create an alert for the error.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply