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

Corrupt Database Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 3:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 5:34 AM
Points: 3, Visits: 7
Hi All,

Having an issue with a corrupt SQL 2005 database. Event log is giving a lot of I/O errors. Running the following command:

DBCC CHECKDB('database') WITH TABLERESULTS, NO_INFOMSGS, ALL_ERRORMSGS

Gives the following output:

CREATE FILE encountered operating system error 665(error not found) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database.mdf:MSSQL_DBCC15'.
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 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:4033984; actual 0:0). It occurred during a read of page (1:4033984) in database ID 7 at offset 0x000007b1b80000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.

Any help on the next steps would be appreciated.
Post #1487128
Posted Thursday, August 22, 2013 3:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 40,427, Visits: 36,877
First problem there, the database snapshot for CheckDB can't be created. Is that an NTFS drive, does the SQL Server service account have full permissions to the folder with the data files?
Is it Enterprise edition?
Are you running any disk-related filter drives (eg DiskKeeper, etc)

Second problem is the corruption, but need to see a full list to help there. Can you take the DB into single user mode and run the following?
DBCC CheckDB (<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS, TABLOCK




Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1487143
Posted Thursday, August 22, 2013 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 5:34 AM
Points: 3, Visits: 7
Yes the drive is an NTFS drive.
SQL Server service account has full permissions to the folder with the data files.
It's Standard edition.

Have run the query and received the following output:

Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:4033984; actual 0:0). It occurred during a read of page (1:4033984) in database ID 7 at offset 0x000007b1b80000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\database.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe 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.
Post #1487158
Posted Thursday, August 22, 2013 4:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:55 AM
Points: 40,427, Visits: 36,877
Restore from a clean backup. That's not repairable.

If you have an unbroken log chain from the clean backup then you won't lose any data (you can take a tail-log backup before starting the restore)

Edit: Oh, and have a very good look at that IO subsystem. Databases don't corrupt themselves, that looks like a section of the disk has been zeroed out. Misbehaving IO subsystem.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1487169
Posted Thursday, August 22, 2013 5:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 22, 2013 5:34 AM
Points: 3, Visits: 7
Thanks for the confirmation. Will go ahead with the restore.
Post #1487176
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse