DBCC CheckDB 遇到早期硬體故障

自 6 月下旬以來，我們的生產數據庫伺服器（Windows Server 2008 R2 DC 上的 SQL 2008 R2 Enterprise）在其夜間 DBCC CheckDB (Prod_DB) 中遇到了一個奇怪的錯誤。那時，同樣的工作一直執行良好。

我已經從這台伺服器恢復了完整備份到測試伺服器，數據文件似乎沒問題，DBCC CheckDB 沒有返回錯誤。

我上週應用了這個修補程序，但問題沒有得到解決。

https://support.microsoft.com/en-us/kb/967351/en-us

該消息聽起來很嚴重，但是，該應用程序在其他方面執行正常。所以我不確定我在這裡有什麼。

如果有人可以分享任何建議，我們將不勝感激。

這是來自 SQL 代理作業歷史記錄的錯誤消息：

Date                      7/28/2015 4:02:00 AM
Log                         Job History (DBCC Check DBs)

Step ID                 1
Server                   XXX
Job Name                            DBCC Check DBs
Step Name                         check dbs
Duration                              00:12:37
Sql Severity        17
Sql Message ID 823
Operator Emailed            
Operator Net sent           
Operator Paged                
Retries Attempted          0

Message
Executed as user: XXX. During undoing of a logged operation in database 

'Prod_DB', an error occurred at log record ID (21235:2462484:113). 
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database. 
[SQLSTATE 42000] (Error 3314)  A database snapshot cannot be created because it failed to start. 
[SQLSTATE 42000] (Error 1823)  A database snapshot cannot be created because it failed to start. 
[SQLSTATE 42000] (Error 1823)  The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline. 
[SQLSTATE 42000] (Error 7928)  The database could not be exclusively locked to perform the operation. 
[SQLSTATE 42000] (Error 5030)  Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. 
[SQLSTATE 42000] (Error 7926)  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00001a68024000 in file 'M:\Datab\Prod_DB.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. 
[SQLSTATE 01000] (Error 823)  The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x00002fd543e000 in file 'M:\Datab\Prod_DB.mdf:MSSQL_DBCC9'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. 
[SQLSTATE 01000] (Error 823).  The step failed.

當您第一次啟動時DBCC CHECKDB，它將創建一個數據庫快照，以允許數據庫在此過程中保持線上。如果您遇到此問題，可以查看以下內容：

1. 對您的 NTFS 系統進行碎片整理，如果 NTFS 儲存碎片嚴重，則存在一個問題，它將不允許 SQL Server 創建數據庫快照。這是因為快照只能有一定數量的片段。微軟支持中心在作業系統錯誤 1450 和 665 報告數據庫數據文件中記錄了這一點。（我相信你的問題就在這裡）
2. 檢查是否有人更新了 NTFS 過濾器驅動程序，因為備用過濾器流可能存在問題。

此外，您始終可以嘗試在 NTFS 的碎片較少的部分上創建數據庫的數據庫快照，並CHECKDB希望在那裡執行。

社區維基回答：

根據 Microsoft 客戶服務和支持 (CSS) 文章，錯誤 665 指向文件系統限制：

• 作業系統錯誤（665 – 文件系統限制）不再僅適用於 DBCC
• 稀疏文件錯誤：1450 或 665 由於文件碎片：修復和解決方法

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