GilaMonster (10/18/2012)
Nothing to do with checkpoint.If you don't defined explicit transactions, then each and every data modification statement is wrapped in its own transaction which is automatically committed upon completion.
So let's say you have this:
Insert into tb1 ...
Update tbl1 ...
Delete from tbl1
No explicit transaction, so that's run as 3 transactions. If SQL crashed half way through the update, the insert would be considered committed and the update not committed, so upon restart the inserted rows would be in the table and none of the update would have been done
If instead you had this
begin transaction
Insert into tb1 ...
Update tbl1 ...
Delete from tbl1
commit
now, if SQL crashes half way through the update, the insert and update are rolled back on restart as the commit was never reached.
Hi Gail,
We are having some data inconsistency in our production system.
We delete in batches in our stored procedure. One batch we delete 25 k records with 1000 as batches. .
Sometimes we encounter the following error.
Error: 18056 Severity: 20 State: 46.
The client was unable to reuse a session with SPID 1971 which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Will SP execution stop in the middle of the execution.
The SP does not have transaction control ( I am not the owner for the program. Difficult to include the transaction control on my own.)
Please advise if this is stupid work.
Thank you.