SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Wierd blocking scenario


Wierd blocking scenario

Author
Message
Sher-1032270
Sher-1032270
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
Attila-378403
Attila-378403
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 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)
Sher-1032270
Sher-1032270
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 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.
Attila-378403
Attila-378403
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 34
RPC completed also needed in the trace, sorry, I missed somehow
Sher-1032270
Sher-1032270
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 50
I had included that actually. thanks anyway.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search