Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CHECKDB issue


CHECKDB issue

Author
Message
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
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
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
Could the issue be that it's on 2005 standard edition? Apparently snapshots are only available from 2005 enterprise onwards?


Dird
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
Doubt that would be the problem.

Do you have any 3rd party I/O drivers, like a defrag tool or software-level encryption?
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
How would I know/check? sys.certificates returns 0 rows.


Dird
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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/
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771
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
HowardW
HowardW
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 9892
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.
Dird
Dird
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 771


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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search