InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:20:40 GMT20http://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspx[quote][b]GilaMonster (10/22/2012)[/b][hr]Please don't hijack other people's threads. Start your own thread with this problem.[/quote]I am sorry. I will start a new thread on this. --- Babu Tue, 23 Oct 2012 02:30:41 GMTbaabhuhttp://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspxPlease don't hijack other people's threads. Start your own thread with this problem.Mon, 22 Oct 2012 08:21:02 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspx[quote][b]GilaMonster (10/18/2012)[/b][hr]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 tbl1No 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 doneIf instead you had thisbegin transactionInsert into tb1 ...Update tbl1 ...Delete from tbl1commitnow, if SQL crashes half way through the update, the insert and update are rolled back on restart as the commit was never reached.[/quote]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. Mon, 22 Oct 2012 07:23:02 GMTbaabhuhttp://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspxThanks Gail, Clear Explanation!!Mon, 22 Oct 2012 06:42:36 GMTwhizkidgpshttp://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspxNothing 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 tbl1No 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 doneIf instead you had thisbegin transactionInsert into tb1 ...Update tbl1 ...Delete from tbl1commitnow, if SQL crashes half way through the update, the insert and update are rolled back on restart as the commit was never reached.Thu, 18 Oct 2012 02:09:12 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspxRead about CHECKPOINTThu, 18 Oct 2012 00:13:46 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1374125-391-1.aspxI've recently gone through the article about log file management in SQL Server. I was told that in the article, if the system got crashes then the transaction which are committed in LOG file, those will be inserted into DATA file after system restarts. In the same way, the transaction which are uncommitted in the LOG file, those will be gone out.In SQL statement, We explicitly given a statement like "BEGIN TRAN", "COMMIT TRAN" and "ROLLBACK TRAN", the transaction will be performed based on each statement. But, we use these SQL statements wherever we needed, and we won't give these statements in all such cases when performing DML statements.My Doubt is, who will be initiating the Transaction Status (Commit\Rollback) in LOG file, if we does not define Transaction statements(Begin,Commit, Rollback) explicitly?Wed, 17 Oct 2012 21:39:42 GMTwhizkidgps