June 13, 2012 at 8:35 am
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
June 13, 2012 at 8:48 am
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
June 13, 2012 at 8:48 am
This was removed by the editor as SPAM
June 13, 2012 at 8:49 am
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.
June 13, 2012 at 8:58 am
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!
June 13, 2012 at 9:01 am
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?
June 13, 2012 at 9:04 am
We are using spotlight.
June 13, 2012 at 9:59 am
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