SQL Server Performance Forum – Threads Archive
Implicit Transactions – Deleted Rows
I have a problem where records a being deleted via implicit transactions (Ican see this from the SQL log). I am not sure I understand implicit
transactions fully but no where in our database or application do we ever
delete these records. Why are they being deleted via implicit transactions?
Read from Books Online.. The Implicit transaction remains in effect until you issue a COMMIT or ROLLBACK statement. After the first transaction is committed or rolled back, SQL Server automatically starts a new transaction the next time any of these statements are executed by the connection. SQL Server keeps generating a chain of implicit transactions until implicit transaction mode is turned off. Implicit transaction mode is set either using the Transact-SQL SET statement, or through database API functions and methods.
You can use below command to turn on or off this mode: – SET IMPLICIT_TRANSACTIONS { ON | OFF } Deepak Kumar –An eye for an eye and everyone shall be blind
]]>