February 8, 2008 at 8:21 am
Hello,
Can anyone help me?
I've got one database in my development environment that after machine crashes when SQL Server starts this database stays in SUSPECT status.
I send you the errorlog with errors, can you tell me how can i recover my database with or without data loss.
Thanks and Regards,
JMSM
2008-02-08 13:09:58.23 spid19s Error: 824, Severity: 24, State: 2.
2008-02-08 13:09:58.23 spid19s SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x50d7a8c4; actual: 0xd5a37a53). It occurred during a read of page (1:1678481) in database ID 5 at offset 0x00000333922000 in file 'D:\SQL_DATA\PTC_QS.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.
2008-02-08 13:09:58.31 spid19s Error: 3313, Severity: 21, State: 2.
2008-02-08 13:09:58.31 spid19s During redoing of a logged operation in database 'PTC_QS', an error occurred at log record ID (50862:16816:84). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
2008-02-08 13:09:58.32 spid19s Error: 3414, Severity: 21, State: 1.
2008-02-08 13:09:58.32 spid19s An error occurred during recovery, preventing the database 'PTC_QS' (database ID 5) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
February 8, 2008 at 8:31 am
The IO subsystem corrupted your database when the machine crashed. Error 824 is that a page checksum failure occured - a page's contents were altered AFTER it had been written out by SQL Server. Could be a torn page (partial write when the machine crashed) or something more insidious in the IO subsystem.
So - your database is corrupt and recovery cannot complete. This means the database is transactionally (and possibly structurally inconsistent) and so is in the suspect state (which means the recovery has started but not completed). You have two options: restore from your backups, or put the database into emergency mode and run emergency mode repair. Take a look at my blog in the Disaster Recovery category for guidance about emergency mode repair and the effect it will have on your database. I recommend the backup route if you have it available to you.
You should also figure out why the IO subsystem corrupted the page - just to make sure that's what you'd possibly expect in a machine crash situation on this machine.
Hope this helps
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
February 8, 2008 at 9:49 am
Thanks Paul.
Regards,
JMSM
February 26, 2008 at 4:37 am
Hi,
Please visit this :
http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-suspect-database/
Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.
February 26, 2008 at 10:58 am
faheemlatif (2/26/2008)
Hi,Please visit this :
http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-suspect-database/
That would be a bit useful if they were on SS2000, but they're not. Please check which forum you're in before posting inappropriate links - thanks!
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 5, 2008 at 2:16 pm
Paul Randal (2/26/2008)
faheemlatif (2/26/2008)
Hi,Please visit this :
http://www.simple-talk.com/sql/backup-and-recovery/pop-rivett-and-the-suspect-database/
That would be a bit useful if they were on SS2000, but they're not. Please check which forum you're in before posting inappropriate links - thanks!
Ouch, harsh ... if you scroll down, there is a link to the msdn BOL of ALTER database, which provides the syntax for putting the database into emergency mode (ALTER DATABASE mydb SET EMERGENCY). So the link is somewhat appropriate.
Also, great explanation of the problem though!
March 6, 2008 at 9:32 am
🙂 I tend to get a little riled when people post links that aren't terribly useful or pertinent. Many people (I'm not saying it's the case this time) just post on the end of threads to get their stats up - same on the MSDN forums.
Nice .sig by the way.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
March 6, 2008 at 9:36 am
Paul Randal (3/6/2008)
🙂 I tend to get a little riled when people post links that aren't terribly useful or pertinent. Many people (I'm not saying it's the case this time) just post on the end of threads to get their stats up - same on the MSDN forums.Nice .sig by the way.
No worries, completely understand ...
haha, thanks.
March 11, 2008 at 4:45 am
to recover data from suspected database do the following
1. open master database
2. open table sysdatabases
3. change the 'status' value of ur database to 32768
4. refresh the sql server
5. now the suspect mode changed to emergency mode
6. create a new database using same script of curepted database
7. using import data facility transfer all data to new db
8. detach the currupted db and attach new db
March 11, 2008 at 5:48 am
Thanks a lot for your answer, in the future i'll consider this post.
Regards,
JMSM
March 11, 2008 at 8:36 am
DINESH (3/11/2008)
to recover data from suspected database do the following1. open master database
2. open table sysdatabases
3. change the 'status' value of ur database to 32768
4. refresh the sql server
5. now the suspect mode changed to emergency mode
6. create a new database using same script of curepted database
7. using import data facility transfer all data to new db
8. detach the currupted db and attach new db
Hi Dinesh - please check the forum you're posting in - this is a 2005 forum so your procedure above for SQL 2000 doesn't work on 2005. Replace steps 1-5 with ALTER DATABASE yourdb SET EMERGENCY.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 10, 2008 at 2:47 am
Ouch, your second harsh post on this topic. While it is true, proper directions are far more relevant than exact steps. If you always provide exact steps to solve the problem, your trainees may stop using their brain and get used to always get exact steps. 😛 Many people work with both ss2k and ss2005, so a forum like this is not necessarily strict ss2005.
April 10, 2008 at 3:12 am
Robert (4/10/2008)
Many people work with both ss2k and ss2005, so a forum like this is not necessarily strict ss2005.
There's a SQL 2000 data corruption forum as well here. Posting 2000-specific solutions in a 2005 forum can cause confusion when someone looking for a solution tries it and it doesn't work.
When it's a case that people post solutions in the 2005 forums using deprecated features, that's one thing, but Dinesh's solution would not work at all on SQL 2005 due to the changes in the system tables and the fact that they are no longer updateable.
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
April 10, 2008 at 3:38 am
Of course. I stated that what Paul wrote is correct, just that there's no need to be harsh. Most of things from ss2k work fine on ss2005, though some should be replaced, some (like this) does not work. But this is not a reason to drive away all ss2k users/developers/dbas.
Besides, if you work with both, you may make a mistake and post the syntax/steps for the other.
Another thing: it was asked in ss2005 forum, so one can't reply in ss2000 forum, right? The poster made a mistake of posting ss2k steps, either inadvertently or because he doesn't work with ss2005, is it the reason to push him away? I think not.
April 10, 2008 at 4:21 am
Personally I don't see anything harsh in what he said. If the comment had been something like "You idiot, you're posting in the wrong #$%#$@ forum", then it would be another matter.
*shrug* Maybe I'm less sensitive to harsh comments than others
Another thing: it was asked in ss2005 forum, so one can't reply in ss2000 forum, right?
No, but neither should one post a 2000-related answer to a 2005-related question, nor a 2005-related answer to a 2000-related question.
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply