INF: How to Manually Remove Replication from SQL Server 6.x (170481)

MORE INFORMATION

Below are the steps to manually remove (uninstall) replication are described in detail. See the SQL Server Books Online for more information on the commands and stored procedures discussed below.

Step 1 - Drop All Subscriptions

You can run sp_helpsubscription in the published database to determine whether or not there are any subscriptions.

Run the command below to drop all subscriptions to all publications within a published database. This command must be run in each database that is published and has subscriptions. Running this command also deletes the distribution tasks associated with the subscriptions that are dropped.

   sp_dropsubscription 'all', 'all', 'all'
				

Note that sp_dropsubscriptions will only work correctly if both of the following conditions are true:

• There is a distribution database installed.
  
  -and-
• The publishing server has correct remote server information for all subscribing servers. Remote server information is defined automatically when you configure replication. You can verify the remote server information by running sp_helpserver.

Step 2 - Drop All Articles and Publications

You can run sp_helppublication in the published database to determine whether or not there are any publications.

You can run "select * from sysarticles" in the published database to determine whether or not there are any articles.

Run the command below to drop all articles and subscriptions within a published database. This command must be run in each database that is published and has publications. Running this command will also delete the synchronization task associated with the publications that are dropped.

   sp_droppublication 'all'
				

Step 3 - Clear the Transaction Log of Any "Undistributed" Replicated

Transactions

You will not be able to truncate the transaction log as long as there are any replicated transactions that have not been distributed. An "undistributed" replicated transaction is a transaction that has been marked for replication in the transaction log of the published database, but has not been "distributed" by the log reader task.

You can see if there are any "undistributed" replicated transactions in a published database by running the following command:

   dbcc opentran(<published_database_name>) with tableresults
				

If the database has at some point had replicated transactions, the above command will return the oldest distributed row ID ("REPL_OLD_DIST_RID") and the oldest non-distributed row ID ("REPL_OLD_NONDIST_RID"). If the database has never had any replicated transactions and there are no open transactions, the above command will return 0 rows.

If the above command does return the oldest distributed and oldest non- distributed row IDs, and if these row IDs are not the same, you have one or more undistributed replicated transactions in that database. If the row IDs are the same, you do not have any undistributed replicated transactions in the database. For more information, see the DBCC statement in the Transact- SQL Reference book.

If you have undistributed replicated transactions, run the following command to mark all replicated transactions as "distributed" so that you can truncate the log:

   sp_repldone 0, 0, null, 0, 0, 1
				

The sp_repldone command must be run in each database that has undistributed replicated transactions.

The sp_repldone command can only be run if the database is marked as published. If necessary, you can temporarily mark a database as published by running the following command:

   sp_dboption <database_name>, pub, true
				

After running sp_repldone, you can mark the database as not published by running the following command:

   sp_dboption <database_name>, pub, false
				

You should contact your primary support provider if you continue to see replicated transactions in the transaction log after completing the procedures in this article.

Step 4 - Remove the Database's "Published" and "Subscribed" Status

Run sp_helpdb to verify which databases have a status of "published" or "subscribed."

Run the following command to remove the "published" status from a database. Running this command also deletes the log reader task for that database.

   sp_dboption <database_name>, published, false
				

Run the following command to remove the "subscribed" status from a database:

   sp_dboption <database_name>, subscribed, false
				

Step 5 - Delete Unneeded Remote Servers and Remote Logins

Replication automatically defines remote servers and remote logins when you install replication. The remote servers and remote logins are necessary to run remote procedure calls. You may delete the remote servers and remote logins if they are not required for other SQL Server applications.

NOTE: You should not drop the local server.

The local server is the one that has server ID 0 when you run sp_helpserver. For more information on remote servers and remote users, see Chapter 10 in the SQL Server Administrator's Companion.

You can run "sp_helpserver" to see a list of remote servers. You can run "sp_helpremotelogin" to see a list of the remote logins. Run the following command to drop a remote login:

   sp_dropremotelogin remoteserver_name [, loginame [, remotename]]
				

Run the following command to drop a remote server: NOTE: Do not drop the local server. The local server has server ID 0.

   sp_dropserver server_name [, droplogins]
				

Step 6 - Remove a Server's Publisher, Subscriber, and Distributor Status

Run sp_helpserver to verify the status of the servers. Then run the following command to remove a server's publisher ("pub") status:

   sp_serveroption <server_name>, pub, false
				

Run the following command to remove a server's remote publisher ("dpub") status:

   sp_serveroption <server_name>, dpub, false
				

Run the following command to remove a server's subscriber ("sub") status:

   sp_serveroption <server_name>, sub, false
				

Run the following command to remove a server's distributor ("dist") status:

   sp_serveroption <server_name>, dist, false
				

Step 7 - Delete Any Remaining Replication Tasks

Open the Manage Scheduled Tasks window in SQL Enterprise Manager by double-clicking the SQL Executive icon. Delete any tasks that have a type of "Logreader," "Distribution," or "Sync." Also delete any replication cleanup tasks. Cleanup tasks have a name in the following format:

   <publisher_server_name>_<subscriber_server_name>_cleanup.
				

Step 8 - Delete the .Sch and .Tmp files in the Repldata Directory

Delete the .sch (published table schema script) and .tmp (synchronization data) files in the MSSQL\Repldata directory on the distribution server.

Step 9 - Drop the Distribution Database

Run the following command to drop the distribution database:

   drop database distribution
				

After dropping the distribution database, you will need to manually delete the data and log device files for the distribution database; this requires stopping and restarting the SQL Server.

Step 10 - Reset the MSlast_job_info Table

The MSlast_job_info table exists in each subscribing database and keeps track of the last job that was replicated to that database from each publishing database. The MSlast_job_info table is created by the distribution task after you subscribe to a publication.

If a subscribing database is not currently subscribed to any publishing databases, you can simply use the following command to drop the MSlast_job_info table in each subscribing database:

   drop table MSlast_job_info
				

If the subscribing database is still subscribed to one or more publishing databases, you should use the following command to delete the rows in the MSlast_job_info table that correspond to the database(s) to which it is no longer subscribed:

   delete MSlast_job_info
   where publisher = '<publishing_server_name>'
   and publisher_db = '<publishing_database_name>'
				

Step 11 - Verify the Replication Registry Entry

Before you can reinstall replication, the "DistributionDB" value under the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Repliction registry key must have a null string.

You can run the following command from the Master database to make the string null:

   xp_regwrite 'HKEY_LOCAL_MACHINE',
   'SOFTWARE\Microsoft\MSSQLServer\Replication', 'DistributionDB',
   'REG_SZ', ''
				

WARNING: Using Registry Editor incorrectly can cause serious problems that may require you to reinstall SQL Server. Microsoft cannot guarantee that problems resulting from the incorrect use of Registry Editor can be solved. Use Registry Editor at your own risk.

It is also possible to change this entry with Registry Editor (Regedt32 in Windows NT 3.50 and Windows NT 3.51, or Regedit in Windows NT 4.0 and Windows 95). If you do use the Registry Editor, do not delete the "DistributionDB" entry itself; rather, double-click the DistributionDB entry and delete the string in the Edit dialog box.