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

Blocked Threads Expand / Collapse
Author
Message
Posted Wednesday, June 13, 2012 8:35 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781

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
Post #1315174
Posted Wednesday, June 13, 2012 8:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:40 AM
Points: 3,683, Visits: 4,818
Can you identify what these SPIDs are actually doing?
Try identify the tasks using e.g. DBCC INPUTBUFFER(spid#


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1315190
Posted Wednesday, June 13, 2012 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1315191
Posted Wednesday, June 13, 2012 8:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 12:10 PM
Points: 65, Visits: 387
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.
Post #1315192
Posted Wednesday, June 13, 2012 8:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
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!
Post #1315206
Posted Wednesday, June 13, 2012 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1315210
Posted Wednesday, June 13, 2012 9:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:41 AM
Points: 361, Visits: 1,781
We are using spotlight.
Post #1315213
Posted Wednesday, June 13, 2012 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1315267
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse