Shulou(Shulou.com)05/31 Report--

This article focuses on "how to deal with error 9004 exceptions after SQLServer mirror downtime". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to deal with error 9004 exceptions after SQLServer mirror downtime".

Exception:

A SQLServer 2008SP4 mirror server is down due to a hardware problem and cannot be synchronized with the principal main library after repair and restart

Error reported in mirror error log:

Date 02:15:20 on 2020-5-2

Log SQL Server (Archive # 3-11:55:00 on 2020-5-4)

Source spid17s

Message

Database mirroring will be suspended. Server instance 'SMESDBSTY' encountered error 9004, state 2, severity 21 when it was acting as a mirroring partner for database' MESDB'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

Date 02:15:20 on 2020-5-2

Log SQL Server (Archive # 3-11:55:00 on 2020-5-4)

Source spid17s

Message

An error occurred while processing the log for database 'MESDB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

An error was reported in principal (master database) error log:

Date 02:04:21 on 2020-5-4

Log SQL Server (Current-05:45:00 on 2020-5-4)

Source spid19s

Message

'TCP://10.209.95.203:5022', the remote mirroring partner for database 'MESDB', encountered error 9004, status 2, severity 21. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

Date 02:04:21 on 2020-5-4

Log SQL Server (Current-05:45:00 on 2020-5-4)

Source spid19s

Message

Error: 1453, Severity: 16, State: 1.

Deal with:

After trying to restart the standby library mirror server, it is still unable to synchronize with the main library. Only from the log analysis, we can see that both principal and Mirror log have error 9004 error estimates related to this, Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance. The solution in the error tip is more general, solving the error can restore the mirror or remove the mirror reconstruction.

There seems to be no way to solve error 9004. It is still not possible to try to remove the mirror partner relationship and re-establish the mirror relationship, and it is too much work to re-backup and restore.

You can only find the answer in the error 9004 error and find an official 9004 error description:

Https://support.microsoft.com/en-ca/help/2015753/how-to-troubleshoot-error-9004-in-sql-server

Symptoms

An operation in SQL Server that needs to read or process the transaction log can fail with an error like the following if the transaction log is damaged:

Error: 9004, Severity: 21, State: 1.

An error occurred while processing the log for database 'mydb'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

The State number can vary for this error and indicates what type of damage has occurred with the log. See the More Information section about State numbers.

In most cases, this error is just seen in the ERRORLOG or Windows Application Event Log with EventID = 9004 because the operation processing the log is not based on a direct user command (such as recovery running when the SQL Server Engine starts. In these situations this error is often seen with Error 3414). However, some queries such as ALTER DATABASE could require a processing of the log and therefore will see these errors. Since the error is Severity=21, the user session is disconnected.

Cause

Error 9004 is a general error indicating the contents of the transaction log are damaged. The reason for the log to become inconsistent are similar to any database corruption problem detected by the SQL Server Engine or DBCC CHECKDB. To find the cause for the damage of the log you should follow the similar techniques for database corruption including an analysis of possible hardware, filesystem, and/or I/O problems. See the Cause section of the following article for more information: How to troubleshoot database consistency errors reported by DBCC CHECKDB.

Resolution

You should restore from a known good backup to recover from this problem. It is possible that if the transaction log portion of a database backup or the transaction log backup itself has damaged transaction log contents, you can encounter an Error 9004 on RESTORE. In this situation, the transaction log in the backup is damaged.

If you cannot restore from a backup, you may be able to bring the database online by rebuilding the transaction log. You should carefully understand the ramifications of rebuilding the transaction log including the possible loss of transactional consistency in your database. To read about how to rebuild the transaction log, please see the section titled Resolving Database Errors in Emergency Mode in the SQL Server Books Online under the DBCC CHECKDB command

More Information

The SQL Server Engine performs logical checks on the consistency of the transaction log contents as it reads and processes it. Not all aspects of the log header, log blocks, and log records are checked. The State number provides more information on what type of failure was encountered when processing the transaction log:

State 1 = The log file header of the Virtual Log File (VLF) was damaged. If a damaged log file header is encountered as part of starting up the database on service startup, you may only see Error 9004 in the ERRORLOG. The log file header is the first portion of each VLF in the log file. This is not the same as the file header or the first 8KB of the log file. If the file header of the the log file is damaged you will encounter Msg 5172 as with a database file header page corruption.

State 2 and 3 = A log block was invalid when performing recovery during RESTORE

State 4 through 12 = These are all various checks on log blocks when processing log records. These including parity, sector, and other logical checks on the consistency of the transaction log

From the documentation, it should be the DB transaction log damage of mirror that caused the inconsistency, and the damage of tran log is likely to be related to this server hardware downtime. Look at the error log at mirror startup again and find that it is indeed caused by transaction log damage.

Date 02:15:20 on 2020-5-2

Log SQL Server (Archive # 3-11:55:00 on 2020-5-4)

Source spid17s

Message

An error occurred while processing the log for database 'MESDB'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

How to repair tran log damage?

Because the job that backs up the tran log every 15 minutes is set in the main library (in case the log is too big to be reused under full recovery mode)

In theory, it should be OK to restore the logs backed up by the main database to the Mirror side.

Hyperemesis:

1. Remove the master / slave mirror partner relationship and execute it on the Mirror end:

Alter database MESDB set partner off

Tran log generated on the same day and after the exception in the 2.copy principal main library to Miror

3. You can use the following execution results to recover tranlog in batches (remove go and try again if there are any syntax errors)

Select 'RESTORE LOG [MESDB] FROM DISK = Nissan' + physical_device_name +''WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10' + char (13) + 'go',backup_set_id,a.type,physical_device_name from msdb.dbo.backupset a, msdb.dbo.backupmediafamily as b where a.media_set_id=b.media_set_id and a. Database

4. After re-establishing the mirror partner relationship, the principal and Mirror can synchronize data normally.

At this point, mirror error 9004 exception handling is complete

At this point, I believe you have a deeper understanding of "how to deal with error 9004 exceptions after SQLServer mirror downtime". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.