Error while setting up Log Shipping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Error while setting up Log Shipping

Author

Topic

shahab03
Starting Member

28 Posts

Posted - 2006-06-13 : 17:13:33

I am getting following error when setting up log shipping

Error 14261: The specified @name ('Log Shipping copy for NIMMAIN.vanxdb_logshipping') already exists.

I have cleaned up all the log shipping related tables in msdb database on primary, secondary and monitoring sql server instances but I am still getting this error.

Where else do I need to l look? or do?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-13 : 17:43:35

Was the maintenance plan deleted as well as the jobs?

Each time the wizard fails, you must manually clear out the tables, so go back and make sure that all log_shipping* tables have been cleared out of all 3 instances.

Tara Kizer
aka tduggan

shahab03
Starting Member

28 Posts

Posted - 2006-06-14 : 09:23:50

Hello Tara

There were couple of jobs which were not deleted. Didnt have any problem after deleting them.

By the way have you successfully performed a Log Shipping Role Change? Documentation is doesnt cover every aspect.

Thanks
Shahab

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-14 : 13:09:52

I have never successfully performed a log shipping role change. We tried it quite a few times in our tests. When we switch between our primary and disastery recovery site, switching is done manually. We then have to resetup log shipping going the other way. We switch 2-3 times per year to ensure that our disaster recovery plan works.

Tara Kizer
aka tduggan

shahab03
Starting Member

28 Posts

Posted - 2006-06-14 : 13:48:26

changing Logshipping roles seems easy from BOL...but I ve experienced lots of hurdles in each step....
I am not able to execute:

EXEC sp_change_secondary_role
@db_name = 'test',
@do_load = 1,
@force_load = 1,
@final_state = 1,
@access_level = 1,
@terminate = 1,
@stopat = NULL
GO

when I executed this SP, I got an error:

Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.

Per following

http://support.microsoft.com/default.aspx?scid=kb;en-us;294397&Product=sql2k

I can resolve the issue above by

1)Ensure that there are no outstanding transaction logs before you execute the sp_change_secondary_role stored procedure. Manually running the RESTORE job on the secondary server before you run sp_change_secondary_role ensures that there are no outstanding transaction logs.

2) You could pass the value of zero to the @terminate parameter (@terminate = 0) while you execute the sp_change_secondary_role stored procedure. However, the user must verify that there are no users connected to the database before the stored procedure is executed to ensure that the RESTORE LOG works successfully.

I would like to choose option 1. However I am not sure whats the best way to find which transaction logs need to be applied and how to apply them? any examples would be wonderful.
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources