Sql Jobs Stuck

Question

Hi All,

I have written a script to stop a Sql Server Job when it is stuck for x hours/min. Most of the live jobs gets stuck after business hours. So I am planning to create a test job and make it stuck so that I can test my script. What is the best way to make a job stuck. I am planning to create a job which inserts rows. I will create lock on table and will try to insert records. This will make a job stuck. Is there any other way I can make the job go in stuck status.

Answer 1

Hi @Sreelatha ,

I have no experience with other ways, but create lock on table seems a simple way, and it does make the jobs get stuck.
Create a simple test table and test job. Executing a statement to lock the table, such as [select * from test WITH (TABLOCKX) WAITFOR DELAY '00:02:00' ], and then run the job. This job which inserts rows will be blocked and go in stuck status.

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
https://learn.microsoft.com/en-us/answers/articles/67444/email-notifications.html

Answer 2

The easiest and resource-friendly way is to use the WAITFOR command, e.g.

WAITFOR DELAY '04:00:00'  

let the job wait for 4 hours without doing anything.

Answer 3

No need to involve tables and TABLOCKX hints. Just do a WAITFOR DELAY as suggested by Olaf!