Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Database Status SUSPECT Expand / Collapse
Author
Message
Posted Friday, February 8, 2008 8:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
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
Posted Friday, February 8, 2008 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Posted Friday, February 8, 2008 9:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
Thanks Paul.

Regards,
JMSM
Post #453335
Posted Tuesday, February 26, 2008 4:37 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 6, 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
Posted Tuesday, February 26, 2008 10:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
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
Posted Wednesday, March 5, 2008 2:16 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.

Post #464748
Posted Thursday, March 6, 2008 9:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:05 AM
Points: 2,039, Visits: 1,665
:) 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
Posted Thursday, March 6, 2008 9:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 6:53 AM
Points: 1,145, Visits: 1,932
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.


---
SQLSlayer
Making SQL do what we want it to do.

Post #465289
Posted Tuesday, March 11, 2008 4:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, March 11, 2008 5:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:18 AM
Points: 186, Visits: 816
Thanks a lot for your answer, in the future i'll consider this post.
Regards,
JMSM
Post #467291
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse