代码改变世界

DBCC CHECKDB 遭遇Operating system error 112(failed to retrieve text for this error. Reason: 15105) encountered

2016-11-22 11:56  潇湘隐者  阅读(1199)  评论(4编辑  收藏  举报

我们一个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.

image

 

另外,我们的磁盘空间告警作业也收到了大量的磁盘空间告警邮件:

 

image

 

其实在白天的时候,该服务器就已经有磁盘空间告警邮件发出,由于系统管理员想在周末做存储扩容。所以在晚上数据库做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/