June 25, 2014 at 3:49 am
Hi,
Configured Sql Server side trace to capture dead locks,
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 137, 15, @on
exec sp_trace_setevent @TraceID, 137, 1, @on
exec sp_trace_setevent @TraceID, 137, 13, @on
Below configurations also done.
SP_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
SP_CONFIGURE 'blocked process threshold',5;
GO
RECONFIGURE WITH OVERRIDE;
GO
but dead locks are not captured and EVENT 'blocked process report' is also not showing in trace files.
Please help how to capture 'blocked process threshold' event.
June 25, 2014 at 3:59 am
The blocked process threshold is not going to catch deadlocks. It's there to report long-term blocking where the blocked session waits longer than the threshold you've configured (5 seconds there)
For deadlocks, you may want traceflag 1222, that writes deadlock graphs to the error log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2014 at 4:09 am
hi,
Yes, i've configured for 5 seconds, and manually executed few staments like
begin tran
select.....
from another session trying to execute same select, and i wait for 1 minute..
then trying to fetch the trace files, i did'nt get and blocked process report event in trace files...
June 25, 2014 at 4:13 am
Selects don't block selects, so the queries you ran shouldn't have triggered a blocked process report, they wouldn't have blocked each other.
What are you trying to do, detect deadlocks or detect long-lasting blocking?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2014 at 5:35 am
Sorry,
am using below statements.
begin tran
Update table
from another session trying to execute same select statement from above table, and i wait for more than 5 minute..
June 25, 2014 at 5:46 am
It does sound like you're confusing deadlocks with blocks. A blocking situation occurs when one process is waiting for another process to release a lock. By default in SQL Server, this can last effectively forever. A deadlock is completely different. A deadlock is when one process needs a page owned by a second process, at the same time, the first process holds a page needed by the second process. They would have to wait on each other forever, so one of them gets chosen as a victim and its process gets rolled back.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 25, 2014 at 6:03 am
Hi,
Want to capture 'Blocked process report' event in trace file for Blocks and Desdlocks.
June 25, 2014 at 6:07 am
GilaMonster (6/25/2014)
The blocked process threshold is not going to catch deadlocks.
So, which are you trying to do? Catch long-lasting blocking scenarios (for which the blocked process report will work) or record deadlocks (for which you may want the traceflag I mentioned earlier)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2014 at 6:31 am
So to test it, try something like:
--run this in one query window
BEGIN TRAN
UPDATE TableX
SET ColA = 'SomeValue'
WHERE ColA = 'SomeOtherValue'
--ROLLBACK TRAN
--run this in a second query window
BEGIN TRAN
UPDATE TableX
SET ColA = 'YetAnotherValue'
WHERE ColA = 'SomeOtherValue'
ROLLBACK TRAN
You should see blocks between the two. If you let them run, eventually you'll see the blocked process report fire.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply