INF: Replication: How to Rebuild the Distribution Database (190797)

SUMMARY

In some cases, you may need to rebuild the distribution database. This may be the case if any of the following conditions is true:

There is some data inconsistency in the distribution database.
The distribution database is otherwise inaccessible (for example, the user has accidentally deleted the distribution database).
Other servers in the replication topology are not accessible (for example, the distribution database has numerous commands for multiple subscribers that are no longer accessible).

MORE INFORMATION

To drop and then re-create a distribution database, perform the following steps:

If possible, use the bulk copy program (BCP) to bulk copy the data from the MSjobs, MSjob_commands, and MSsubscriber_jobs tables. These tables contain information related to replicated transactions. If you are not able to use BCP to move data out of these tables for some reason, you will need to resynchronize all subscribers for all publications after you have re-created the distribution database. Next, use BCP to bulk copy the data in the MSsubscriber_info, MSjob_subscriptions and MSsubscriber_status tables. Information is entered into the MSsubscriber_info table when you configure publishing with the Publishing command under Replication Configuration on the Server menu in SQL Enterprise Manager.

If you cannot use BCP to move the data in the MSsubscriber_info table, you will need to reset and reconfigure the publisher by clicking to clear the check boxes in this window and then clicking to check them again after you rebuild the distribution database.
Either use sp_renamedb to rename the distribution database, or drop the distribution database.

NOTE: In some situations, you may not be able to easily drop the distribution database. For example, there may be a long-running process or a long rollback executing in the distribution database. In those cases, you can use the following steps to drop the distribution database without having to wait for the long running-process or rollback to complete:
1. Stop SQL Server.
2. Rename or delete the distribution .dat files.
3. Restart SQL Server. At this point, the distribution database will be marked suspect.
4. Use the DBCC DBREPAIR statement to drop the distribution database.
Create a new database named "distribution".
Run the Instdist.sql script in the new distribution database. Doing this creates the tables and stored procedures in the new distribution database.
Use BCP to bulk copy the data back into the MSsubscriber_info, MSjobs, MSjob_commands, and MSsubscriber_jobs tables.

At this point, your replication tasks should be able to continue normally.

If you are unable to BCP out the tables, it may be faster to uninstall publishing and do a fresh installation of replication. Note that the distribution database is the heart of the replication technology and you cannot replicate with a unusable distribution database.