Post reply

How to get blocked objects?

  • October 5, 2010 at 4:36 pm

    #148920

    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

  • October 5, 2010 at 4:56 pm

    #1232147

    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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • October 5, 2010 at 5:41 pm

    #1232164

    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....

  • October 5, 2010 at 5:44 pm

    #1232167

    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

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • October 6, 2010 at 6:01 am

    #1232418

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • October 6, 2010 at 8:59 am

    #1232669

    Thank you guys,

    Now I have what I needed 🙂

  • October 6, 2010 at 12:13 pm

    #1232924

    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.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply