As you all are aware that most of the integrity checks are scheduled to run during the weekend and our clients are no exception. The client of us also have the similar schedule, the database integrity check runs on Sunday midnight. While the DBCC CHECKDB was running we had a page and then the SQL Server shuts down unexpectedly. In this blog post, I will try to explain what we did to fix error 665 cause SQL Server shuts down unexpectedly.
While we did review the SQL Server logs, found that the details are almost self explanatory. It says, The operating system returned error 665 which is actually NTFS file system limitation, when a DBCC CHECKDB hit sparse technology limitation during creation of the snapshot file and in turn fail to create snapshot file. These details i.e. How DBCC CHECKDB works is well explained by sir Paul Randel here in Part-1 and Part-2 I will not go into that details. The error details you can read in the error message below, that was the our case. DBCC CHECKDB caused SQL Server shutdown unexpected, and there were error 3314, error 3449 and error 831 recorded in the error log which helped us to research and find the fix. Below is an excerpts from the error log.
2017-01-29 03:17:23.32 spid195 DBCC CHECKDB (SSCLegacyDB) WITH no_infomsgs executed by NT SERVICE\SQLSERVERAGENT found 0 errors and repaired 0 errors. Elapsed time: 0 hours 2 minutes 8 seconds. Internal database snapshot has split point LSN = 0000043d:00006096:0001 and first LSN = 0000043d:00006094:0001. 2017-01-29 03:27:28.22 spid76 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 0x0000639520e000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSCLegacyDB.mdf:MSSQL_DBCC22'. 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. 2017-01-29 03:27:28.22 spid76 Error: 3314, Severity: 17, State: 3. 2017-01-29 03:27:28.22 spid76 During undoing of a logged operation in database 'SSCLegacyDB', an error occurred at log record ID (55379:36772:42). 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. 2017-01-29 03:27:46.36 spid76 Error: 831, Severity: 20, State: 1. 2017-01-29 03:27:46.36 spid76 Unable to deallocate a kept page. 2017-01-29 03:27:46.37 spid76 Error: 3449, Severity: 21, State: 1. 2017-01-29 03:27:46.37 spid76 SQL Server must shut down in order to recover a database (database ID 22). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database. 2017-01-29 03:27:46.37 spid76 SQL Server shutdown has been initiated 2017-01-29 03:27:46.37 spid76 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
What did we do to fix error 665 that caused SQL Server shuts down unexpectedly?
This server that we have this issue reported is running on SQL Server 2012 SP2. After doing little research we have found that it’s a bug that we are hitting and we have found out that there MS has released SQL Server 2012 SP2 CU5 which has this bug fixed. And, all theseError: 3314, Severity: 17, State: 3 details are well explained in an KB 3044958 article . At this moment we have cited all these details to customer and have ask a maintenance window to patch the SQL Server, in the interim we have disabled the integrity check jobs on this server.
Let me know if you have ever encounter this issue and how did you fix error 665 that caused SQL Server shuts down unexpectedly in the comment section or via email or via twitter.
Photo Credit: https://flic.kr/p/9ZU8xP
