December 19, 2010 at 11:13 am
In sql server (any version) we have the checkpoint process which writes the dirty pages to the disk periodically , right?
My question is what kind of dirty data does it directly commit to the disk? What i mean to say isin't the data 1st to be written to the transaction log since sql server provides for a write ahead log which prevents data loss by captuing the data in the t-log, then what kind of data does the checkpoint process write directly to the disk withouth 1st going through the transaction log.
Here is a excerpt from sql 2005 unleashed regarding what checkpoint does.
Writes all modified log pages to the transaction log on disk
Writes all dirty data pages to disk. (Data pages that have not been modified are not
written back to disk to save I/O.)
Now looking at the second line what exactly is the kind of data is being written to the disk withouth 1st going to the t-log.
thanks.
December 19, 2010 at 11:36 am
How about me just routing you to Paul Randals series of sqlserver myths:
This one on checkpoints:http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281530%29-checkpoint-only-writes-pages-from-committed-transactions.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 19, 2010 at 11:54 am
In sql server (any version) we have the checkpoint process which writes the dirty pages to the disk periodically , right?
My question is what kind of dirty data does it directly commit to the disk? What i mean to say isin't the data 1st to be written to the transaction log since sql server provides for a write ahead log which prevents data loss by captuing the data in the t-log, then what kind of data does the checkpoint process write directly to the disk withouth 1st going through the transaction log.
Here is a excerpt from sql 2005 unleashed regarding what checkpoint does.
Writes all modified log pages to the transaction log on disk
Writes all dirty data pages to disk. (Data pages that have not been modified are not
written back to disk to save I/O.)
Now looking at the second line what exactly is the kind of data is being written to the disk withouth 1st going to the t-log.
thanks.
The checkpoint process writes all dirty pages in the buffer pool to disk ie the data file. It is possible for there to be pages in the buffer pool that have not been modified. the output of SELECT statements etc. So these do not overwrite the values on disk as they haven't changed.
Even in simple mode all changes are logged, with simple mode the VLFs in the log are marked as inactive after a checkpoint occurs.
The purpose of a checkpoint is to keep the recovery time of a database to a minimum.
Hope that helps.
Gethyn Elliswww.gethynellis.com
December 19, 2010 at 10:39 pm
Low Rider (12/19/2010)
Now looking at the second line what exactly is the kind of data is being written to the disk withouth 1st going to the t-log.
None. No dirty data page is written to disk without its log record having being written first. The log records can be written some time before the pages, doesn't have to be in the same checkpoint operation.
Before checkpoint writes the pages down, the associated log records must either already be on disk (due to commit of a transaction) or must be written by the checkpoint.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply