When I would have planned this upfront, I might have made a series about SQL Agent, but I didn’t.
So far this is what my blog contains about SQL Agent:
- Using alternate (non-time) triggers to start a SQL Agent job
- Making your SQL Agent job scripts generic for all servers
The reasons I have picked this subject again today, are two:
- I want to share some tips on using SQL Agent Schedules.
- Because I have made a major update to the SQLAgentJobStarter tool (the first post was about this) and wanted to bring this to your attention.
Tips about schedules
There are two things about SQL Agent jobschedules that “got” me in the past:
- Using meaningless names for schedules like “Schedule” because I thought it did not matter.
- Use a scripted SQL Agent job with a schedule as a starting point for a new job.
But both ways of working do have disavantages, which you can read below.
Using meaningless names for schedules
SQL Server Management Studio has an option for managing schedules, which is really convenient.
However if your schedules have meaningless names, you have to click through for every schedule (the number in the last column is a hyperlink) to find out which job uses this schedule.
On the other hand, if you add the jobname to the schedule name (I use the format “Schedule for <jobname>”), this is what you see! Looks far more clear to me.
Use a scripted SQL Agent job with a schedule as a starting point for a new job
What I did: script a job (that had a schedule), change the jobname in the script, and in that way, created a new job to edit further.
After I did this I painfully found out that both jobs shared the same schedule! I changed the schedule of one job, and then the change was made for both jobs!
This was caused by the (same) value for @schedule_uid in both scripts/jobs.
So if you work this way, make sure to generate a new guid for the second job (for instance with the T-SQL function NEWID()), to avoid that the schedule will be shared.
The scripted value for @schedule_uid ..
Using alternate (non-time) triggers to start a SQL Agent job
My tool SQLAgentJobStarter has got a major update:
- added “Running” as a state that can be checked for a job (in the previous version the values where “Succeeded”, “Failed” or “Completed”);
- added jobcondition property “reverse_condition”, which enables negative triggers, e.g. a file may not be found, a job may not be running;
- a check is done that the jobs used for jobtriggers and jobtrigger conditions actually exist, if not an error is thrown, so this can prevent a silent failure, when a job is renamed or removed after a jobtrigger has been made.
Read more here..
Conclusion / Wrap up
Because a lot of my time was consumed by the SQLAgentJobStarter improvements, this article is a short one.
Still I hope I have shared some useful insights about the SQL Server Agent and job schedules.
(c) 2017 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.