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

Commit, truncate of log file and checkpoint in SQL Server Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2012 6:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:02 PM
Points: 274, Visits: 1,464
.
Post #1373199
Posted Tuesday, October 16, 2012 7:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
First of all, a transaction has to be committed in order for any updates it has made to become "official" in the database. In other words, you don't actually change your data until you commit the transaction. Second, the checkpoint writes all dirty pages from memory to data file on disk. A page is considered "dirty" whenever anything on it has changed in memory and those changes have not yet been written to disk. So, even after a commit, you're not really done working with the transaction until you've also written any and all dirty pages back to the data disk. Plus, if you have replication, that also has to be done before you've "finished" your transaction. Truncating the log file - in this context interpreted as the act of clearing the log records of your transaction - can not be done until you're really finished, so it has to wait for all of these actions to complete. If your recovery model is set to simple, a checkpoint can also truncate the log file, under recovery model full or bulk-logged, only a transaction log backup can do this.

So, in order for your log records to be cleared, you need to have commited the transaction, written pages to data disk, performed replication if applicable and backed up the log if you're running anything other than simple recovery.




Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1373228
Posted Tuesday, October 16, 2012 9:28 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:50 AM
Points: 517, Visits: 346
For the record, I might add that a truncate log does not actually clear individual records or transactions from the log. It simply clears all VLFs (Virtual Log Files) that are currently marked as inactive. So, you need all the transactions that have records on the same VLF to be completely finished before the VLF can be cleared.



Vegard Hagen
Norwegian DBA, blogger and generally a nice guy who believes the world is big enough for all of us.

@vegard_hagen on Twitter
Blog: Vegard's corner (No actual SQL stuff here - haven't found my niche yet. Maybe some day...)

"It is better to light a candle than to curse the darkness."
(Chinese proverb)

Post #1373357
Posted Tuesday, October 16, 2012 9:57 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 @ 2:44 PM
Points: 42,443, Visits: 35,498
Vegard Hagen (10/16/2012)
For the record, I might add that a truncate log does not actually clear individual records or transactions from the log. It simply clears all VLFs (Virtual Log Files) that are currently marked as inactive.


Truncating the log marks active VLFs that contain no log records needed by anything in the DB as inactive. There's no clearing that occurs, it's simply the marking of VLFs as inactive and hence reusable.



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 #1373374
Posted Thursday, October 18, 2012 4:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:02 PM
Points: 274, Visits: 1,464
Thanks Gail and Vegard. As per my understanding as of now, committed transactions reside in transaction log file and they are marked to be saved permanently in datafile / disk and they get saved in datafile / disk when checkpoint occur. These committed transactions are guaranteed to be saved in datafile / disk permanently. Please correct me if I am missing somthing.

Thanks in advance.
Post #1374266
Posted Thursday, October 18, 2012 5:00 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 @ 2:44 PM
Points: 42,443, Visits: 35,498
Err... close but I sense a misunderstanding there.

When changes are made, they are made to the data pages in memory and are logged. When the transaction commits, the log records are guaranteed hardened on disk. At a later point the checkpoint (or lazy writer) will write the modified data pages to disk. The checkpoint does not read the log and make the changes to the data file, it writes the already changed data pages from memory to disk.



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 #1374271
Posted Thursday, October 18, 2012 1:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:02 PM
Points: 274, Visits: 1,464
Thanks for better understanding Gail..
Post #1374569
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse