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

Wierd blocking scenario Expand / Collapse
Author
Message
Posted Friday, May 15, 2009 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 3, 2010 1:48 PM
Points: 5, Visits: 50
We are facing a wierd blocking issue on one of our production SQL Server 2005 server. The blocking starts when there are more users using the system (thru app using BEA weblogic and hybernate). Looking at the threads which are blocked I see a lead blocker is blcoking around 8-10 other threads. Running DBCC inputbuffer for the lead SPID shows diferent query every time, it could be simple insert or even select. But every time we encounter blocking the DBCC Opentran shows the lead blocker SPID has open transaction and won't close until we kill that SPID. After kiing the SPID eevrything comes to normal but the app server have to restart and it is causing a lot of trouble to the users.
We have kept the deafult settings for SQL Server 2005 and are on latest service pack 3.

Any insight will be highly appreciated.

Thanks.
Post #718012
Posted Friday, May 15, 2009 9:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 6:15 AM
Points: 11, Visits: 34
A transaction is left open obviously.
I don't know the application, but a good candidate is a timeout.

-The connection was running a long query
-It was timed out
-The connectoin was reused (or the code continued) without rolling back the open transactoin.

Developers sometimes forget about the fact that a timeout does not close the transactions.

If the issue occures regurarily, set up a trace with
-Attention
-SQL Batch completed
-LOGIN-Logout events

and watch...

(If you see sp_resetconection call, it can be handled as logout/login, it rolls back every trans)
Post #718022
Posted Friday, May 15, 2009 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 3, 2010 1:48 PM
Points: 5, Visits: 50
ok. I have started the trace. I have also asked the developers to look into whether connections are properly closed after preparing and executing the statements.

Thanks.
Post #718043
Posted Friday, May 15, 2009 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 19, 2010 6:15 AM
Points: 11, Visits: 34
RPC completed also needed in the trace, sorry, I missed somehow
Post #718256
Posted Friday, May 15, 2009 1:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 3, 2010 1:48 PM
Points: 5, Visits: 50
I had included that actually. thanks anyway.
Post #718262
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse