configure distribution wizard does not appear while trying to configure Distribution in SQL 2005

Recently we encountered a case wherein distribution wizard was not appearing when we try to configure distribution server using sql server 2005 management studio.

We do not see any error message in the errorlog or application event log while the cursor turns to hourglass for few seconds and disappears.


We later found that you can observe this issue when you restore Distribution database as a User Database.

The Distributor is a server that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication. To set up replication, you must configure a Distributor. Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.



In order to resolve the issue we need to delete the Distribution database since Distribution database is one of the system databases which gets created while configuring the Distributor server and could not be restored as a User Database



1. Configure a Distributor (This will create a distribution database (System database))
2. Make a backup of the distribution database
3. Disable the Distribution
4. Drop the distribution database
5. Restore the Distribution database
6. Now in the Management Studio right click the Replication folder and select Configure Distribution
7. Now you will see the hourglass and it will disappear in few seconds


If you run profiler you will see "Select * from sysdatabases where name =’distribution’ running in a loop
It might even slowdown the system. To stop this from happening Close the SQL Management Studio and reopen it.

The above behavior is changed in Katmai in which instead of using the existing restored distribution database, UI will automatically generate a new distribution1 as dist db name.

Levi Justus
Technical Lead, Microsoft Sql Server.


Comments (3)

  1. Cristian says:

    Somebody knows how to do this with SQL Server 2000?

  2. Levi says:

    Hi Cristian.

    In SQL Server 2000 SP4, we do not have this issue. It has the same fix thats made in Katmai. Somehow this was missed in only SQL 2005

    The UI will automatically generate distribution1 database if a Distribution database already exist

    The issue on your side might be different. Please open a ticket with the PSS and they should be able to troubleshoot further