還原事務日誌後，輔助日誌傳送數據庫變得可疑

配置 主數據庫 SQL Server 2008 R2 SP3 Web 版執行在 Windows Server 2012 R2 x64 上，本地 RAID 10 SAS 10k 磁碟陣列位於企業級數據中心中 輔助數據庫 SQL Server 2008 R2 SP3 Web 版執行在 Hyper-V windows Server 2012 R2 x64 VM 在我們的總部伺服器機房中使用 QNAP NAS (i​​SCSI) RAID 10 SAS 10k 磁碟陣列 這兩個實例通過專用硬體 VPN 通過光纖連接

問題 在為其中一個數據庫配置輔助日誌傳送（只讀模式）後，它似乎可以正常執行一兩天，然後在其中一個 LS_Restore sql 代理作業中隨機恢復失敗，並且發現數據庫位於SUSPECT模式。請參閱下面的 SQL 錯誤日誌。我們已經將它設置並拆除了大約 4 次，但每次仍然發生相同的情況……

基於關於日誌尾部和未對齊 IO 的警告進行了大量研究後，我懷疑原因可能與 Hyper-V 在目標（輔助日誌傳送數據庫）創建的虛擬磁碟正在使用的事實有關新的 4K 原生格式，而源上的磁碟使用更常見的 512E 標準（請參見下面的螢幕截圖）。每個物理扇區的字節數和每個群的字節數是不同的！

這聽起來合理嗎？或者其他人有這個問題嗎？如果有，你做了什麼來解決它？

更新：發現這個 KB 準確描述了我的問題，但它僅適用於 SP2 而不是 SP3： https: //support.microsoft.com/en-us/kb/2987585

SQL 日誌文件

03/11/2016 22:00:07,spid57,Unknown,An error occurred while processing the log for database 'QfsFinancial'.  If possible<c/> restore from backup. If a backup is not available<c/> it might be necessary to rebuild the log.
03/11/2016 22:00:07,spid57,Unknown,Error: 9004<c/> Severity: 16<c/> State: 6.
03/11/2016 22:00:07,spid57,Unknown,There have been 16128 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file E:\Databases\QFSFinancial\LOG\QfsFinancial_log.ldf.
03/11/2016 22:00:05,spid57,Unknown,The database 'QfsFinancial' is marked RESTORING and is in a state that does not allow recovery to be run.
03/11/2016 22:00:05,spid57,Unknown,Starting up database 'QfsFinancial'.
03/11/2016 22:00:05,Backup,Unknown,Log was restored. Database: QfsFinancial<c/> creation date(time): 2015/06/30(22:41:11)<c/> first LSN: 205383:118850:1<c/> last LSN: 205383:120116:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'E:\LogShippedLogs\QfsFinancial_20160311081500.trn'}). This is an informational message. No user action is required.
03/11/2016 22:00:05,spid57,Unknown,CHECKDB for database 'QfsFinancial' finished without errors on 2016-03-06 02:15:14.853 (local time). This is an informational message only; no user action is required.
03/11/2016 22:00:05,spid57,Unknown,Starting up database 'QfsFinancial'.
03/11/2016 22:00:04,spid57,Unknown,Recovery is writing a checkpoint in database 'QfsFinancial' (8). This is an informational message only. No user action is required.
03/11/2016 22:00:04,spid57,Unknown,The tail of the log for database QfsFinancial is being rewritten to match the new sector size of 4096 bytes.  2048 bytes at offset 62089216 in file E:\Databases\QFSFinancial\LOG\QfsFinancial_log.ldf will be written.
03/11/2016 22:00:04,spid57,Unknown,The database 'QfsFinancial' is marked RESTORING and is in a state that does not allow recovery to be run.
03/11/2016 22:00:04,spid57,Unknown,Starting up database 'QfsFinancial'.
03/11/2016 22:00:03,Backup,Unknown,Log was restored. Database: QfsFinancial<c/> creation date(time): 2015/06/30(22:41:11)<c/> first LSN: 205383:113524:1<c/> last LSN: 205383:118850:1<c/> number of dump devices: 1<c/> device information: (FILE=1<c/> TYPE=DISK: {'E:\LogShippedLogs\QfsFinancial_20160311080000.trn'}). This is an informational message. No user action is required.
03/11/2016 22:00:03,spid57,Unknown,CHECKDB for database 'QfsFinancial' finished without errors on 2016-03-06 02:15:14.853 (local time). This is an informational message only; no user action is required.
03/11/2016 22:00:03,spid57,Unknown,Starting up database 'QfsFinancial'.
03/11/2016 22:00:03,spid57,Unknown,Recovery completed for database QfsFinancial (database ID 8) in 1 second(s) (analysis 204 ms<c/> redo 0 ms<c/> undo 82 ms.) This is an informational message only. No user action is required.
03/11/2016 22:00:03,spid57,Unknown,Recovery is writing a checkpoint in database 'QfsFinancial' (8). This is an informational message only. No user action is required.
03/11/2016 22:00:02,spid57,Unknown,The tail of the log for database QfsFinancial is being rewritten to match the new sector size of 4096 bytes.  3072 bytes at offset 61441024 in file E:\Databases\QFSFinancial\LOG\QfsFinancial_log.ldf will be written.
03/11/2016 22:00:02,spid57,Unknown,The database 'QfsFinancial' is marked RESTORING and is in a state that does not allow recovery to be run.
03/11/2016 22:00:02,spid57,Unknown,Starting up database 'QfsFinancial'.
03/11/2016 22:00:00,spid57,Unknown,Setting database option SINGLE_USER to ON for database QfsFinancial.

源伺服器上的磁碟格式

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>fsutil fsinfo ntfsinfo E:
NTFS Volume Serial Number :       0x58b83b05b83ae164
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x000000008bb83fff
Total Clusters :                  0x00000000117707ff
Free Clusters  :                  0x000000000117e33c
Total Reserved :                  0x0000000000000780
Bytes Per Sector  :               512
Bytes Per Physical Sector :       512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x000000000b180000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0x0000000000000002
Mft Zone Start :                  0x000000000482eb60
Mft Zone End   :                  0x0000000004830000
Resource Manager Identifier :     CC229DD7-1DBF-11E4-80B4-A0D3C1F1E137

C:\Windows\system32>

目標伺服器上的磁碟格式

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Windows\system32>fsutil fsinfo ntfsinfo E:
NTFS Volume Serial Number :       0x827ec3877ec37289
NTFS Version   :                  3.1
LFS Version    :                  2.0
Number Sectors :                  0x00000000bb7fe7ff
Total Clusters :                  0x000000000176ffcf
Free Clusters  :                  0x0000000000a1e4f8
Total Reserved :                  0x0000000000000000
Bytes Per Sector  :               512
Bytes Per Physical Sector :       4096
Bytes Per Cluster :               65536
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0x00000000000c0000
Mft Start Lcn  :                  0x000000000000c000
Mft2 Start Lcn :                  0x0000000000000001
Mft Zone Start :                  0x000000000000c000
Mft Zone End   :                  0x000000000000cca0
Resource Manager Identifier :     EE6D99B6-DC5D-11E5-80E2-00155D323B15

C:\Windows\system32>

我已經解除安裝了 SP3 並安裝了 SP2，然後是我在問題中提到的修補程序(KB2987585)，最後重新安裝了 SP3。到目前為止一切順利 - 一旦我們成功進行了幾天的日誌傳送，我將更新。

根據我上面的評論，問題似乎已解決。回滾到 SP2（沒有 SP2 CU，否則無法安裝此修復程序）並安裝 KB2987585 修補程序並啟用該修補程序的關聯跟踪標誌，然後重新應用 SP3，我的所有問題都消失了，現在它已經平穩執行了 5 天沒有警告或數據庫進入可疑模式。

引用自：https://dba.stackexchange.com/questions/132096