September 22, 2017 at 2:44 am
I am confused I have these open transactions sleeping so I assumed the problem was with the application trying to access the db not closing the connection.
But when I pointed the application at another DB server with the restored DB the problem goes away.
I am very confused any ideas?
September 22, 2017 at 8:11 am
Edward-445599 - Friday, September 22, 2017 2:44 AMI am confused I have these open transactions sleeping so I assumed the problem was with the application trying to access the db not closing the connection.But when I pointed the application at another DB server with the restored DB the problem goes away.
I am very confused any ideas?
Not sure which part is confusing but an open transaction would rollback when the client disconnects.
Sue
September 22, 2017 at 8:46 am
Sue_H - Friday, September 22, 2017 8:11 AMNot sure which part is confusing but an open transaction would rollback when the client disconnects.Sue
Thanks sue I know this, what am confused about is that this should be an application problem. But I backed up and restored to another db server ran the same app against it and it works fine. How can that be?
September 22, 2017 at 10:09 am
Edward-445599 - Friday, September 22, 2017 8:46 AMThanks sue I know this, what am confused about is that this should be an application problem. But I backed up and restored to another db server ran the same app against it and it works fine. How can that be?
It doesn't matter from the database side. It IS an application issue. The application connections were reset when you pointed it to a different database. Whatever happened when the connections first got orphaned likely has not happened. Could be a connection pooling thing or how the application cleans up connections or some other issue with the application server. The database isn't going to be able to tell you. About as close as you can get is if/when it happens again, you should be able to get the last commands for those sessions and you can troubleshoot from there - such as seeing if all of those were executing the same commands, see if they all last executed things at the same time, etc. And then you can check the application, the application server and the logs there, etc.
Sue
September 23, 2017 at 5:03 pm
Sound like transactions are being opened without being closed. The easy & immediate solution would be to go through and kill all SPIDs that have open transactions, that are suspended that are more than N seconds old.
Of course you want to get to the root of the problem... Based on your description, it sounds like either the application or a stored proc is allowing a user action to open a transaction and relying on a second user action to commit the transaction. That of course is a recipe for disaster. When you test the application yourself, odds are you're testing it like a developer, and using the application as it was designed to be used... Users, on the other hand, seem to be constantly finding new and innovative ways to use the application in the most horrific manner possible.
The mane thing is to pin down the action(s) that are opening the transactions and what is preventing the commit....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply