December 22, 2005 at 10:28 am
Hi,
I come across some articles on SQL Server 2000 that
if we use transaction and didn't rollback or commit the
transaction due to improper coding or errors, it might
be 'locking' some tables in the database.
If that will to happen, is there any way to
rollback the old transaction ?
I know that using DBCC opentran will show any transaction not committed.
Anyone know of how to solve this issue.
Thank you
December 22, 2005 at 10:51 am
This shouldn't be the case unless connections are being left open. When a connection is closed, killed, or dies it automatically rolls back the transaction.
I did this to make sure I wasn't missing anything.
1. In Query Analyzer, I wrote a script that starts a transaction then updates a table with no rollback or commit.
2. I looked at DBCC opentran to see it.
3. I then killed the connection in that has the open transaction.
4. If you run DBCC opentran again, there are no open transactions.
If you are using an application that uses connection pooling, this is not going to always be true because the connections aren't actually closed. They are instead just returned to the pool.
What is the application you are using?
December 23, 2005 at 8:15 am
If your application calls stored procedures, you can temprorarily add "COMMIT" or "ROLLBACK" in the beginning of the called SP. Then you can changed the SP back.
December 25, 2005 at 1:30 am
Hi,
Thank for the advise.
Actually, we are having some problem with our web application using SQL2k SP3.
We also helping our client migrate their legacy data to our system using C# written
application which might crash or abort abnormally. Whenever our migration program is
running, the memory usage by SQL will go very high.
On the other hand, our SQL2k server will be very slow when the memory usage is
1.2G. Normal query taking 5-7 second will end up taking 1-2minutes.
Therefore, I am suspecting of the uncommitted transaction/rollback that
causing my query slowing.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply