How to Ensure Only One Instance of a Job is Running in SQL Server

jobssql serversql server 2014sql-server-agent

SQL Server 2014 Standard Edition.

We have a job that runs from 20-40 min. We only want one instance of this job running, but we don't want huge gaps between runs.

So, scheduling it every 60 min is not ideal, because there could be 30-40 dead min. Ideally, we would schedule it every 30 min, because that would give us the ideal of either running every 10 min, or running back to back.

On a Windows OS scheduled task, there are settings to control how to handle job instantiation when the job is still running.

How do I get some control on SQL Server?
How do I get to my ideal (or close to it)?

Best Answer

The way SQL Server Agent works is exactly how you want already - if you schedule the job for every 10 minutes, and at 11:00 it runs for 12 minutes, it won't run again until 11:20 (the 11:10 instance won't start, because an instance of the job was already running).