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:

WHILE(1=1)
BEGIN
IF NOT EXISTS
(

SELECT A.job_id FROM

(

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

)A

INNER JOIN

(

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

)B

ON A.job_id = B.job_id

)

BEGIN
BREAK;
END

WAITFOR DELAY ’10:00′

END

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

Advertisements

About Zahid

Team Lead at IMS Health (www.imshealth.com). A part-time consultant and trainer. Fields of expertise are ETL, BI Reporting (Microstrategy, Excel, ASP.NET Dashboards etc.) 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s