SQL Server Performance Forum – Threads Archive

Manual clean up procedures

Folks, As discussed in my previous thread, I really had hard time to disable the replications, So I decided to do:
1. Using SP_removedbreplication on 2 subscribers, successful
2. Using SP_removedbreplication on the publisher, failed with the same error.
3. Using sp_changedistributor_password to reset the pwd.
4. Suddenly, I found all the local publications were gone, strange?
5. Delete all the replicaiton jobs related to the replication agents
6. Delete all the logins
7. Delete the sys db distribution Anything else I forgot to clean up? Thanks ——————
Bug explorer/finder/seeker/locator
——————

What was the error on 2nd point?
What was the information from 4th point from SQL server error log? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

To Satya
Msg 22538, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 155 Only replication jobs or job schedules can be added, modified, dropped or viewed through replication stored procedures. ——————
Bug explorer/finder/seeker/locator
——————

Could not find anything valuable in terms of 4th point
——————
Bug explorer/finder/seeker/locator
——————

The following is the code of sp_MSrepl_check_job_access procedure where you were getting the error…I think your job catogory was modified or misconfigured…
May be procedures error out but deleted the replication… may be when you run in SSMS it may be running in a transaction…when you run through procedure may be it is not…
— Verify that the job to be modified is a repl job
IF NOT (@category_id = 10
AND @subsystem = N’Distribution’)
AND NOT (@category_id = 14
AND @subsystem = N’Merge’)
AND NOT (@category_id = 13
AND @subsystem = N’LogReader’)
AND NOT (@category_id = 15
AND @subsystem = N’Snapshot’)
AND NOT (@category_id = 19
AND @subsystem = N’QueueReader’)
— 18= REPL-Alert Response
— 16= REPL-Checkup
— 11= REPL-Distribution Cleanup
— 12= REPL-History Cleanup
— 20= Replication
— 17= REPL-Subscription Cleanup
AND NOT (@category_id IN (18, 16, 11, 12, 20, 17)
AND @subsystem = N’TSQL’)
BEGIN
— Only replication jobs or job schedules can be added, modified, dropped or viewed through replication stored procedures.
RAISERROR(22538, 16, -1)
RETURN 1
END
MohammedU.
Moderator
SQL-Server-Performance.com

Thank you, Mohammed, I did changed the job category, I created new category named REPL_TEST, put all related SQL jobs into that category. Thank you Where did you find that SP ‘sp_MSrepl_check_job_access’, I checked my instance (standard edition with sp2 CTP), could not be able to locate sp_MSrepl_check_job_access, I checked distribution and master database. ——————
Bug explorer/finder/seeker/locator
——————

That make sense<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> This procedure is not available to regular user because it is in resource db which is not visible to the users. MohammedU. Moderator SQL-Server-Performance.com

]]>