distribution database SUSPECT - HELP! HELP!

We have SQL 2000 server replicate to another box.
I just found replication stopped! and distribution database was marked SUSPECT.
I am a SQL server newby, I checked sysdatabases table in master database and found
the status column is 280 and status2 is 1090519040.
Is there any way I can quickly recover this database and make replication moving?
Thanks in advance
David
David,
have a look in BOL for sp_resetstatus. Here is a brief synopsis:
"sp_resetstatus turns off the suspect flag on a database. This procedure
updates the mode and status columns of the named database in sysdatabases.
The SQL Server error log should be consulted and all problems resolved
before running this procedure. Stop and restart SQL Server after executing
sp_resetstatus.
A database can become suspect for several reasons. Possible causes include
denial of access to a database resource by the operating system, and the
unavailability or corruption of one or more database files."
Regards,
Paul Ibison
|||Thank you very much!
|||i m also in same case.
if u know pls let me know
- delwar
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Delwar,
the main reason for this is faulty hardware - drive array etc. Sp_resetstatus will help if the MDF or LDF file for the database is not available during startup. If there's still an issue, start the database in Emergency mode, Update the Status column in
master..sysdatabases table for that database to 32768. After this database will be usable with out transaction log so you can create a new database and use DTS to transfer objects and data (thanks to Hari Prasad for this). DBCC CHECKDB with REPAIR_REBUIL
D can be used if there is still a problem, and after that it's a PSS call.
HTH,
Paul Ibison

Distribution database of Transaction Replication publication being marked SUSPECT by recov

Hi experts there,
I have a Publication created for Transactional Replication. All the
while working fine.
Now it failed and I am not able to access to the database at all. It
shows the following error message:
Error 926: Database 'distribution' cannot be opened. It has been
marked SUSPECT by recovery. See the SQLServer error log for more
information.
Tried to detach the database but getting the following error: -
"The database cannot be detached while it is being replicated"
Tried running DBCC CheckDB but cannot run with database still in
suspect mode
Basically I cannot perform backup on it, cannot detach it, cannot even
drop the publication.
Tried also the following method:
1) sp_resetstatus DISTRIBUTION
Prior to updating sysdatabases entry for database 'DISTRIBUTION', mode
= 0 and status = 24 (status suspect_bit = 0).
No row in sysdatabases was updated because mode and status are already
correctly reset. No error and no changes made.
2) DBCC CHECKDB ('DISTRIBUTION', REPAIR_REBUILD) WITH ALL_ERRORMSGS
Server: Msg 926, Level 10, State 1, Line 1
Database 'distribution' cannot be opened. It has been marked SUSPECT
by recovery. See the SQL Server errorlog for more information.
Anybody know what cause all this and how to resolve it? Please
help!!!!!
I need to make the Replication running back soonest possible.
Thanks/TewI have seen this behavior once before. The only way we could get it out of
suspect mode was to directly update the sysdatabases table and set the
status to 32768 (emergency bypass). After recycling SQL Server we were then
able to run Checkdb. You might find corruption in the database. If not you
can set the status back to 0 and see if it will recover normally.
Rand
This posting is provided "as is" with no warranties and confers no rights.