How to clean up SQL server agent jobs after a replicated DB removal

[desmondchew]

Our DBA has migrated a replicated DB to another server. However the old server still has Qlik Replicate job created in SQL server agents as it was configured for replication with publish and distribution setup.

How can we remove all the SQL server agents jobs that are related to the database that was setup for Qlik replication as it is no longer necessary?

Thank you.

Desmond 

[SushilKumar]

Hello Team,

When you want to disable replication. refer below Microsoft link below.

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/replication/manually-remove-repli...

For Qlik replicate follow below steps.

revoke all the privilege from QLIK replicate user. If its a part of db_owner group or SYSADMIN group   remove it from them. or disable it from that particular DB or instance.

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer.

Thanks

Sushil Kumar

[desmondchew]

Hi,

The issue is we are still using Qlik replication for other databases in the old server (Server A). We only migrated one schema (database) to another server (Server B). So let say we have 5 databases in Server A was setup initially for replication. Now we have left 4 databases in Server A that still utilizing Qlik. We do not want to move those. We only want to remove the one that we migrated.

[SushilKumar]

Hello Team,

In That case you need to keep all the Privileges intact As SQL keeps info at System metadata level (INSTANCE) not the individual Scheme (DB) level. Hence however you can disable/reverse some Setting   at table levels which were under that Scheme. Rest you need to keep as it as.

Regards,

Sushil Kumar

[PGN]

On the distributor, just delete the Log reader agent job(s).  There's one created per database.  On the database server (which may be the same as the distributor depending on your setup), delete the publication for the database in question.  

[DesmondWOO · Accepted solution]

Hi @desmondchew ,

In the SQL Server Management Studio --> Replication --> Local Publications, you should find the publication named like this:

[db name]:AR_PUBLICATION_XXXXXX

Delete the publication that you no longer need.

After deleting the publication, the corresponding SQL Server Agent job should be automatically removed. Please verify if this resolves the issue.

Regards,
Desmond

[desmondchew]

Hi,

I could see that value is no longer present under "Local Publications" for the DB that I we have moved to another server. However the db is still visible under "Replication Monitor"

Please see screenshot attached.

[DesmondWOO]

Hi @desmondchew ,

Publisher should be removed from the Replication monitor automatically after deleting the publication.  Here are the steps I attempted:

1. Started a new task for SQL Server. Allowed- Replicate to create publication.
2. Observed that the new publication appeared in the Replication Monitor.
3. Stopped Replicate task.
4. Deleted the publication as I mentioned in the previous post.
5. Refresh the SQL Server Studio Management GUI.
6. Publisher was removed.

I saw a "red cross" icon in the screenshot you shared. It is recommended to consult with your DBA or Microsoft engineer for further assistance.

Regards,
Desmond