Uncommited transaction issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Uncommited transaction issue

Hi,<br /><br />I come across some articles on SQL Server 2000 that<br />if we use transaction and didn’t rollback or commit the<br />transaction due to improper coding or errors, it might<br />be ‘locking’ some tables in the database.<br /><br />If that will to happen, is there any way to<br />rollback the old transaction ?<br /><br />I know that using DBCC opentran will show any transaction not committed.<br /><br />Anyone know of how to solve this issue.<br /><br />Thank you [<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]
Did you identify that transaction?
Could you post it?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
Hi, I have not yet identify that transaction.
As mentioned, I read from some article that uncommitted/rollback
transaction can ‘block’ some table. Currently, my production DB often reach 1.2G of memory usage, then
every query will be very very slow, from normal 5-7seconds query will end up
taking 1-2minutes. Therefore, I am suspecting that uncommitted/rollback
transaction might be one of the reason.
May be there are others reasons.
Did you rebuild indexes and update statistics as part of maintenance plan?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
Hi Martin, Yes, I have auto index rebuilding automated
but not statistics. Any other good suggestion that I should be looking
at ? Thank
As far I know, you have to wait until rollback is done. If kill this rollback, there is a chance to corrupt some table/s.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
Hi, If using DBCC opentrans(‘database name’) do report some
unclosed transaction. I don’t think using ‘rollback trans’
command will work, right ? How do I rollback it and know that the transaction is
not a active one running now ? Thank
]]>