May 15, 2009 at 9:13 am
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.
May 15, 2009 at 9:21 am
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)
May 15, 2009 at 9:37 am
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.
May 15, 2009 at 1:54 pm
RPC completed also needed in the trace, sorry, I missed somehow
May 15, 2009 at 1:58 pm
I had included that actually. thanks anyway.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply