本篇内容主要讲解“怎么处理SQLServer mirror宕机后error 9004异常”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么处理SQLServer mirror宕机后error 9004异常”吧!
异常：
一台SQLServer 2008SP4 mirror server因为硬件问题宕机，修复重启后就无法与principal主库连上同步了
mirror error log中报错：
Date 2020/5/2 上午 02:15:20
Log SQL Server (Archive #3 - 2020/5/4 上午 11:55:00)
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 2020/5/2 上午 02:15:20
Log SQL Server (Archive #3 - 2020/5/4 上午 11:55:00)
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.
principal(主库) error log 中报错：
Date 2020/5/4 下午 02:04:21
Log SQL Server (Current - 2020/5/4 下午 05:45:00)
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 2020/5/4 下午 02:04:21
Log SQL Server (Current - 2020/5/4 下午 05:45:00)
Source spid19s
Message
Error: 1453, Severity: 16, State: 1.
处理：
尝试重启备库mirror server后依然无法和主库同步，只能从log分析，看到principal和mirror端log中都有error 9004报错估计和这个有关，Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance. 错误提示中的解决方法说的比较笼统，解决error可恢复mirror或者移除mirror重建。
解决error 9004似乎无从下手，尝试移除mirror partner关系再重新建立mirror关系还是不行，重新备份恢复建立的话工作又太大了。。
只能在回到error 9004错误中找答案，查到官方一篇9004错误说明：
https://support.microsoft.com/en-ca/help/2015753/how-to-troubleshoot-error-9004-in-sql-server
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.
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.
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
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
从文档中看，应该是mirror的DB transaction log损坏导致不一致问题，而tran log的损坏也很可能与这此server硬件宕机有关，再次详细查找mirror启动时的error log发现果然是transaction log损坏导致
Date 2020/5/2 上午 02:15:20
Log SQL Server (Archive #3 - 2020/5/4 上午 11:55:00)
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.
tran log损坏如何修复呢？
因为主库中有设置每个15分钟把tran log备份出来的job (full recovery mode下以免log过大不能重用的做法)，
理论上讲通过把主库备份出来的日志到mirror端恢复应该就可以了,
剧吐操作：
1. 移除主备mirror partner关系，mirror端中执行：
alter database MESDB set partner off
2.copy principal主库中的异常当天及之后产生的tran log到mirror端
3.通过以下执行结果可批量运用恢复tranlog(如果语法有报错可以去掉go再试)
select 'RESTORE LOG [MESDB] FROM DISK = N''' + 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_name='MESDB' and a.backup_start_date >'2020-05-02 12:00:00'
4.重新建立mirror partner关系后，principal 和mirror端可以正常同步数据了
此时， mirror error 9004异常处理完成
到此，相信大家对“怎么处理SQLServer mirror宕机后error 9004异常”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
