Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 database marked as suspect

Author  Topic 

venkath
Posting Yak Master

202 Posts

Posted - 2006-07-07 : 03:43:38
Hi all

I tried this command

DBCC CHECKDB (A2PD,REPAIR_ALLOW_DATA_LOSS)
The above command was not executed as there was some long rollback was going from a user...

I stopped and re started the MSSQLSERVR service...

Then i found the follwing error..

how can i work with the database A2PD marked as suspect


Server: Msg 926, Level 14, State 1, Line 1
Database 'A2PD' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.


Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-07 : 04:05:10
Duplicate of http://sqlteam.com/forums/topic.asp?TOPIC_ID=68757

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-07 : 16:28:13
Why on earth did you restart SQL Server? What did you think that would achieve?

Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-07 : 16:59:02
is this the same database with the corruption issue you posted about yesterday?

If so, that database has corruption that DBCC CHECKDB cannot fix even with the REPAIR_ALLOW_DATA_LOSS argument. Your only chance for recovery is to restore a valid backup, or hire a data recovery service to salvage your data (read Paul's reply to your original thread). You are completely SOL on this one.

Stopping the server while rollback is occuring is a pretty bad move btw. YOu might want to note that as something never to do in the future.


-ec

Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-07 : 17:03:40
see
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jocampo
Starting Member

48 Posts

Posted - 2006-07-12 : 13:32:52
Very interesting article ... but i have a question ...

How do i Get the server to retry recovery? ...

Thanks in advanced,

JC


quote:
Originally posted by nr

see
http://www.nigelrivett.net/SQLAdmin/RecoverCorruptDatabase.html


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 13:43:00
The status change below that statement does it.
If the database is marked as suspect it won't try to recover. You can reset the status and the server will retry but unless it was a transient error it will be suspect again.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2006-07-12 : 14:07:42
I'd like to stress again that using backups is far better than manually recovering a corrupt database. For SQL Server 2005, there's the added option of using Emergency Mode Repair if you don't have backups and can't afford the downtime required to migrate to a new database. See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx


Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
Go to Top of Page

jocampo
Starting Member

48 Posts

Posted - 2006-07-12 : 15:30:52
100% Agree!!!!

I work for a big production company at Puerto Rico and we do not rely on repaired suspect databases; this should be the last resource to use... when you have no more bullets in your magazine. Instead, we have a very well configured maintenance plans (i'm in charge of this) which run daily on each server (around 80 SQL servers). They all run full backups. We configured Jobs to send the file using the network to a main SQL backup repository. We then backup to tape.... the native SQL backups.

In real world, like production companies, we must be sure that the data we are working with is 100% acurate.

My 2 cents.

quote:
Originally posted by paulrandal

I'd like to stress again that using backups is far better than manually recovering a corrupt database. For SQL Server 2005, there's the added option of using Emergency Mode Repair if you don't have backups and can't afford the downtime required to migrate to a new database. See http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/18/636105.aspx


Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine + SQL Express
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Go to Top of Page

tfakih
Starting Member

1 Post

Posted - 2006-07-27 : 05:41:46
Dear jocampo
Can u please tell ...How to configure Jobs to send the file using the network to a main SQL backup repository?
It will help me a lot to schedule maintenance plan.
Thanks in advance
TFakih
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 06:11:18
Take the backup to a local drive.
Consider the transfer of that file as a separate task.

How that is implemented depends on your environment. A file copy would probably suffice if you have access to the destination.
So just code in a job a step to do a backup followed by a step to do a copy.
Also condier automatically restoring the backup at the destination and performing dbcc checks on it. For important systems I do that with every backup.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -