How to get blocked objects?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to get blocked objects?

How to get blocked objects?

Rate Topic

Display Mode

Topic Options

Author

Message

ricardo_chicas

Posted Tuesday, October 05, 2010 4:36 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:49 PM
Points: 94, Visits: 426

Hello all,
I was dealing with some blocked sessions and I wonder if there is a way to know exactly what object is being blocked, I know how to get what spid is blocking the others, but I think it will be really usefull to know the table...

Thanks in advance

Post #998842

Evil Kraig F

Posted Tuesday, October 05, 2010 4:56 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 5,658, Visits: 6,099

ricardo_chicas (10/5/2010)

Use sp_lock, the spid(s), and objID

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #998849

ricardo_chicas

Posted Tuesday, October 05, 2010 5:41 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:49 PM
Points: 94, Visits: 426

Thank you
but.. what happen when I get this after running sp_lock:
spid__dbid__ObjId__indId__type__resource__mode__status
545___6_____0_____0_____DB_____________S____GRANT

There isn't an objectid....

Post #998871

@SQLFRNDZ

Posted Tuesday, October 05, 2010 5:44 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 412, Visits: 857

Use the following query:

see the output query and the tables in the query.

select req.session_id, ses.login_name,req.blocking_session_id,sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_time
from sys.dm_exec_requests req left join sys.dm_exec_sessions ses on req.session_id= ses.session_id
cross apply sys.dm_exec_sql_text(sql_handle) as sqltext

--SQLFRNDZ

Post #998874

Grant Fritchey

Posted Wednesday, October 06, 2010 6:01 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 4:50 AM
Points: 13,371, Visits: 25,143

Although, using sys.dm_exec_requests won't show blocking if parallelism is involved.

Another way to do it, and you can use almost the same query, is to look at sys.dm_os_waiting_tasks.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #999183

ricardo_chicas

Posted Wednesday, October 06, 2010 8:59 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:49 PM
Points: 94, Visits: 426

Thank you guys,
Now I have what I needed :)

Post #999454

@SQLFRNDZ

Posted Wednesday, October 06, 2010 12:13 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 412, Visits: 857

select session_id,wait_duration_ms,
wait_type,blocking_session_id
from sys.dm_os_waiting_tasks where session_id >50 and blocking_session_id is not null

Run this and the before query I posted for daily thats it you are done.

--SQLFRNDZ

Post #999749

« Prev Topic | Next Topic »

Permissions