<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Data Corruption
»
Database Status SUSPECT
24 posts, Page 1 of 3
1
2
3
»
»»
Database Status SUSPECT
Rate Topic
Display Mode
Topic Options
Author
Message
JMSM
JMSM
Posted Friday, February 08, 2008 8:21 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 185,
Visits: 751
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.
Post #453275
Paul Randal
Paul Randal
Posted Friday, February 08, 2008 8:31 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905,
Visits: 1,601
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
Post #453288
JMSM
JMSM
Posted Friday, February 08, 2008 9:49 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 185,
Visits: 751
Thanks Paul.
Regards,
JMSM
Post #453335
faheemlatif
faheemlatif
Posted Tuesday, February 26, 2008 4:37 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, August 06, 2008 5:34 AM
Points: 22,
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.
Post #460143
Paul Randal
Paul Randal
Posted Tuesday, February 26, 2008 10:58 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905,
Visits: 1,601
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
Post #460401
Adam Bean
Adam Bean
Posted Wednesday, March 05, 2008 2:16 PM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:18 PM
Points: 1,103,
Visits: 1,781
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!
--
Post #464748
Paul Randal
Paul Randal
Posted Thursday, March 06, 2008 9:32 AM
SSCommitted
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905,
Visits: 1,601
:) 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
Post #465284
Adam Bean
Adam Bean
Posted Thursday, March 06, 2008 9:36 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 23, 2013 12:18 PM
Points: 1,103,
Visits: 1,781
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.
--
Post #465289
DINESH-162072
DINESH-162072
Posted Tuesday, March 11, 2008 4:45 AM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, March 19, 2008 6:23 AM
Points: 1,
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
Post #467265
JMSM
JMSM
Posted Tuesday, March 11, 2008 5:48 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 185,
Visits: 751
Thanks a lot for your answer, in the future i'll consider this post.
Regards,
JMSM
Post #467291
« Prev Topic
|
Next Topic »
24 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
