Friday, February 03, 2006

distribution database corrupted

If you have got a corrupt distribution database and unable to remove or configure your publication then follow the following steps.

1. SELECT * from msdb..msdistributiondbs
2. If a row is returned delete it.
3. Then, reconfigure replication.

What if the above steps didn't work, the one option I think of is to remove the replication from the server. If you have any other way , please let me know.

use master
go
sp_configure 'allow update', 1
go
reconfigure with override
go
DELETE master..sysservers WHERE srvname = 'repl_distributor'

go
sp_configure 'allow update', 0
go
reconfigure with override
go

2 comments:

Unknown said...

Thanks for this article! I was not able to use any option of replication anymore.

After following the first three steps I was able to use the 'Configure Distribution' option again. However I did needed to remove my distribution database manually.

Use master
Alter DATABASE distributionDBname SET OFFLINE
DROP Database distributionDBname

Remember to first 'Disable Publishing and Distribution' otherwise you will be in a lot of trouble.

Unknown said...

It is nice to hear that your problem is now solved.

Regards

Tesh