| Author |
Topic |
|
venkath
Posting Yak Master
202 Posts |
 Posted - 2006-07-07 : 03:43:38
|
| Hi all I tried this commandDBCC 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 suspectServer: Msg 926, Level 14, State 1, Line 1Database '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 |
|
|
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 RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
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 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
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,JCquote:
Originally posted by nr
seehttp://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.
|
|
|
|
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. |
|
|
|
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.aspxPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
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.aspxPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine + SQL Express(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)
|
|
|
|
tfakih
Starting Member
1 Post |
Posted - 2006-07-27 : 05:41:46
|
| Dear jocampoCan 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 advanceTFakih |
|
|
|
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. |
|
|
|
|
database marked as suspect