October 29, 2002 at 2:00 pm
CHECKPOINT
Forces all dirty pages for the current database to be written to disk. Dirty pages are data or log pages modified after entered into the buffer cache, but the modifications have not yet been written to disk.
Why would a log page on Disk be "entered into the Buffer cache"?
Is this what it is saying here?
October 29, 2002 at 2:32 pm
You make a change. This is written to memory (buffer). On checkpoint, if it has not been written to disk, it now is. The normal, write log, write disk 2nd rules still apply.
Steve Jones
October 29, 2002 at 2:48 pm
Before the Checkpoint, the database is not syncronzed, the changes are saved in the Transaction log but not in the data files.
After a checkpoint, all the transactions that are in the log but not in the database, are saved to the datafiles on disk. At this point the database is syncronized.
October 29, 2002 at 3:19 pm
So if a data page is pulled into cache and then changes are made to that data page while in cache, that page is considered "dirty". With that same change, is a log record created in cache prior to that data page change in cache?
I am wondering about the relationship between data pages and log pages while in cache.
Also, what happens if a different change is made to that same data page again while in cache?
October 29, 2002 at 4:44 pm
A thread of SQL saves the transactions in buffer cache to the transaction Log(log file on disk). this is automatic, an SQL decide when to execute the thread depending on the free memory in the buffer and time pending on the buffer.
All the transacions that are in the buffer are saved to disk in the transaction log(log file). At this time, what you have on disk (changes on lof file) is not the same that what you have in the database (data files) because the changes has not been applied.
With the checkpoint, all the transactions that are in the tran log, are saved to disk in the data files.
October 29, 2002 at 6:50 pm
so any data page change never goes from the data buffer cache to disk???
So the changes get written to the log cache, then to the log file on disk, then to the data file on disk from the log file on disk, based on checkpoint.
October 29, 2002 at 9:26 pm
no.
change is made by user.
change recorded in log file (in memory).
change recorded in data file in memory
change recorded to log file on disk. May be checkpoint, may occur if system is not too busy.
change recorded to data file on disk from disk change in memory. The log and data items are separate
Steve Jones
October 29, 2002 at 10:47 pm
sounds right to me.
Thanks.
October 29, 2002 at 11:27 pm
Wait a sec.....
BOL:
"Because SQL Server must wait for the disk subsystem to complete I/O to SQL Server log files as each transaction is completed, it is important that the disks containing SQL Server log files have sufficient disk I/O handling capacity for the anticipated transaction load."
- Thus as each and every BEGIN..COMMIT or auto-commit transaction is ran, it must go to disk right away and write before it is considered completed.
- So for every transaction SQL Server makes a disk I/O to log file.
October 30, 2002 at 12:12 pm
comments Steve?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply