Blocked Threads

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Blocked Threads

Blocked Threads

Rate Topic

Display Mode

Topic Options

Author

Message

nihal9200_kwada

Posted Wednesday, June 13, 2012 8:35 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:30 PM
Points: 352, Visits: 1,692

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

Stewart "Arturius" Campbell

Posted Wednesday, June 13, 2012 8:48 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:31 AM
Points: 3,129, Visits: 4,312

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

anthony.green

Posted Wednesday, June 13, 2012 8:48 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

UconnDBA

Posted Wednesday, June 13, 2012 8:49 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 53, Visits: 315

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

nihal9200_kwada

Posted Wednesday, June 13, 2012 8:58 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:30 PM
Points: 352, Visits: 1,692

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

anthony.green

Posted Wednesday, June 13, 2012 9:01 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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?

Post #1315210

nihal9200_kwada

Posted Wednesday, June 13, 2012 9:04 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:30 PM
Points: 352, Visits: 1,692

We are using spotlight.

Post #1315213

anthony.green

Posted Wednesday, June 13, 2012 9:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

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

Post #1315267

« Prev Topic | Next Topic »

Permissions