SQL Server Performance Forum – Threads Archive

second job is getting abend when first is running

Hi Everyone, I am having one issue related to SQL jobs. I have one job i.e. A which i am creating through scripts and running it. Job A may take time to finish for example: can take 15 min to completes and functionallity Job A is calling stored procedure P_Calculate with input parameter as int=5 to calculate data. Now when Job A is running and performing calculation, at the same time another user executed Job ‘B’ which also call stored procedure p_Calculate with input parameter as int=8. My issue is that my sql agent is not waiting for job A to finish and start Job B. it is directly abending Job B.I want some setting through which i can make Job B to wait till Job a finishes.
Please provide me your inputs. Thanks and Regards
Ravi K

Locking job related table/row?

Hi Mmarovic, Yes it is locking table/row. Because my stored procedure P_Calculate has transaction handling. Just now i have retested above scenario and i can see from sysjobhistory table that my second job is getting abend because deadlock is happening. i am getting below error. Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205)
Thanks and Regards
Ravi K

You can call the second job after the transaction using SP_START_JOB statement, this way you need not to schedule the second job that is contributing in deadlock. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If the problem is that someone can execut procedure mentioned any time and you want all execution to be done serialy one after another, you can start procedure by locking controll table that you would create for that purpose or row in such table that controls execution order for that specific procedure.

Hi Everybody Thanks for your reply. Can i set SET LOCK_TIMEOUT 18000 in each sp. So whenever first time any SP execute and if row/table is locked , sp will wait for 18000 milliseconds. Please provide you inputs on above solution Thanks and Regards Ravi K

If there isn’t any deadlock situation then you may continue this for a while, but do watch about timeouts from other application processess. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>