Loading / Suspect issue! - SQL Server Forums

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

Loading / Suspect issue!

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.)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources