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 3 of 3

«««1 2 3

Using the Blocked Process Report in SQL Server 2005/2008

Rate Topic

Display Mode

Topic Options

Author

Message

doc_sewell

Posted Thursday, October 14, 2010 10:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 04, 2012 8:18 AM
Points: 155, Visits: 123

Jon,
How about if I just want a normal blocked process report trace (without the XML). Would you have a template (or column settings and filter) you recommend?

Post #1004545

doc_sewell

Posted Thursday, October 14, 2010 10:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 04, 2012 8:18 AM
Points: 155, Visits: 123

Jonathan,
What about turning the blocked process threshold off when you are done?

Post #1004587

manoj.ks

Posted Wednesday, August 10, 2011 6:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:40 AM
Points: 9, Visits: 167

how to read the data from blocked process threhold report? I have enabled blocked process threshold in one of my server. Can someone tell me how to do it?

Post #1157558

pkrudysz

Posted Wednesday, January 04, 2012 8:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:10 AM
Points: 48, Visits: 257

manoj.ks (8/10/2011)

how to read the data from blocked process threhold report? I have enabled blocked process threshold in one of my server. Can someone tell me how to do it?

Use SQL Server Profiler and select the 'Block Process Report' event. Once blocking occurs, the Profiler will show it.

Post #1229973

pkrudysz

Posted Wednesday, January 04, 2012 8:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:10 AM
Points: 48, Visits: 257

Jonathan,

I've received the below for the blocked process:
<inputbuf>
Proc [Database Id = 14 Object Id = 1311948665] </inputbuf>
</process>

This is a stored procedure that doesn't touch the table that was being blocked. Could this indicate that: the blocked process report only shows the last executed query of a multi-query batch or transaction?

How else can a table be blocked by a SP that has nothing to do with it?

Post #1229981

Jonathan Kehayias

Posted Wednesday, January 04, 2012 8:22 AM

SSCommitted

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

Post #1229983

pkrudysz

Posted Thursday, January 05, 2012 12:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:10 AM
Points: 48, Visits: 257

Jonathan Kehayias (1/4/2012)

Yes, the root of the blocking could be from a previous statement in a transaction.

Thanks. I again lost hope to capture the query that caused blocking in SQL Server. However, with the Blocked Process Report, I can at least identify the transaction responsible.

Post #1230516

« Prev Topic | Next Topic »

27 posts, Page 3 of 3

«««1 2 3

Permissions