Question about database transaction log

Question

I read the following statement:

SQL Server does not write data to disk. It is stored in the buffer until this cache is full or until SQL Server issues a checkpoint, and then the data is written. If a power failure occurs when the cache is still full, then this data is lost. As soon as the power returns, SQL Server will start from the last checkpoint state and any updates after the last checkpoint that were logged, because successful transactions will be executed from the transaction log.

And a few questions arise:

• What to do if a power failure occurs after . Does SQL Server issue a checkpoint and up to the buffer cache written to disk? Is the contents of the buffer missing in the cache?

• The transaction log is also saved as a disk file, which is not different from the actual database file. So how can we guarantee the integrity of the log file?

So, is it true that no real transaction ever exists? This is just a matter of probability .

Answer 1

The assertion is true that data can be written to the cache, but misses the vital point at which SQL Server uses the Write Ahead Logging (WAL) method. Logging is not cached, and a transaction is considered complete only after writing transaction records to the log.

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

In the event of a failure, the log is played back, as you mention, but the situation regarding the data pages that are still in memory and not written to disk does not matter, since the log of their changes is stored and can be restored.

It is not true that there is no real transaction, but if you are working in simple logging mode, then there is no playback option.

For the integrity of the log file / the same as the data file — the correct backup schedule and the correct recovery testing schedule — not just back up the data / logs and assume that they work.

Answer 2

What if a power failure occurs after SQL Server issues a checkpoint and before the buffer cache is completely written to disk? Is the contents in the buffer cache missing?

The start and end of the checkpoint are different entries in the transaction log.

The checkpoint is marked as successful only after the checkpoint record is written to the block, and the LSN the oldest live transaction (including the checkpoint itself) is written to the database.

If the checkpoint is not completed, the database reverts to the previous LSN , taking data from the transaction log as needed.

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

We could not. It's just that the data is stored in two places, not in one.

If someone stole your server with both data and log files, your transactions are lost.