Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CHECKDB issue Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 1:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
Hi all,

Someone restarted a server last week & SQL Server came back up as "in recovery" then completed after a while. Today I've just tried to run "DBCC CHECKDB WITH PHYSICAL_ONLY" on 2005 SE sp3. The output I get is as follows:
Msg 926, Level 21, State 6, Line 1
Database 'mdb' 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.

My session is then disconnected from the database. It says suspect (although it later says to discard this) but when I reconnect and look at 'IsSuspect' it says it isn't (0). Any idea why this would be the case?

Error log says:
Date,Source,Severity,Message
06/17/2013 08:45:58,spid123,Unknown,Database 'mdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
06/17/2013 08:45:58,spid123,Unknown,Error: 926<c/> Severity: 21<c/> State: 6.
06/17/2013 08:45:58,spid80,Unknown,E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6: Operating system error 1784(error not found) encountered.
06/17/2013 08:45:58,spid80,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.
06/17/2013 08:45:58,spid80,Unknown,The operating system returned error 1784(error not found) to SQL Server during a write at offset 0x0000006430e000 in file 'E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6'. 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<c/> see SQL Server Books Online.
06/17/2013 08:45:58,spid123,Unknown,DBCC CHECKDB (mdb) WITH no_infomsgs<c/> physical_only executed by GBAHES777\GBAHES777 found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds.
06/17/2013 08:45:58,spid80,Unknown,E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6: Operating system error 1784(error not found) encountered.
06/17/2013 08:45:58,spid80,Unknown,Error: 17053<c/> Severity: 16<c/> State: 1.
06/17/2013 08:45:58,spid80,Unknown,The operating system returned error 1784(error not found) to SQL Server during a write at offset 0x0000034f840000 in file 'E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6'. 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<c/> see SQL Server Books Online.

Sometimes I also get this error:
The operating system returned error 1784(error not found) to SQL Server during a write at offset 0000000000000000 in file 'E:\Application-data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mdb.mdf:MSSQL_DBCC6'. 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.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
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.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.




Dird // Junior DBA
11g OCA
10.5 newbie
Post #1464019
Posted Monday, June 17, 2013 2:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
Could the issue be that it's on 2005 standard edition? Apparently snapshots are only available from 2005 enterprise onwards?


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1464029
Posted Monday, June 17, 2013 2:57 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 1,247, Visits: 9,879
Doubt that would be the problem.

Do you have any 3rd party I/O drivers, like a defrag tool or software-level encryption?
Post #1464034
Posted Monday, June 17, 2013 3:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
How would I know/check? sys.certificates returns 0 rows.


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1464036
Posted Monday, June 17, 2013 3:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 1,247, Visits: 9,879
Not SQL Server Encryption, 3rd party encryption on the O/S (e.g. TrueCrypt etc.). Or a new install/upgrade of a defrag tool like DiskKeeper. This is likely something at the OS/Storage level that's preventing a snapshot from being created (this is NTFS functionality).

Article from Paul Randal here:

http://www.sqlskills.com/blogs/paul/search-engine-qa-14-beware-3rd-party-file-system-drivers-with-dbcc-checkdb/
Post #1464037
Posted Monday, June 17, 2013 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
There is DiskKeeper on the server but I don't know if its classed as a "new" version since it's 2010? Apparently there's no encryption software.

I'll ask the Windows team about third party encryption. Thanks for the article although the solution isn't helpful in this case. But I'll keep a note of it in case the issue occurs again on a server where I'm able to manually create snapshots.

Would I need to tell them to disable diskkeeper for the duration of dbcc check?



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1464049
Posted Monday, June 17, 2013 4:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 1,247, Visits: 9,879
It'll be DiskKeeper:

http://www.sqlskills.com/blogs/paul/diskeeper-10-intelliwrite-corruption-bug/


Would I need to tell them to disable diskkeeper for the duration of dbcc check?


I would advise either upgrading to the latest version, or keep it off the server entirely, personally.
Post #1464052
Posted Monday, June 17, 2013 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650


Thanks for the link & help The Windows team will raise a change to upgrade it.

Although I'm a little disappointed about not being able to do my first real MSSQL restore =(



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1464056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse