June 17, 2013 at 1:57 am
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.
June 17, 2013 at 2:40 am
Could the issue be that it's on 2005 standard edition? Apparently snapshots are only available from 2005 enterprise onwards?
June 17, 2013 at 2:57 am
Doubt that would be the problem.
Do you have any 3rd party I/O drivers, like a defrag tool or software-level encryption?
June 17, 2013 at 3:07 am
How would I know/check? sys.certificates returns 0 rows.
June 17, 2013 at 3:12 am
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:
June 17, 2013 at 3:54 am
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?
June 17, 2013 at 4:01 am
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.
June 17, 2013 at 4:11 am
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 =(
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply