Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Using the Blocked Process Report in SQL Server 2005/2008 Expand / Collapse
Author
Message
Posted Tuesday, March 30, 2010 6:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:07 AM
Points: 938, Visits: 1,142
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
Posted Tuesday, March 30, 2010 10:01 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 PM
Points: 1,683, Visits: 1,797
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




Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #892984
Posted Wednesday, March 31, 2010 4:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:48 PM
Points: 45, Visits: 351
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
Posted Friday, April 9, 2010 1:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:48 PM
Points: 45, Visits: 351
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
Posted Friday, April 9, 2010 2:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:34 PM
Points: 70, Visits: 815
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
Posted Friday, April 9, 2010 3:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:48 PM
Points: 45, Visits: 351
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
Posted Friday, April 9, 2010 3:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:34 PM
Points: 70, Visits: 815
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
Posted Friday, April 9, 2010 3:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:48 PM
Points: 45, Visits: 351
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
Posted Saturday, April 10, 2010 12:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:48 PM
Points: 45, Visits: 351
Any ideas?
Post #901121
Posted Monday, April 12, 2010 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:48 PM
Points: 45, Visits: 351
Anyone?
Post #901610
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse