Getting wiered error in log shipping configuration

Question

Post reply

Getting wiered error in log shipping configuration

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

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

klnsuddu SSCertifiable Points: 5836 More actions · Answer 1

klnsuddu

SSCertifiable

Points: 5836

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'

klnsuddu SSCertifiable Points: 5836 More actions · Answer 2

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

klnsuddu SSCertifiable Points: 5836 More actions · Answer 3

klnsuddu

SSCertifiable

Points: 5836

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?

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 4

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

DBA in Unit 7 SSCarpal Tunnel Points: 4059 More actions · Answer 5

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

rambilla4 SSCrazy Eights Points: 8715 More actions · Answer 6

rambilla4

SSCrazy Eights

Points: 8715

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...

klnsuddu SSCertifiable Points: 5836 More actions · Answer 7

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?

ps. One Orange Chip Points: 29255 More actions · Answer 8

ps.

One Orange Chip

Points: 29255

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