question

Doria avatar image
0 Votes"
Doria asked Doria commented

Blocked process report.

Hi everyone!

I am trying to use the SQL Server blocked process reporting feature but it is not working. Below the commands I executed and the article I followed.

18052-untitled1.png

17949-untitled2.png

18015-untitled3.png

17980-untitled4.png

https://www.red-gate.com/simple-talk/sql/sql-tools/how-to-identify-blocking-problems-with-sql-profiler/


What am I missing?

Thanks.


sql-server-general
untitled1.png (72.9 KiB)
untitled2.png (78.2 KiB)
untitled3.png (67.3 KiB)
untitled4.png (60.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would change the sp_configure value to 10. It is the deadlock detector that also checks for blocked processes and it wakes up every 5 sec (slightly simplified). Perhaps it is confused by the values 1?

Also,what events do you capture in the trace?

Finally, I dont remember if this configure requires engine restart, so please check that.

0 Votes 0 ·
SreekanthMadambath-0012 avatar image
0 Votes"
SreekanthMadambath-0012 answered

Blocked threshold will not report if you set for 1 Sec.
The minimum is 5 sec.

Set above 5 sec and validate the same.

https://blog.sqlauthority.com/2014/06/30/sql-server-find-blocking-using-blocked-process-threshold/

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Doria avatar image
0 Votes"
Doria answered TiborKaraszi commented

Unfortunately the same result. It simply doesn't register anything. Could it be something else?

18048-untitled.png


Regards.



untitled.png (81.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I asked you earlier: What events do you capture in that trace?

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Doria commented

Hi Doria-6500,

Try below T-SQL again. The setting takes effect immediately without a server stop and restart.

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 20 ;
GO
RECONFIGURE ;
GO

Please refer to MS document blocked process threshold Server Configuration Option to get more detail information.

This is a blog talking about this, hope it could help you.

Scripts to use the Blocked Process Report

If the response helped, do "Accept Answer" and up vote it.

Best regards.
Cathy


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks!

0 Votes 0 ·

Did you resolve your issue? Did the replies could help you?

0 Votes 0 ·