June 8, 2009 at 11:44 am
Hi all,
I have a non-production database that isn't normally supported by my team, but we were reached out to to try and help. The DB was in suspect mode when I found it.
After reviewing this board (http://www.sqlservercentral.com/Forums/Topic399609-266-1.aspx), I ran these commands:
alter database PhilTIM set online
ALTER DATABASE PhilTIM SET EMERGENCY
alter database PhilTIM set single_user
dbcc checkdb ('PhilTIM', REPAIR_ALLOW_DATA_LOSS)
The results that come back are this:
DBCC results for 'PhilTIM'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'PhilTIM'.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1420368; actual 0:0). It occurred during a read of page (1:1420368) in database ID 11 at offset 0x000002b58a0000 in file 'd:\MSSQL.1\MSSQL\PhilTIM.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.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1420368; actual 0:0). It occurred during a read of page (1:1420368) in database ID 11 at offset 0x000002b58a0000 in file 'd:\MSSQL.1\MSSQL\PhilTIM.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.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1420368; actual 0:0). It occurred during a read of page (1:1420368) in database ID 11 at offset 0x000002b58a0000 in file 'd:\MSSQL.1\MSSQL\PhilTIM.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.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
Fortunately and unfortunately, this is my first go-round with a currupt/suspect database. Paul's links and references have been great thus far, but I'm kind of stuck.
Trying to use this Db as the context and running any sort of queries results in this message:
Could not continue scan with NOLOCK due to data movement.
How would I be able to review the data on those pages, or correct these remaining errors?
Any help would be appreciated.
Since this is not production, I'm using this as a learning experience for the future.
Thanks!
Steve
June 8, 2009 at 12:09 pm
SK (6/8/2009)
Fortunately and unfortunately, this is my first go-round with a currupt/suspect database. Paul's links and references have been great thus far, but I'm kind of stuck.
Emergency mode repair is already the last resort. If it failed, then there's few options remaining except what the message said.
Msg 7909, Level 20, State 1, Line 1
The emergency-mode repair failed.You must restore from backup.
Trying to use this Db as the context and running any sort of queries results in this message:
Could not continue scan with NOLOCK due to data movement.
Any hardware errors? Specifically IO subsystem?
Since this is not production, I'm using this as a learning experience for the future.
In that case, just be aware that Emergency mode repair is a last resort. If you have backups rather restore from backup
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
June 8, 2009 at 1:42 pm
Thanks Gail. That's what I thought. I will have our sys admins look into the storage on the machine. Weird that its only this one DB out of 8 that are all on the same drive. This wasn't under our watchful eyes and like most that aren't, no backups exist 😀
Thanks for the reply.
Have a good one!
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply