| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-04 : 18:19:27
|
| Cross posted to Twitter #sqlhelpTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-03-19 : 19:35:46
|
| The hotfix did not resolve it for us. Same error.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 helpsRegardsShankyhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
CHECKDB Failing