How to remove Orphaned subscription

How to remove Orphaned subscription

TITLE: Microsoft SQL Server Management Studio

------------------------------

Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'. The subscription record does not exist at Subscriber.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeleteSubscriptionAtSubscriber&LinkId=20476

TITLE: Microsoft SQL Server Management Studio

------------------------------

Could not delete the subscription at Subscriber 'S608161RGSW14' in database 'TransactorMI'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeleteSubscriptionAt&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

'S608161RGSW14' is not defined as a Subscriber for 'AJGECOMVDBP235\GTI'.

Could not update the distribution database subscription table. The subscription status could not be changed.

Changed database context to 'Transactor'. (Microsoft SQL Server, Error: 20032)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=20032&LinkId=20476

Using below script you can drop the sucbscription  you need tun below script in publication server 

In below script 
MYTran is the publication database 
MYTranGTI_MY_PUB is publication name 
MYTranMI is the subscriber database name 

USE  MYTran

GO

DECLARE @publication AS sysname;

DECLARE @subscriber AS sysname;

--enter your publication name

SET @publication = N'MYTranGTI_MY_PUB';

--enter subscriber name

SET @subscriber = N'MYTranMI';

USE MYTran

EXEC sp_dropsubscription

@publication = @publication,

@article = N'all',

@subscriber = @subscriber

,@ignore_distributor=1;

GO

Using below script you can drop the publication you need tun below script in publication server 

MYTran is the publication database 

MYTranGTI_MY_PUB is publication name 

USE MYTran
Go

DECLARE @publicationDB AS sysname;

DECLARE @publication AS sysname;

--set your publication database here

SET @publicationDB = N'MYTran';

SET @publication = N'MYTranGTI_MY_PUB';

EXEC sp_droppublication

@publication = @publication

,@ignore_distributor=1;

-- Remove replication objects from the database.

USE [master]

EXEC sp_replicationdboption

@dbname = @publicationDB,

@optname = N'publish',

@value = N'false';

GO

See Also

Remove SQL Replication in Publisher and Subscriber after changing server names

How to copy non-clustered indexes to subscriber in snapshot replication

In snapshot replication by default Non-cluster indexes are not copied to subscriber below is the work around for copying Non-cluster indexes to subscriber in Snapshot replication.

Step 1:

  - Login into the Publisher server

  -  Expand the Replication folder and Local Publications

  -  Right click on the publisher and select properties

Step 2:

                In Publication Properties window select Articles Tab à Select the Article Properties

               

Step 3:

  -  In Popup select Set Properties of All Table articles to copy all objects Non-Cluster indexes

  -  Select Set Properties of Highlighted table article to change the option to copy Non-cluster indexes of specific table

Step 4: 

                In Properties for All Tables Articles window change the below highlighted property from False to True.

               

In next run of snapshot will copy all noncluster indexes to subscriber.