We restored a database on a new server for a migration. When attempting to set a subscription to a transactional publication, we get the error “Can’t drop the table because it is set for replication” However, it is not being replicated to. I have already set several push subscriptions up from this server that go to another so I don’t want to completely wipe out all replication and rebuild everything. I was wondering if there is a place that I could delete the piece of information that makes the article think it is still replicated to so that I can add it back to a subscription?
Date 1/29/2018 3:42:13 PM Log Job History
Step ID 1 Server Server1Pub Job Name RepJobName Step Name Run agent. Duration 00:00:05 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message 2018-01-29 21:42:13.174 Copyright (c) 2016 Microsoft Corporation 2018-01-29 21:42:13.174 Microsoft SQL Server Replication Agent: distrib 2018-01-29 21:42:13.174 2018-01-29 21:42:13.174 The timestamps prepended to the output lines are expressed in terms of UTC time. 2018-01-29 21:42:13.174 User-specified agent parameter values: -Publisher Server1Pub -PublisherDB DBPub -Publication PubName -Distributor Server1Pub -SubscriptionType 1 -Subscriber Server2Sub -SubscriberSecurityMode 1 -SubscriberDB db1 -XJOBID 0x466A0D3EF212C442B2B0E6521144ED71 -XJOBNAME ReplicationJobName -XSTEPID 1 -XSUBSYSTEM Distribution -XSERVER Server2Sub -XCMDLINE 0 -XCancelEventHandle 0000000000006F44 -XParentProcessHandle 000000000000592C 2018-01-29 21:42:13.174 Startup Delay: 2294 (msecs) 2018-01-29 21:42:15.474 Connecting to Subscriber ‘Server2Sub’ 2018-01-29 21:42:15.509 Connecting to Distributor ‘Server1Pub’ 2018-01-29 21:42:16.279 Parameter values obtained from agent profile: -bcpbatchsize 2147473647 -commitbatchsize 100 -commitbatchthreshold 1000 -historyverboselevel 1 -keepalivemessageinterval 300 -logintimeout 15 -maxbcpthreads 1 -maxdeliveredtransactions 0 -pollinginterval 5000 -querytimeout 1800 -skiperrors -transactionsperhistory 100 2018-01-29 21:42:16.864 Initializing 2018-01-29 21:42:17.239 Snapshot will be applied from the alternate folder ‘2.168.5.124\Replications\unc\Folder’ 2018-01-29 21:42:17.914 Agent message code 3724. Cannot drop the table ‘dbo.table’ because it is being used for replication. 2018-01-29 21:42:18.144 Category:COMMAND Source: Failed Command Number:
Message: drop Table [dbo].[table]
2018-01-29 21:42:18.194 Category:NULL Source: Microsoft SQL Server Native Client 11.0 Number: 3724 Message: Cannot drop the table ‘dbo.table’ because it is being used for replication.
