Most DBAs are aware that they can simply right click on a SQL Server Agent Job from within the SQL Server Management Studio GUI and start the job at any time desired by using the Start Job at Step menu command:
But, when you do that, you’re executing the SQL Server Agent Job in question with YOUR credentials. As such, this option represents a pretty poor way to test out whether a newly created job will work or not—simply because it means you’ll typically be running the job with elevated permissions (or with different permissions than that what the job will be running as when it’s actually scheduled or executed).
Testing (Important) New Jobs
For jobs where you might need to test SQL Server’s ability to use Credentials and Proxies (for elevated permissions), or where you simply can’t afford a job failure (due to either a logic bomb in your code somewhere or due to permissions errors, or anything else), the easiest way to determine if a job scheduled to run sometime fairly far out into the future will actually work or not, is to use a One Time schedule.
Granted, for jobs that you’re spinning up that are running, say, every 5 or 10 minutes there’s no need to bother checking to see if it’ll run correctly—you can just wait a few minutes and see if it’s going to work.
But, if you’ve just set up a new maintenance routine, changed your entire backup strategy, or have pushed out a batch processing job that you want to make sure will actually execute at 3am tomorrow morning, do you really want to wait until either that job fails (and notifies you in the middle of the night), or until you stumble into the office tomorrow morning to find out if it actually worked as expected or not? (In some cases, the logic in these jobs will more or less dictate that you CAN’T pre-emptively run/test them—so you’ll have to wait. But, more often than not, you should be able to test these jobs immediately—so that there are no surprises later on when it’s supposed to run but fails because you weren’t able to test it.)
Creating and Using One Time Schedules
Creating and using one time schedules is pretty trivial. I think most DBAs simply overlook them because it’s pretty rare that you’d want to schedule a job to ONLY ever run once. Still, one time schedules represent a great way to verify if a job will work or not—without having to wait hours or days to see if you configured everything correctly.
To use one time schedules for testing, simply do the following:
- Create and Schedule your job as you normally would, i.e., set up the job, the job steps, create and define the schedule, set up alerts for failure, and so on
-
Then, go back into the job and click on the Schedules tab—and click on the New button to create a new schedule:
- Give this schedule a name, like "test schedule" or something similar.
-
Then, from the Schedule type drop-down, select the One time schedule type:
- Keep a close eye on the clock/time on the SERVER where you’re creating this job (like you, I hate waiting, but I usually push these one time jobs about 40-70 seconds into the future rather than try and race the timer/clock to get a new job created only to miss it by a few seconds).
- Then, click OK, and OK again, to save the changes and create the one-off job schedule to test your job.
Within a minute or so, the job should spin up and execute—but the key is that it’ll be doing so in the exact same way (and with the exact same credentials or permissions that it’ll be doing so when it’s normally scheduled to run) and you’ll be able to gauge whether or not the job should be able to run normally or not—without having to wait a few hours to see.
Related: Create a Job-Scheduling Advisor
