HELP - Distribution DB is Suspect

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

SQL Server 2000 Forums

SQL Server Administration (2000)

HELP - Distribution DB is Suspect

Author

Topic

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-11-11 : 08:44:04

The night operators powered down an external disk pack while SQL Server was still running causing the Distribution db to be marked as suspect. I ran sp_resetstatus "distribution" and received the following message.

Prior to updating sysdatabases entry for database 'distribution', mode = 0 and status = 272 (status suspect_bit = 256).
For row in sysdatabases for database 'distribution', the status bit 256 was forced off and mode was forced to 0.
Warning: You must recover this database prior to access.

Is this normal? What needs to be recovered? The database is no longer marked as suspect.

Thanks, Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 12:08:56

Run DBCC CHECKDB to verify that it is in a consistent state. Are you able to access the database?

Tara

DBADave
Constraint Violating Yak Guru

366 Posts

Posted - 2003-11-11 : 12:15:24

Hi Tara,

Here is what I did.

1 - Restarted SQL Server services
2 - Ran sp_resetstatus "distribution' and saw message listed in my original post
3 - Found that no agents existed for published database
3 - Restored distribution
4 - Disabled replication jobs
5 - Determined exact time of failure (10:35pm)
6 - Determined last successful Distribution (10:00pm)
7 - Determined that no data should have been changed in published db since 10:00pm
8 - Started replication jobs
9 - Saw message from Log Reader and Distribution Agents indicating the following:
2 Transaction(s) with 10046 command(s) were delivered
10 - Ran CheckDB - No errors detected
11 - Checked Error Log - No errors detected

This appears to have fixed the problem.

I don't know if "best practice" dictates I should restore distribution, publication and subscription dbs or just distribution. Any thoughts?

Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-11 : 12:17:56

Not sure about that. But I would think that distribution being restored is all that you need. You might want to check some datetime columns between the publication and subscription databases to see if anything is missing. You might consider just redoing replication just in case. It's fairly easy if you generate the script.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources