Using the Blocked Process Report in SQL Server 2005/2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Using the Blocked Process Report in SQL...

27 posts, Page 2 of 3

««1 2 3 »»

Using the Blocked Process Report in SQL Server 2005/2008

Rate Topic

Display Mode

Topic Options

Author

Message

Wilfred van Dijk

Posted Tuesday, March 30, 2010 6:46 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, March 06, 2013 12:56 AM
Points: 898, Visits: 1,045

Which commands and in which order do I have to execute to stop/remove this trace?

Wilfred
The best things in life are the simple things

Post #892694

Jonathan Kehayias

Posted Tuesday, March 30, 2010 10:01 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

You would use sp_trace_setstatus to remove the trace. You'd have to get the traceid from the sys.traces DMV and then you can remove the trace:


-- Find the TraceID for the session in sys.traces
select * from sys.traces

-- Stop and Remove TraceID 2 from the server
exec sp_trace_setstatus 2, 2

Post #892984

rafa.aborges

Posted Wednesday, March 31, 2010 4:07 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:48 AM
Points: 40, Visits: 211

I'm monitoring the whole SQL Server environment with the blocked process report.

I'm running this process in background as a job to get the informations.

The problem is that it generated a lot of lines of blocked informations and I'm having issues to parse the information.

Is there any script that I could get only the sqlhandle and the line of the object of the blocked and blocking process? That's all I need.

Anyone can help me?

Thanks

Post #894271

rafa.aborges

Posted Friday, April 09, 2010 1:44 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:48 AM
Points: 40, Visits: 211

How reliable is the information generated in the blocked process report?

I'm asking that because we let the report on for several days and when we parsed the information, we had some unusual locks, like a table being blocked by another that is not related to the blocking table.

The only reliable information we had was the blocked process.

Does anyone know something related to this?

Thanks in advance

Post #900806

don.schaeffer

Posted Friday, April 09, 2010 2:05 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 7:05 AM
Points: 60, Visits: 633

This script might get what you want:

select
WaitTime = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@waittime','varchar(20)' ),
BlockingQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocking-process/process/inputbuf)[1]','varchar(200)'),
BlockingSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@spid','varchar(20)' ),
BlockingLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/@lastbatchstarted','varchar(20)' ),
BlockingSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockingStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockingStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocking-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' ),
BlockedQuery = cast( textdata as xml ).value( '(/blocked-process-report/blocked-process/process/inputbuf)[1]','varchar(200)'),
BlockedSpid = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@spid','varchar(20)' ),
BlockedLastBatchStarted = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/@lastbatchstarted','varchar(20)' ),
BlockedSQLHandle = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@sqlhandle','varchar(50)' ),
BlockedStmtstart = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtstart','varchar(50)' ),
BlockedStmtend = cast( textdata as xml ).value( '/blocked-process-report[1]/blocked-process[1]/process[1]/executionStack[1]/frame[1]/@stmtend','varchar(50)' )
from dbo.Feb02 t
where eventclass = 137
order by BlockedLastBatchStarted

Post #900816

rafa.aborges

Posted Friday, April 09, 2010 3:40 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:48 AM
Points: 40, Visits: 211

Thanks for the reply!

I already ran that script, but, as I said, the information returned is not reliable.

Example: in my blocked process report, it shows that the table A is executing an update and it's blocked and in the blocking handle show that a select on table B is being executed. It doesn't make any sense to me, since table A is not related at all to table B.

Is there another way to check exactly what is blocking?

Post #900868

don.schaeffer

Posted Friday, April 09, 2010 3:45 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 09, 2013 7:05 AM
Points: 60, Visits: 633

If there are transactions involved locks may be retained on tables from previous statements. Even though the second spid might be selecting from table b it may still have a lock of some sort on table a if this is wrapped within a transaction.

Post #900872

rafa.aborges

Posted Friday, April 09, 2010 3:49 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 10:48 AM
Points: 40, Visits: 211

That's what I'm thinking. Since it's inside a stored procedure, it might being blocked by a previous transaction. The problem is, how can I get the exact statement that is causing the lock?

Post #900873