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

How to get blocked objects? Expand / Collapse
Author
Message
Posted Tuesday, October 5, 2010 4:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:40 PM
Points: 136, Visits: 530
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
Posted Tuesday, October 5, 2010 4:56 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 6,147, Visits: 7,204
ricardo_chicas (10/5/2010)
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


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
Posted Tuesday, October 5, 2010 5:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:40 PM
Points: 136, Visits: 530
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
Posted Tuesday, October 5, 2010 5:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:04 PM
Points: 463, Visits: 1,023
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
Posted Wednesday, October 6, 2010 6:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
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
Posted Wednesday, October 6, 2010 8:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:40 PM
Points: 136, Visits: 530
Thank you guys,
Now I have what I needed :)
Post #999454
Posted Wednesday, October 6, 2010 12:13 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 12:04 PM
Points: 463, Visits: 1,023
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 »

Add to briefcase

Permissions Expand / Collapse