SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Status SUSPECT


Database Status SUSPECT

Author
Message
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 977
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.
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3803 Visits: 1717
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
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 977
Thanks Paul.

Regards,
JMSM
faheemlatif
faheemlatif
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 128
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.
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3803 Visits: 1717
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
Adam Bean
Adam Bean
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2560 Visits: 2192
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!

---
SQLSlayer
Making SQL do what we want it to do.
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3803 Visits: 1717
Smile 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
Adam Bean
Adam Bean
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2560 Visits: 2192
Paul Randal (3/6/2008)
Smile 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.

---
SQLSlayer
Making SQL do what we want it to do.
DINESH-162072
DINESH-162072
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 3
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
JMSM
JMSM
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 977
Thanks a lot for your answer, in the future i'll consider this post.
Regards,
JMSM
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search