blocking

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » blocking

blocking

Rate Topic

Display Mode

Topic Options

Author

Message

sqlfan

Posted Wednesday, March 11, 2009 8:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:35 AM
Points: 4, Visits: 316

how can I find how long the process is being blocked in SQL server?

Post #673317

Rome-828069

Posted Wednesday, March 11, 2009 9:28 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, December 01, 2009 7:50 AM
Points: 21, Visits: 88

Activity Monitor

Post #673384

sqlfan

Posted Wednesday, March 11, 2009 9:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:35 AM
Points: 4, Visits: 316

thanks...Can I get the same (time line) using any SQL query?

Post #673392

Mohit K. Gupta

Posted Wednesday, March 11, 2009 11:26 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:37 PM
Points: 941, Visits: 1,041

In SQL 2005 I recommend using SQL Server Profiler.

Create a new trace and select following event:

Errors and Warnings
-> Blocked Process Report

Make sure "Show All Columns" is selected.

This gives you very nice detailed XMl output of blocking processes. You can import this into SQL Table later and run queries against it if you wish. Thanks.

Mohit.

---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN.
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. Smooooth

How to ask for help .. Read Best Practices here.

Post #673539

Joseph-465703

Posted Thursday, March 12, 2009 9:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 06, 2012 10:04 AM
Points: 144, Visits: 455

right click on server property --> Reports --> Standard Reports -->

All Blocking Transaction

Post #674361

Dave Mason

Posted Monday, March 16, 2009 12:11 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:37 AM
Points: 335, Visits: 401

To get the info in a query, try using this dynamic mgmt view: sys.dm_exec_requests.
For instance, to get all the spid's that are currently blocked:

SELECT *
FROM sys.dm_exec_requests
WHERE wait_time > 0
AND blocking_session_id != 0

Dave Mason
Orange County, FL

Post #676819

y.koteswarrao-652921

Posted Monday, July 12, 2010 6:10 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 14, 2010 6:06 AM
Points: 76, Visits: 199

Hi

can we use the stored procedure sp_who2 by viewing the column 'blkby' to find the blocking issues?

Koteswar

Post #950694

TheSQLGuru

Posted Tuesday, July 13, 2010 12:50 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 3,575, Visits: 5,115

on sqlblog.com Adam Machanic has an INCREDIBLE replacement for sp_who2 called sp_whoisactive. Get it, learn it, use it!!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail

Post #951746

Medhatithi Guha

Posted Monday, August 15, 2011 2:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 27, 2012 1:03 PM
Points: 4, Visits: 40

In our Production server, we have created a SQL Server Agent job which runs every 1 minute and monitors if there is any blocking for more than 10 seconds. In case it finds a blocking process, it send an email to us (DBAs) and we look into the issue in more details.
Below is a sample code for this job

SELECT * (here please include the information you want)
FROM sys.dm_exec_requests DR
INNER JOIN sys.dm_exec_sessions DS ON DR.Session_id = DS.Session_id
CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS s2
WHERE DR.Session_ID in
(SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE (wait_time/10000) > @maxtime)

Also, if you want to dig more deep like the table name, index name, type of lock etc, you may use the below query to get lock info

SELECT request_session_id as spid,
db_name(resource_database_id) as dbname,
CASE
WHEN resource_type = 'OBJECT' THEN
object_name(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN 'n/a'
ELSE object_name(p.object_id)
END as entity_name, p.index_id,i.name IndexName,
resource_type as resource,
resource_description as description,
request_mode as mode, request_status as status
FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
ON p.hobt_id = t.resource_associated_entity_id
left join sys.indexes i on p.object_id = i.object_id and p.index_id = i.index_id
WHERE resource_database_id = db_id();

Post #1160240

« Prev Topic | Next Topic »

Permissions