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.
| Author |
Topic |
|
Gopher
Yak Posting Veteran
83 Posts |
 Posted - 2006-03-23 : 05:24:54
|
Hi All
Currently I have a SQL 2000 db which is replicated via log shipping to another server and occasionally I have had the database go into suspect mode when the logs have been applying. This is obviously something I want to avoid or solve if it happens again.
I have looked on books online and have found info on sp_resetstatus however I want to be able to add the 'with no recovery' once the suspect status has been changed. Is this the right way to do this or is there a better way?
Many Thanks
Gopher |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-03-23 : 15:56:35
|
What do you mean by 'with no recovery'? sp_resetstatus doesn't have that option.
What you need to do is work out why the database is going suspect - most likely because the database on the subcriber is corrupt and the log application is hitting a corruption. What's the output of
DBCC CHECKDB ('yourdb') WITH NO_INFOMSGS, ALL_ERRORMSGS
Thanks
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.) |
 |
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2006-03-24 : 03:34:47
|
Thanks for your response
When I recover our database I restore the last full backup and then the remaing logs. While restoring the logs I use the NO_RECOVERY option so that I can restore more logs.
I just wan something to do which will check the DB and remove the suspect status if this is possible.
Does that help? |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2006-03-24 : 14:18:43
|
Kind of - it explains what you meant.
As I said earlier, you need to work out why the database is going suspect. What messages do you receive when the database goes suspect? What's the output of the CHECKDB I asked you for?
Thanks
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.) |
|
|
|
|
|
|
Loading / Suspect issue!