INF: Replication: How to Rebuild the Distribution Database
ID: Q190797
|
The information in this article applies to:
-
Microsoft SQL Server version 6.5
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:
- Stop SQL Server.
- Rename or delete the distribution .dat files.
- Restart SQL Server. At this point, the distribution database will be
marked suspect.
- 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.
Additional query words:
rep repl dist db deleted SEM
Keywords : SSrvRep
Version : WINNT:6.5
Platform : winnt
Issue type : kbinfo
|