March 16, 2011 at 11:47 am
Could someone tell me if it is possible to force a transaction created by a user to be committed by the sa user in this case. I asked this because in this cases some transaction is blocking another users in the database and if I (like the dba) kill the blocking transaction the user that begin that transaction loses data. Hope someone can help me.
Well, maybe I could not explain completely my problem. I asked how to force a transaction to be committed because the user that begins the transaction sees that it ends normally however while he stays connected to the database his transaction blocks the other users and when that transaction is killed the user loses his data. I have to say that the problem always is with a specific user but it is not all the time, I mention that in order to discard the fact was a "begin transaction" without its commit statement.
Greetings
March 16, 2011 at 11:56 am
That's nonsense. Transactions have to finish down their own path. If you are experiencing blocking issues, focus on the causes and tune the queries instead.
-- Gianluca Sartori
March 16, 2011 at 12:48 pm
Gianluca Sartori (3/16/2011)
That's nonsense.
Indeed 😀
Can you force them to be committed? ...no, that is indeed nonsense
Can you force a blocking processes to rollback to keep your server healthy until you find the root cause of the issues, sure...you can KILL the user process: http://msdn.microsoft.com/en-us/library/ms173730.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 4:03 pm
No, you really can't do that. You're going to have to get to that user and train them or get ahold of their code and clean it up. Either way, addressing the root cause is your best solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply