Avoid Concurrency problem while running SQL Server Jobs

Hello everyone,

This time my problem was to delay the start of a SQL server job if another job is already running. Obviously, the reason for this is that the jobs use the same database resources. The sysjobhistory table in MSDB database has the job ids and their status stored. First, I selected the job id which have the run_status=4 (4 means “Job in progress”) from sysjobhistory table. Then I selected the job_id and name of my suspected jobs from sysjobs table. Then, I joined these two result sets and put the joined result set inside IF NOT EXISTS command. In short, I check if any of my suspected jobs is running before I start my job. If the “IF NOT EXISTS” command returns true, I break the while loop and start my job. If it returns false, the process goes to sleep mode for 10 minutes. After 10 minutes the process wakes up and continue from the beginning of the while loop. Below is the code I have used to solve my problem:




SELECT job_id FROM msdb.dbo.sysjobhistory WHERE  run_status = 4




SELECT job_id,name FROM msdb.dbo.sysjobs WHERE name LIKE ‘%Mirror%’
OR name LIKE ‘%BR%’
OR name like ‘%Fact%’


ON A.job_id = B.job_id





—-start my job——
EXEC msdb..sp_start_job @job_name = ‘My Job’

About Zahid

10+ years of experience in BI and DWH technologies. Fields of expertise are BI Reporting (MSBI, Microstrategy, Excel, Power BI) , ETL, Data Warehouse, OLAP Cube, MDX etc.
This entry was posted in SQL, Database, SSIS (SQL Server Integration Service) and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.