SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 CHECKDB Failing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkizer
Almighty SQL Goddess

USA
36895 Posts

Posted - 03/04/2014 :  18:16:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
36895 Posts

Posted - 03/04/2014 :  18:19:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
36895 Posts

Posted - 03/04/2014 :  18:25:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
36895 Posts

Posted - 03/04/2014 :  18:30:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Posts

Posted - 03/19/2014 :  13:59:27  Show Profile  Reply with Quote
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

USA
36895 Posts

Posted - 03/19/2014 :  14:01:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
36895 Posts

Posted - 03/19/2014 :  19:35:46  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/22/2014 :  01:19:32  Show Profile  Reply with Quote
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

USA
36895 Posts

Posted - 04/22/2014 :  14:02:18  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 04/23/2014 :  02:06:17  Show Profile  Reply with Quote
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

USA
36895 Posts

Posted - 04/23/2014 :  12:44:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Netherlands
1 Posts

Posted - 08/26/2014 :  03:54:30  Show Profile  Reply with Quote
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

USA
36895 Posts

Posted - 08/26/2014 :  12:23:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Starting Member

United Kingdom
43 Posts

Posted - 09/01/2014 :  10:11:35  Show Profile  Reply with Quote
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

USA
36895 Posts

Posted - 09/02/2014 :  14:56:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 09/02/2014 14:57:05
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000