Solved

SQL Agent Jobs

Posted on 2014-11-28
6
39 Views
Last Modified: 2016-06-16
Hello Guys,

I had question regarding resuming SQL Agent Jobs.
The problem that I'm facing is because of these jobs.
Actually every Saturday and Sunday, the servers in our environment are rebooted because
of patching or some other activity. Because of these the agent jobs that are scheduled on these servers
are stopped. Jobs which are usually getting affected because of these are full and differential backups.
My question is, Is there any possible way by which I can resume these jobs which are interrupted because of
server restart? Meaning as soon as the server is restarted and the SQL services are online, I want that the point at which the jobs stopped should resume again and execute until unless there is any failure or some other problem.

Kindly look into this issue and please give your comments.

Thanks in advance.
0
Comment
Question by:Abhinav Singh
6 Comments
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 40470561
You can have a job that runs when SQL Server Agent starts (on the Schedule Type of that job chose "Starts automatically when SQL Server Agent starts") but I think you should review the process of shutting down the database servers.
Try to set the jobs to run before or after the patch application. Talk with the system administrator team to define a better strategy.
0
 

Author Comment

by:Abhinav Singh
ID: 40470710
See, if I use "Starts automatically when SQL Server Agent starts", then I won't be able to schedule it for every day or every week. Currently the backup jobs has been scheduled to execute on daily basis.
We tried coming up with better strategy, but that too is not working out.
If you could just tell me that there is any other way or some query using which we could find the job was stopped and make it execute again from where it stopped, then it would be helpful.
Thanks for replying
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40473204
I meant to create a new job that every time SQL Server Agents starts it will check the status of the backup jobs. If those jobs were cancelled that it will run them again. Makes sense for you?
0
 
LVL 4

Assisted Solution

by:jimtpowers
jimtpowers earned 125 total points
ID: 40474658
There isn't any way for a backup job to pick up where it left off. Vitor's suggestion is correct. The job just has to start again at the beginning.
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 125 total points
ID: 40568901
Yes, if you cannot control when the server is rebooted, then you need to have a job that starts up when Agent starts up and checks the latest SQL backup job to see if the latest status is complete or not and act appropriately.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mssql SQL query 14 45
BULK LOGGED - log full 9 25
the whoisactive update 12 38
Generate Weekly Schedule 15 13
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now