Blocked Threads

  • Blocked Threads on SERVER-QA

    Locks - Blocked Processes: Session 85 has been blocked by session 63 for the last 7729 seconds.

    Please advise how to truobleshoot and fix. thanks

  • First find out what SPID63 is doing, is it inside a transaction which hasnt been committed

    select * from sys.dm_exec_requests

    cross apply sys.dm_exec_sql_text(sql_handle)

    cross apply sys.dm_exec_query_plan(plan_handle)

    where session_id = 63

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ chapter 6

  • This was removed by the editor as SPAM

  • Using Activity Monitor can look at details for each session to see what SQL they are executing. Also paying attention to HostName can give you an idea where the SQL is being executed from.

  • I ran sp_who2 and also checked in activity monitor, there are no blkby sessions.

    I ran below query. No output.

    select * from sys.dm_exec_requests

    cross apply sys.dm_exec_sql_text(sql_handle)

    cross apply sys.dm_exec_query_plan(plan_handle)

    where session_id = 63

    Looks like no blocked process now. Is sql server resolves this automatically. How to find what caused the problem. Thnaks!

  • Without something which logs the two sessions statements you cant.

    How did it alert you that the sessions where blocking? Do you have some monitoring software installed?

  • We are using spotlight.

  • forgive my ignorance but do you know how to use the drill through to actually see what causes the alerts? if so should of got the tsql in question which caused the error.

    if I remember correctly spotlight doesn't store the session information for the alert unless you modify it's alert storage settings from default so trying to get the information now might be tricky

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

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