This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(246.4, 21%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Is there any way to catch blocking information that appears sometime in the future? Cause we are not sure when the block comes.
Additional SQL Server features and topics not covered by specific categories
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
You can use the "broker" to report on blocking events. You have to decide how long a blocking lock must last before you do something (like send an email). Too short a time and you get inundated, too long and it'll never report.
google for more info "blocked process report" and "([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])"
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.
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.
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".