Several statuses that SQL Server databases can be in

Welcome to the Windows community forum and interact with 3 million technical staff to enter the database status. A complete model is as follows: the specific transition relationships of several statuses that the database can be in are beyond the scope of this article, but here I will emphasize the two statuses that are closely related to log corruption: RECOVERY_PENDING

Welcome to the Windows community forum and interact with 3 million technical staff> enter several statuses that the database can be in. A complete model is as follows: the specific transition relationships of several statuses that the database can be in are beyond the scope of this article, but here I will emphasize the two statuses that are closely related to log corruption: RECOVERY_PENDING

Welcome to the Windows community forum and interact with 3 million technicians>

The database can be in several States. A complete model is as follows:

The state of the database.

The specific transition relationships of the above States are beyond the scope of this article, but here I will emphasize the two States that are closely related to log corruption: RECOVERY_PENDING and SUSPECT.

If the database is not shut down normally, that is, there is still data that has not been CheckPoint to the disk. If the database is to be started, it must go through the Recovery process. If the log is damaged, the Recovery process cannot be performed, data inconsistency may occur.

At this time, the database may be in one of the following two states:

RECOVERY_PENDING: You need to run crash recovery, but the process cannot begin due to resource waiting. For example, the log is completely damaged.

SUSPECT: crash recovery has started, but cannot be completed

Therefore, to handle this situation, you must determine whether data loss is allowed based on your business environment. You can choose to recover data from the backup or change the database status to EMERGENCY. EMERGENCY Mode means that the database skips the crash recovery stage. Although the database can be accessed at this time, data transaction inconsistency may occur. If only some data pages are inconsistent, however, if the transaction for table structure modification exists, the database architecture may be inconsistent. If you do not have an appropriate backup set, you can only restore the data in this way. Setting the database to EMERGENCY mode is simple, as shown in code list 1.

Alter database AdventureWorks2012 SET EMERGENCY

Code List 1. Set the database to emergency mode

One option related to this mode is REPAIR_ALLOW_DATA_LOSS. This option will still execute the crash recovery process, but will skip the damaged days to fix Data Consistency issues as much as possible, this option creates a new log file and makes the database ONLINE. A simple example of using this option is shown in code list 2.

Alter database AdventureWorks2012 SET SINGLE_USER

Dbcc checkdb (AdventureWorks2012, REPAIR_ALLOW_DATA_LOSS)

Code List 2. Use the REPAIR_ALLOW_DATA_LOSS Option

It is worth noting that, as a DBA, you must always have "slave", and the above operations should be considered if you have insufficient preparation.

The database is online and logs are corrupted.

In this case, if the log to be used by SQL Server during running is corrupted (for example, during rollback), SQL Server will deprecate the database and switch it to the SUSPECT mode.

If there is no backup, you can only consider using the EMERGENCY mode.

Another way is to change the database recovery mode to simple, manually initiate a CheckPoint to truncate the log, and then change the database back to the full recovery mode. However, this method will destroy the log chain. However, the damaged logs may be cleared.