7

Our production database server (SQL 2008 R2 Enterprise on Windows Server 2008 R2 DC) run into a strange error during its nightly DBCC CheckDB (Prod_DB) since late June. The same job had been running fine up then.

I have restored the full backup from this server to a test server, and the data file seems ok, and the DBCC CheckDB returned no error there.

I applied this hotfix last week but did not get the issue resolved.

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

The message sounds serious enough, however, the application is otherwise functioning normally. So I am not sure what I am having here.

It will be grateful if any one could share any advice.

Here is the error message from SQL agent job history:

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.
3
  • 1
    Has the disk subsystem been checked (disk themselves, cards, firmware, etc.)? We had 823 errors recently on client system and they ended up finding an issue with a card in the system or something to that nature.
    – user507
    Jul 28, 2015 at 16:24
  • Have you checked to make sure the files don't have external limits placed on them in SQL Server or on the file system? Dec 9, 2015 at 20:03
  • Can you try creating your own snapshot and run checkdb on it? If that does not work try using with TABLOCK option in CHECKDB. The only issue with TABLOCK is it will lock your DB.
    – SQLPRODDBA
    May 19, 2016 at 14:53

2 Answers 2

1

When you first kick off a DBCC CHECKDB it will create a database snapshot to allow the database to stay online during the process. There are some things you can look at if you are running into problems with this:

  1. Defragment you NTFS system, there is an issue where if the NTFS storage is heavily fragmented it will not allow SQL Server to create the database snapshot. This is because the snapshot can only have a certain amount of fragments.This is documented on the microsoft support center in OS errors 1450 and 665 are reported for database data files. (I believe your problem lies here)
  2. Check to see if anyone has updated an NTFS filter driver as there can be a problem with alternate filter streams.

Additionally, you can always attempt to create a database snapshot of the database on a less fragmented part of the NTFS and run CHECKDB there to hopefully.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.