Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Commit, truncate of log file and checkpoint in SQL Server


Commit, truncate of log file and checkpoint in SQL Server

Author
Message
beejug1983
beejug1983
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1482
.
Vegard Hagen
Vegard Hagen
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 363
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)


Vegard Hagen
Vegard Hagen
Mr or Mrs. 500
Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)Mr or Mrs. 500 (519 reputation)

Group: General Forum Members
Points: 519 Visits: 363
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)


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47191 Visits: 44358
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, MVP, M.Sc (Comp Sci)
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


beejug1983
beejug1983
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1482
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47191 Visits: 44358
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, MVP, M.Sc (Comp Sci)
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


beejug1983
beejug1983
SSC Veteran
SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)SSC Veteran (292 reputation)

Group: General Forum Members
Points: 292 Visits: 1482
Thanks for better understanding Gail..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search