CODEWITHSUNDEEP
sqlsql-server
Aleksa
Aleksa

Reputation: 3124

SQL Server insert flow

As I understood reading some articles in internet, SQL Server has a buffer cache where it stores pages, and when insert statement is executed, the modified data is written only to that buffer in memory, not to the disk.

And when system checkpoint comes all dirty pages are flushed to disk.

Does this mean that when we execute insert statement and get a return value that everything was ok, the data might still not be written to disk and in theory if system crash occurs before checkpoint, the dirty pages wont be saved to disk although we received information that everything was ok and the transaction is commited?

Upvotes: 0

Views: 119

Answers (1)

TomTom
TomTom

Reputation: 62157

No. Because you totally ignore the 2nd part of the mechanism - the LOG FILE. The Log file keeps all changed pages and is flushed to disc. In case of a crash, upon start, the server will replay the changes from the log file.

Upvotes: 2

Related Questions