August 22, 2013 at 3:22 am
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.
August 22, 2013 at 3:45 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2013 at 4:25 am
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.
August 22, 2013 at 4:44 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2013 at 5:02 am
Thanks for the confirmation. Will go ahead with the restore.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply