One of my servers been giving me grief as of late because the vendor has created process into their application that dumps the full content of their table into the temp database before processing it for each page load.
I been trying to find out what kind of blocking it is causing but when I turned the profiler on the “Blocked Process Report” event under “Errors and Warnings” did not return any information. So I thought okay maybe there isn’t blocked process …
I created a blocked event on purpose but it still did not report it. I have used this before on other servers without problem so I was a bit confused. I checked the Configuration settings on server and found the Blocked Process detection was disabled. By default apparently it is disabled (new to me); as all other SQL 2005 servers I have it is enabled. I guess someone must have enabled it at later point.
How to check if you have Blocked Process Report enabled:
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'blocked process threshold' GO
If the value returned from the last statement is zero; then it is disabled. You can set it to your preferred value it is in seconds with following code:
sp_configure 'blocked process threshold', 10 GO RECONFIGURE GO
Remember to turn off the advanced options :).
How to Identify Blocking Problems with the Profiler by Brad McGehee, Link.
MSDN Online, sp_configure options, Blocked Process Threshold, Link.
MSDN Online, Blocked Process Event Details, Link.
I tried doing this, and set an alert for a 5 second threshold for Lock Wait Time, and simulated a blocking condition- I let the blocking condition run for 2 minutes, but still the alert didn't fire. Then I committed the transaction, causing the lock to release, then the alert fired. Is this the expected behavior? Shouldn't the alert fire WHILE the blocking is occurring?
Hi Kory,Hmm that is strange; you should see a report in the SQL Profiler every 5 second a new line pop up. Not just when the block is completed, I did following to produce it:Started SQL Server ProfilerConnected to server, told it to trace All Columns for Event Errors and Warnings/Blocked process report.Started SSMS and openned to connections.In Connection #1 I entered following command:BEGIN TRANSACTION A Update Table1 Set Col1 = 'A'In Connection #2 I entered following command:SELECT * FROM Table1I went back to SQL Server Profiler and started to see Blocked process report event every 5 seconds (blocked process threshold on this server is 5).If you are doing something different please let me know we can try to figure this out :).