Question about database transaction log

Question

I read the following statement:

SQL Server doesn’t write data immediately to disk. It is kept in a buffer cache until this cache is full or until SQL Server issues a checkpoint, and then the data is written out. If a power failure occurs while the cache is still filling up, then that data is lost. Once the power comes back, though, SQL Server would start from its last checkpoint state, and any updates after the last checkpoint that were logged as successful transactions will be performed from the transaction log.

And a couple of questions arise:

1. What if the power failure happens after SQL Server issues a checkpoint and before the buffer cache is actuall written to disk? Isn't the content in buffer cache permanently missing?

2. The transaction log is also stored as disk file, which is no different from the actual database file. So how could we guarantee the integrity of log file?

So, is it true that no real transaction ever exists? It's only a matter of probability.

Answer 1

The statement is correct in that data can be written to cache, but misses the vital point that SQL Server uses a technique called Write Ahead Logging (WAL). The writes to the log are not cached, and a transaction is only considered complete once the transaction records have been written to the log.

http://msdn.microsoft.com/en-us/library/ms186259.aspx

In the event of a failure, the log is replayed as you mention, but the situation regarding the data pages still being in memory and not written to disk does not matter, since the log of their modification is stored and can be retrieved.

It is not true that there is no real transaction, but if you are operating in simple logging mode then the ability to replay is not there.

For the integrity of the log file / same as the data file - a proper backup schedule and a proper restore testing schedule - do not just backup data / logs and assume they work.

Answer 2

What if the power failure happens after SQL Server issues a checkpoint and before the buffer cache is actuall written to disk? Isn't the content in buffer cache permanently missing?

The checkpoint start and end are different records on the transaction log.

The checkpoint is marked as succeeded only after the end of the checkpoint has been written into the log and the LSN of the oldest living transaction (including the checkpoint itself) is written into the database.

If the checkpoint fails to complete, the database is rolled back to the previous LSN, taking the data from the transaction log as necessary.

The transaction log is also stored as disk file, which is no different from the actual database file. So how could we guarantee the integrity of log file?

We couldn't. It's just the data are stored in two places rather than one.

If someone steals your server with both data and log files on it, your transactions are lost.