Sql Jobs Stuck

asked 2020-09-29T23:47:10.987+00:00
Sreelatha 41 Reputation points

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,473 questions
No comments
{count} votes

Accepted answer
  1. answered 2020-09-30T06:13:39.607+00:00
    Cris Zhan-MSFT 6,561 Reputation points

    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

    No comments

2 additional answers

Sort by: Most helpful
  1. answered 2020-09-30T06:14:21.857+00:00
    Olaf Helper 25,551 Reputation points

    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.

    No comments

  2. answered 2020-09-30T06:35:55.88+00:00
    tibor_karaszi@hotmail.com 4,291 Reputation points Microsoft MVP

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

    No comments