Transaction log management in SQL Server (4/9): Log management in the simple recovery model

When everything is OK, there is no need to pay particular attention to what the transaction log is and how it works. You just have to make sure that each database has the correct backup. When a problem occurs, the understanding of the transaction log is important to take corrective action, especially if the database needs to be urgently restored to the specified point. This series of articles will tell you the specifics of what each DBA should know.

This title is almost a misnomer because, to a large extent, running in simple mode does not require log management. In simple mode, the only purpose of the transaction log is to guarantee the ACID properties of the transaction during database recovery operations, as well as to enforce database consistency and transactional persistence. The transaction log cannot be backed up, cannot be used for database recovery, and cannot be used as a log transfer.

working in simple mode

All transactions are still logged, although a particular bulk operation is a minimized log; in fact, the log level and the bulk-logged application are very similar. The active part of the log is maintained as usual, so that whenever the database is started in a simple mode, the recovery process enters and the data file is reconciled with the transaction log content.

However, in simple mode, all virtual log files (VLF) are marked as inactive (recoverable) in the 2nd chapter, and are automatically truncated during periodic database checkpoints. This means that when checkpoints occur, any VLF that has a maximum LSN less than the lowest LSN in the file is truncated, and the resulting space in the transaction file is periodically and frequently reused.

The database in the simple recovery model will always be the automatic truncation mode. As described in the 3rd article, all user databases are actually automatically truncated before the first full backup is performed.

How much time does the checkpoint take place?

In order to recover the database to recovery interval (recovery interval) server configuration option, the SQL Server engine determines how much time to checkpoint, based on the number of log records that need to be processed, in the specified time. If your database is read-only, the time between checkpoints can be very long. However, in a system that is frequently updated on the business, checkpoints occur nearly every minute. Click Https://msdn.microsoft.com/zh-cn/library/ms189573.aspx to see the detailed description.

As discussed in the previous article, in the full recovery model, the transaction log maintains "inactive history or closed transactions", along with active/open transactions. This "history" can be captured in a log backup to restore the database to a previous point in time. However, in simple mode, this history does not exist so the log cannot be used to restore the database to a previous point in time. In fact, in the simple recovery model, you can't even make a transaction log backup, as shown in the following code.

 1  use   master;  2   DATABASE   TestDB  3  set   RECOVERY simple;  4  backup  log   TestDB  5   DISK  =   "   " 6  Span style= "color: #0000ff;" >go  

This means that your backup plan can only be executed in the full and differential backup plan.

support and objection to the simple mode

The downside of simple mode is that you will have a high chance of losing data because you can only restore the database to the nearest full or differential backup. As mentioned earlier, if the lost data is measured in minutes, not measured in hours, do not use simple mode.

However, if you are running a development or test database, or even a read-only production database, then using simple mode can be a viable, even a sensible choice, which can greatly reduce the maintenance burden on the database. Backup will have less storage space and subsequent recovery operations will be simpler. Also, because the transaction log is automatically truncated, you rarely have the risk of the log growing out of control, causing a 9002 error.

Although simple mode significantly reduces the burden of transaction log management, this is considered wrong. If you use this mode, you will completely forget the log maintenance. The transaction log plays an important role in the daily operation of the database, and you still need to properly adjust the size and growth of the transaction log, depending on the transaction nature and frequency of the database. Not because the logs are automatically truncated, which does not mean that robust and long-running transactions do not cause the log to grow fast, and if you do not resize correctly, it will cause you trouble-this will be described in detail in the 7th-8th article.

Transaction log management in SQL Server (4/9): Log management in the simple recovery model