October 17, 2012 at 9:39 pm
I'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?
October 18, 2012 at 12:13 am
Read about CHECKPOINT
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 18, 2012 at 2:09 am
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 22, 2012 at 6:42 am
Thanks Gail, Clear Explanation!!
October 22, 2012 at 7:23 am
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.
October 22, 2012 at 8:21 am
Please don't hijack other people's threads. Start your own thread with this problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 23, 2012 at 2:30 am
GilaMonster (10/22/2012)
Please don't hijack other people's threads. Start your own thread with this problem.
I am sorry. I will start a new thread on this.
--- Babu
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply