SQL Server Performance

Manual clean up procedures

Discussion in 'SQL Server 2005 Replication' started by xiebo2010cx, Feb 5, 2007.

  1. xiebo2010cx Member

    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?


    Bug explorer/finder/seeker/locator
  2. satya Moderator

    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
    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.
  3. xiebo2010cx Member

    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
  4. xiebo2010cx Member

    Could not find anything valuable in terms of 4th point

    Bug explorer/finder/seeker/locator
  5. MohammedU New Member

    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')
    -- Only replication jobs or job schedules can be added, modified, dropped or viewed through replication stored procedures.
    RAISERROR(22538, 16, -1)
    RETURN 1

  6. xiebo2010cx Member

    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
  7. MohammedU New Member

    That make sense<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />This procedure is not available to regular user because it is in resource db which is not visible to the users.<br /><br /><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com

Share This Page