Post reply

In SQL 2012 what happened to PROC master.dbo.sp_MSload_replication_status

  • June 27, 2015 at 6:58 am

    #299520

    In SQL 2012 Enterprise, I'm trying to resolve the rogue RED X on a deleted Publication / Subscription by executing master..sp_MSload_replication_status and I'm getting error:

    Msg 2812, Level 16, State 62, Line 1

    Could not find stored procedure 'sp_MSload_replication_status'.

    I take it this proc has been deprecated in SQL 2012 Enterprise??

    Is there a replacement proc or process we can use to remove the rogue RED X in replication?

    BT

  • August 2, 2015 at 6:06 am

    #1816642

    [font="Courier New"]

    Rogue RED X on ReplMonitor for non-existent PUBS / SUBs ---

    -- !!! Use this solution at your own risk! It worked perfectly for me, twice to date. (successful on SQL 2008 R2 Enterprise and successful on SQL 2012 Enterprise)

    Problem: After deleting the SUBSCRIPTION for a PUBLICATION then deleting actual PUBLICATION PUBS, I was receiving ROGUE RED X in ReplMonitor.

    Resolution: Carefully review your distribution database "system tables" then delete the orphan rows from those tables including:

    3 tables to reveiew and cleanup include:

    -- distribution.[dbo].[MSpublications]

    -- distribution.[dbo].[MSreplication_monitordata]

    -- distribution.[dbo].[MSsnapshot_agents]

    ----- *************

    ----- START: *

    ----- *************

    -- ****

    -- **** 1) Cleanup TABLE: distribution.dbo.[MSpublications]

    -- ****

    select * from distribution.dbo.[MSpublications] WHERE -- (publication_id = NN)

    publication IN ('Your PUB Name')

    -- DELETE FROM distribution.dbo.[MSpublications] WHERE publication IN ('Your PUB Name')

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

    -- ****

    -- **** 2) Cleanup TABLE: distribution.dbo.[MSreplication_monitordata]

    -- ****

    select * from distribution.[dbo].[MSreplication_monitordata] WHERE Status = 6

    -- DELETE FROM distribution.dbo.[MSreplication_monitordata] WHERE

    publication = 'Your PUB Name'

    select * from distribution.[dbo].[MSreplication_monitordata]

    WHERE agent_name = 'YourServerName\YourServerReplication-Name-NN'

    -- DELETE FROM distribution.dbo.[MSreplication_monitordata] WHERE

    agent_name = 'YourServerName\YourServerReplication-Name-NN'

    (NOTE: after running the DELETE and re-RUNNING the SELECT, you may still see your row(s); not to worry)

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

    -- ****

    -- **** 3) Cleanup TABLE: distribution.dbo.[MSsnapshot_agents]

    -- ****

    select * from distribution.[dbo].[MSsnapshot_agents] (NOLOCK)

    -- DELETE FROM distribution.[dbo].[MSsnapshot_agents] WHERE publication = 'Your PUB Name'

    -- IMPORTANT: YOU MAY NEED to WAIT a few to several minutes for the ROGUE RED X to DISAPPEAR from ReplMonitor....

    ----- *************

    ----- END: *

    ----- *************[/font]

    BT

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply