我們一個SQL Server伺服器在執行YourSQLDBa的作業YourSQLDba_FullBackups_And_Maintenance時遇到了錯誤： Exec YourSQLDba.Maint.ShowHistoryErrors @JobNo = 1227 yMaint.IntegrityT... ...
我們一個SQL Server伺服器在執行YourSQLDBa的作業YourSQLDba_FullBackups_And_Maintenance時遇到了錯誤：
Exec YourSQLDba.Maint.ShowHistoryErrors @JobNo = 1227
<row>
<ctx>yMaint.IntegrityTesting</ctx>
<Sql>DBCC checkDb('xxxx') </Sql>
<err>In case of non-completion of this command check SQLServer Error Log at 2016-11-17 00:00:03.327 for Spid 67</err>
</row>
錯誤日誌裡面有下麵一些錯誤信息，如下所示：
Date 2016/11/17 0:53:21 Log SQL Server (Archive #8 - 2016/11/18 0:00:00) Source spid67 Message DBCC CHECKDB (xxxx) executed by xxxxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 53 minutes 18 seconds. Internal database snapshot has split point LSN = 00623e60:00004a5b:0001 and first LSN = 00623e60:000048f3:0011. This is an informational message only. No user action is required. Date 2016/11/17 0:53:21 Log SQL Server (Archive #8 - 2016/11/18 0:00:00) Source spid13s Message Write to sparse file 'xxxxxxx.ndf:MSSQL_DBCC10' failed due to lack of disk space. SQL Server Date 2016/11/17 0:53:21 Log SQL Server (Archive #8 - 2016/11/18 0:00:00) Source spid129 Message D:\xxx\xxxx\xxxxx.ndf:MSSQL_DBCC10: Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered.
另外，我們的磁碟空間告警作業也收到了大量的磁碟空間告警郵件：
其實在白天的時候，該伺服器就已經有磁碟空間告警郵件發出，由於系統管理員想在周末做存儲擴容。所以在晚上資料庫做DBCC CHECKDB時，就由於磁碟空間不足，遭遇了這個錯誤。DBCC CHECKDB時會生成資料庫快照，而資料庫的快照文件是基於稀疏文件(Sparse File),而當快照創建後，隨著對源資料庫的改變逐漸增多，稀疏文件也會慢慢增長。所以，當磁碟空間不足時，就會遇到下麵錯誤信息：
1：Write to sparse file 'xxxxxxx.ndf:MSSQL_DBCC10' failed due to lack of disk
2：SSQL_DBCC10: Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered
此時由於磁碟空間不足，就會導致DBCC CHECKDB停止。關於DBCC CHECKDB耗用大量額外磁碟空間，可以參考下麵這篇博客。
CHECKDB From Every Angle: Why would CHECKDB run out of space?
參考資料：
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-why-would-checkdb-run-out-of-space/
