Post reply

Getting wiered error in log shipping configuration

  • July 16, 2009 at 1:19 pm

    #1025872

    Thanks you,

    Executing below script on Primary has resolved the issue.:-)

    DELETE FROM msdb.dbo.log_shipping_monitor_secondary

    WHERE secondary_database='YOUR UNWANTED DBNAME'

  • July 22, 2009 at 1:03 am

    #1028106

    Hi Max wei,

    I have been disabled the log shipping for all the databases including the problem creating database MyDb. But some of the log shipping tables still have the log shipping information on PRIMARY SERVER ONLY. please see the attachment and please tell me how can I cleanup these tables?

    Can I truncate these tables?? Is truncating table solves the issue? Is it any harm to truncate the these tables.In the attachmnet, I have mentioned only some columns not all columns

  • July 22, 2009 at 10:20 am

    #1028493

    Could you please advice me how to cleanup the Log shipping tables, if disable log shipping task for database not removes everything?

  • July 23, 2009 at 10:51 am

    #1029145

    Hi,

    I'm also facing the same issue...I'm configuring the log shipping in sql server 2005 EE

    I just want to make sure that when we disable already configured log shipping for 10 databases:

    1. All the jobs on Primary and secondary should be deleted.

    2.All the log shipping tables in msdb should has clean tables. No data in them right? If an any case,some log shipping tables still have some data even after removing Log shipping, what are the steps to follow to cleanup those tables? OR

    3.Is the log shipping tables will contain some data even after you disable log shipping for all database and there is NO log shipping at all.

    I appreciate your help

    Thanks

  • July 23, 2009 at 3:02 pm

    #1029296

    Yes, disabling the logshipping on the databases would not completely clean up the logshipping related tables in MSDB.

    For those of you who are database-hygienist and never want unuseful-unrelated data in your database, you will need to clean up the Data in some tables manually, or you could write up a SP to clean it up.

    Per a blog article from the SSC member here hi_abhay78, you should be able to clean up the unwanted stuff created by your legacy-faulty LS.

    One thing to keep in mind: make sure you get rid of the right entries, which may be identified by some _ID's. Check those log_shipping related tables in MSDB.

    For your convenience, the following are excerpted from his blog article. And there is more in the article.

    Check this first :

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

    Run these SPs first on secondary and then on Primary if your previous setup failed or had some issue wiping out the stuff:

    You can write a script to take the database names from sysdatabases and then fetch it to the variables (you will see how its done in the script many times).

    sp_delete_log_shipping_primary_database @database= ''

    sp_delete_log_shipping_primary_secondary @primary_database='',@secondary_server='abhay-test\SPIDER',@secondary_database =''

    sp_delete_log_shipping_secondary_database @secondary_database=''

    sp_delete_log_shipping_secondary_primary @primary_database='',@primary_server='abhay-test\Apollo'

    check these tables on both primary and secondary in MSDB context .If you find any row that should not be there i.e. a row for a database which is no longer a part of logshipping but still exists , delete that row using where clause .if you feel that you first setup of logshipping failed and all the rows in the tables below are not needed delete all the rows .

    dbo.log_shipping_monitor_alert

    dbo.log_shipping_monitor_error_detail

    dbo.log_shipping_monitor_history_detail

    dbo.log_shipping_secondary_databases

    dbo.log_shipping_secondary

    dbo.log_shipping_secondaries

    dbo.log_shipping_primary_secondaries

    dbo.log_shipping_primary_databases

    dbo.log_shipping_primaries

    dbo.log_shipping_monitor_secondary

  • July 23, 2009 at 5:56 pm

    #1029387

    Yes, disabling the logshipping on the databases would not completely clean up the logshipping related tables in MSDB.

    Is this a bug in SQL Server 2005? Is this confirmed by MS? If yes, could you please take me to that link...

  • July 25, 2009 at 11:01 am

    #1030178

    Hi,

    Even my log shipping tables have some data after completely removing log shipping and all backup, copy & restore jobs from all databases.

    Now, I have reconfigured the log shipping for all databases and everything working fine. So I'm thinking even if you some data after completely removing data, it will not effect anything?

    If any body came across this, please advice how you handled?

  • July 25, 2009 at 11:36 am

    #1030185

    Log shippping tables store general configuration of your log shipping, errors and history of backup and restores. so i dont think it should bother you (except configuration part if its incorrect).


    Pradeep Singh

  • June 2, 2010 at 12:34 pm

    #1175502

    Hi, Klnsuddu,

    I have done the exactly the same thing here as you did and got the exactly same results and false alerts.

    Also I used Max Wei's suggestion and ran it on Primary. It resolved the issue:

    DELETE FROM msdb.dbo.log_shipping_monitor_secondary

    WHERE secondary_database='YOUR UNWANTED DBNAME'

  • April 18, 2012 at 8:19 am

    #1475360

    It did the magic ! Thanks 🙂

Viewing 10 posts - 16 through 24 (of 24 total)

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