Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log file Management Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 9:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 22, 2012 6:39 AM
Points: 13, Visits: 29
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?

Post #1374125
Posted Thursday, October 18, 2012 12:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Read about CHECKPOINT

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1374157
Posted Thursday, October 18, 2012 2:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1374189
Posted Monday, October 22, 2012 6:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 22, 2012 6:39 AM
Points: 13, Visits: 29
Thanks Gail, Clear Explanation!!
Post #1375393
Posted Monday, October 22, 2012 7:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:38 PM
Points: 1,287, Visits: 1,117
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.
Post #1375418
Posted Monday, October 22, 2012 8:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
Please don't hijack other people's threads. Start your own thread with this problem.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1375448
Posted Tuesday, October 23, 2012 2:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:38 PM
Points: 1,287, Visits: 1,117
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
Post #1375901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse