Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 CHECKDB Failing

Author  Topic 

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-04 : 18:16:41
DBCC CHECKDB is failing on one of my critical databases, but I don't think there's corruption. Here's the output:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbname'.
Msg 926, Level 21, State 6, Line 1
Database 'dbname' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

I manually created a database snapshot and ran checkdb on that, it came back clean:

DBCC results for 'dbname_ss'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'dbname_ss'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've already opened a case with Microsoft but am wondering if this is unlikely data corruption. When I first spoke with the engineer, I was told to restore the database. But this was before I ran checkdb on the database snapshot.

I think I'm encountering a Windows sparse file issue or a filter driver issue. I've got our McAfee team working on some issues with the server that is blocking our exclusions (*.mdf, *.ldf, *.bak, etc).

Do you think this is unlikely data corruption given the clean checkdb on the newly created database snapshot?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-04 : 18:19:27
Cross posted to Twitter #sqlhelp

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-04 : 18:25:46
Here are the errors in the Error Log:

F:\Data1\dbname.mdf:MSSQL_DBCC11: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
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 0x00000cb76c6000 in file 'F:\Data1\dbname.mdf:MSSQL_DBCC11'. 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.
F:\Data1\dbname.mdf:MSSQL_DBCC11: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-04 : 18:30:58
Argenis and Robert Davis agree that this is a sparse file issue and not data corruption. Yippee!

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jrmclennan
Starting Member

1 Post

Posted - 2014-03-19 : 13:59:27
Tara,

I just ran into this error this morning during a CHECKDB. Same error. My research shows this is an OS issue, too, "Yippee!" I am on Win 2008 R2, SQL 2012.

Did you ever find a solution or a hotfix?

Thanks,
Jon
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-19 : 14:01:29
We actually did install a hotfix last night, but I haven't yet tested if checkdb is okay. I changed the job to use its own database snapshot to get around the issue for now. I need to schedule a regular checkdb to see if the hotfix worked. I'll reply back with those results.

The hotfix I applied is not relevant for WIndows 2008 R2. This particular server of ours is running Windows 2008. You should probably open a case with MS to find out what is available for R2.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-19 : 19:35:46
The hotfix did not resolve it for us. Same error.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 2014-04-22 : 01:19:32
If DBCC CHECKDB reports consistency errors is to restore from a known good backup. However, if you cannot restore from a backup, then CHECKDB provides a feature to repair errors. If system level problems such as the file system or hardware may be causing these problems, it is recommended you correct these first before restoring or running repair.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-22 : 14:02:18
elliswhite, the issue that I reported is not a consistency issue. Yes CHECKDB was failing, but it clearly says "0 consistency errors".

The resolution was to manually create a database snapshot and run checkdb on that. We could have fixed this in another way by resolving the sparse file issue, but that would have required downtime.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 2014-04-23 : 02:06:17
Firstly you must go through SQL-Server error logs and find out the root cause of the error from Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-23 : 12:44:42
quote:
Originally posted by elliswhite

Firstly you must go through SQL-Server error logs and find out the root cause of the error from Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.



No, not for this issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

henro
Starting Member

1 Post

Posted - 2014-08-26 : 03:54:30
Tara,

do you have any updates to your situation you might want to share with us?
I am running into exactly the same issue on a Win2012 Server with SQL 2012. In our case it is the REPORTSERVER database crashing often (but not regularly). I am at a total loss where to go from here....

Greetings,
Henro
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-26 : 12:23:48
Crashing? My database wasn't crashing. It was simply a sparse file issue that we worked around by running DBCC CHECKDB against an explicit database snapshot.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-09-01 : 10:11:35
quote:
Originally posted by tkizer

Here are the errors in the Error Log:

F:\Data1\dbname.mdf:MSSQL_DBCC11: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
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 0x00000cb76c6000 in file 'F:\Data1\dbname.mdf:MSSQL_DBCC11'.



Tara I am not sure whether you referred to this link 'http://support.microsoft.com/kb/2002606/en-gb' but as per link this is known issue especially the error 665. As others have noted this is windows Server limitation nothing to do with SQL Server and your database is not corrupt rest assured, I am telling so because manually when you are running checkdb on snapshot its suceeding.

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-02 : 14:56:39
quote:
Originally posted by Shanky

quote:
Originally posted by tkizer

Here are the errors in the Error Log:

F:\Data1\dbname.mdf:MSSQL_DBCC11: Operating system error 665(The requested operation could not be completed due to a file system limitation) encountered.
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 0x00000cb76c6000 in file 'F:\Data1\dbname.mdf:MSSQL_DBCC11'.



Tara I am not sure whether you referred to this link 'http://support.microsoft.com/kb/2002606/en-gb' but as per link this is known issue especially the error 665. As others have noted this is windows Server limitation nothing to do with SQL Server and your database is not corrupt rest assured, I am telling so because manually when you are running checkdb on snapshot its suceeding.



Shanky, as mentioned above, this issue is resolved. Yes I was aware of that article and mentioned the Windows sparse file issue in my original post, which is from March. I actually don't work for that company anymore.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

AkhileshK
Starting Member

2 Posts

Posted - 2014-10-09 : 10:35:52
Hi Tara,

I am running into a similar situation with Win 2012 and SQL 2012. I am not sure about the hotfix but doubt it would resolve the issue. I ended up reading the similar kb articles mentioned above again and again. Could you please share, how to get rid of this error following a systematic procedure? I also came across a post http://blog.sqlguy.dk/ It would be helpful to know, what exactly causes the error?

Thanks in advance!

Thanks and Regards,
Akhil
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-09 : 11:18:41
Hi Akhil,

To get rid of the error, change your checkdb job so that it creates an explicit database snapshot and run checkdb on that instead.

It's due to Windows sparse files. Why it occurs I don't know. That's something Microsoft needs to answer.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

AkhileshK
Starting Member

2 Posts

Posted - 2014-10-10 : 03:44:50
Thanks Tara for your prompt response. I will try rescheduling the checkdb job as recommended.

Thanks and Regards,
Akhil
Go to Top of Page

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-10-15 : 03:27:56
Well at this point you must access your database in emergency mode which is read only as on changing mode you can copy the database into new database and achieve accessibility
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-15 : 12:41:27
quote:
Originally posted by Lincolnburrows

Well at this point you must access your database in emergency mode which is read only as on changing mode you can copy the database into new database and achieve accessibility



What? There is no need to do that for this issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -