The role of SQL Server logs in high availability and disaster recovery

Brief introduction

The role of the log is to ensure persistence and data consistency, through the log can achieve data undo and redo, so through the log, SQL Server can not only achieve disaster recovery, but also through the redo of the log to achieve high availability. This article focuses on the role of logging in several high-availability scenarios provided in SQL Server and in disaster recovery.

Log corruption

The log may be corrupted due to an IO subsystem failure. When log corruption occurs, if you have a little knowledge of the log, and can save the data as much as possible in case of log corruption, then it must be a very good:-), so let's look at the recovery of several log corruptions.

1. The database shuts down properly and logs are corrupted.

Log corruption is less important when the database is shut down properly because dirty data for all committed transactions in the database has been checkpoint to the physical disk, so there is no data inconsistency. Therefore, if the MDF and ndf files are intact, you can attach them directly after you specify the for Attach_rebuild_log parameter, as shown in Figure 1.

Figure 1. If the database shuts down properly, attach directly

However, it is noteworthy that the use of this method to attach a database automatically rebuilds the log files, the log file size is 0.5MB, that is, 2 VLF, automatically grow to 10%, so you need to manually set the size of the log, to avoid too many VLF.

2. The database is not properly shut down, log damage

Before we talk about this, let's look at a few states that the database can be in, a complete model as shown in Figure 2.

Figure 2. The state relationship that a database can be in

The specific transition relationships for the above states are beyond the scope of this article, but here I will highlight two states that have a significant relationship to log corruption: Recovery_pending and suspect states.

If there is no normal shutdown of the database, that is, there is no data checkpoint to disk, if the database to start must go through the recovery process, if the log is damaged, you can not carry out the recovery process, will result in inconsistent data problems.

In this case, the database may be in one of the following two states:

Recovery_pending: The crash RECOVERY needs to be run, but the process cannot start because of resource waiting, such as a complete log corruption

Suspect:crash recovery has begun, but cannot be completed