How to catch blocking information that appears in the future

Riley 380 Reputation points
2024-03-27T02:32:18.9766667+00:00

Is there any way to catch blocking information that appears sometime in the future? Cause we are not sure when the block comes.

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,608 questions
{count} votes

3 answers

Sort by: Most helpful
  1. CosmogHong-MSFT 22,541 Reputation points Microsoft Vendor
    2024-03-27T03:31:39.8266667+00:00

    Hi @Riley

    There are some queries that you could catch information of blocking. You could schedule the script executed periodically.

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments No comments

  2. Erland Sommarskog 100.8K Reputation points MVP
    2024-03-27T22:30:51.6733333+00:00

    Catching information about blocking that has not occurred yet? Nah, SQL Server is not that advanced.

    But what you can do is run this:

    EXEC sp_configure 'blocked process threshold (s)', N
    RECONFIGURE
    
    
    

    Where N is a number divisible by 5. Say that you set to 20. If SQL Server finds that a blocking situation has been going on for 20 seconds, SQL Server will then generate a blocked-process event. You can then track these events with Trace, Extended Events or Event Notifications. These events information includes information about the blocking situation. What commands the blocker and the blockee is running and which locks the blockee is waiting for.

    0 comments No comments

  3. Olaf Helper 40,576 Reputation points
    2024-03-28T06:04:05.8666667+00:00

    sometime in the future?

    No, really not. You could only check if currently there are locks on resources you what to access, but that would be a short snapshot for that very one millisecond; the effort is greater than the benefit to implement it.

    See https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-ver16

    0 comments No comments