Sql Jobs Stuck

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

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.
12,631 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
0 comments No comments
{count} votes

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

    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

    0 comments No comments

2 additional answers

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

    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.

    1 person found this answer helpful.
    0 comments No comments

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

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

    0 comments No comments