Assuming a database using full recovery model, when a record is written in SQL Server (by
UPDATE etc) write ahead logging will ensure the change is written to the log file before modifying the data page.
Both the log and data page entries are made in RAM and committed to disk later by a Checkpoint.
If there is a system crash (power loss for the sake of argument) what will happen to dirty pages (IE data that is changed in RAM but not committed to disk) as the contents of RAM do not survive system restarts, is this data lost?
After some testing, I can see the dirty pages are not lost, but I am not sure why:
using this tutorial
create a test database
CREATE DATABASE DirtyPagesDB GO USE DirtyPagesDB GO
turn off automatic checkpoints
DBCC TRACEON(3505, -1); DBCC TRACESTATUS();
create a table, insert some data and issue a checkpoint:
CREATE TABLE t1 (Speaker_Bio CHAR(8000)) GO INSERT INTO t1 VALUES ('SQL'),('Authority') GO CHECKPOINT
confirm no dirty pages
-- Get the rows of dirtied pages SELECT database_name = d.name, OBJECT_NAME = CASE au.TYPE WHEN 1 THEN o1.name WHEN 2 THEN o2.name WHEN 3 THEN o1.name END, OBJECT_ID = CASE au.TYPE WHEN 1 THEN p1.OBJECT_ID WHEN 2 THEN p2.OBJECT_ID WHEN 3 THEN p1.OBJECT_ID END, index_id = CASE au.TYPE WHEN 1 THEN p1.index_id WHEN 2 THEN p2.index_id WHEN 3 THEN p1.index_id END, bd.FILE_ID, bd.page_id, bd.page_type, bd.page_level FROM sys.dm_os_buffer_descriptors bd INNER JOIN sys.databases d ON bd.database_id = d.database_id INNER JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id LEFT JOIN sys.partitions p1 ON au.container_id = p1.hobt_id LEFT JOIN sys.partitions p2 ON au.container_id = p2.partition_id LEFT JOIN sys.objects o1 ON p1.OBJECT_ID = o1.OBJECT_ID LEFT JOIN sys.objects o2 ON p2.OBJECT_ID = o2.OBJECT_ID WHERE is_modified = 1 AND d.name = 'DirtyPagesDB' AND ( o1.name = 't1' OR o2.name = 't1' ); GO
confirm time of last checkpoint
SELECT f1.[Checkpoint Begin], f2.[Checkpoint End] FROM fn_dblog(NULL, NULL) f1 JOIN fn_dblog(NULL, NULL) f2 On f1.[Current LSN] = f2.[Previous LSN] WHERE f2.Operation IN (N'LOP_BEGIN_CKPT', N'LOP_END_CKPT');
Add more rows
INSERT INTO t1 VALUES ('SQL'),('Authority')
Use query above to confirm that there were dirty pages
Kill the SQL Server task from task manager to simulate a power off.
Start the service
Re-run the command above to get the last checkpoint time, it was the same (IE no checkpoints have run other than the one we did manually)
SELECTed from the table t1 and all four records were there