stop log shipping
-
Hi All,
How can I stop log shipping between log shipping pair of databases
Thanks in advance.
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me -
This was removed by the editor as SPAM
-
hi...i just tried this today..its very simple you can right click on the primary db and uncheck the option which says 'Enable this is as primary server' and then it will ask you whether you want to remove log shipping or not..click OK ..and ur all set..
-
You can just disable the job that does the restore to the database. Another option that I sometimes used is to modify the job that does the restore. I sometimes added those lines to the job:
DECLARE @dt datetime
SET @dt = ‘19000101’
If @dt < getdate()
When ever I want to stop the logs hipping for a known period of time (for example 3 hours), I just modify the value of @dt. This way the restore won’t be done for 3 hours, but after 3 hours it will continue doing the restore and I won’t have to remember to enable it (as you can imagine, before using this code few times I disabled the restore job and forgot to enable it when I should have)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
As per BOL:-
Right-click the database you want to use as your primary database in the log shipping configuration, and then click Properties.
Under Select a page, click Transaction Log Shipping.
Clear the Enable this as a primary database in a log shipping configuration check box.
Click OK to remove log shipping from this primary database.
-
Adi Cohn (9/15/2008)
You can just disable the job that does the restore to the database. Another option that I sometimes used is to modify the job that does the restore. I sometimes added those lines to the job:DECLARE @dt datetime
SET @dt = ‘19000101’
If @dt < getdate()
When ever I want to stop the logs hipping for a known period of time (for example 3 hours), I just modify the value of @dt. This way the restore won’t be done for 3 hours, but after 3 hours it will continue doing the restore and I won’t have to remember to enable it (as you can imagine, before using this code few times I disabled the restore job and forgot to enable it when I should have)
Adi
For some reason I thought that Helen wants to temporary stop the log shipping. It seems that I was wrong, and she wanted to completely remove the log shipping. If this is the case, my previous answer was completely off base.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Hi, I saw you reply to delay the log shipping which sounds great. I have two question? 1) when you modify the the restore job do you places your value of @dt. code before the code that's already there or after. 2) What would be the steps I would have to perform to come up If I wanted to come up on the stanby to take it out of read only mode.
Mo
-
The additional code that you write has to come before the code that the log shipping wizard creates. It should look more or less like this:
Declare @dt datetime
Set @dt = '2009-04-10 18:56:30.153'
If @dt <= getdate
Begin
XXXXXXXX
End
The xxxxxx is the original code that was created with the logshipping wizard.
I used this method when someone needed to create a report and the query for the report needed more time then the interval between the backups. During that time the database still could be accessed to read data only, and we couldn’t update it. You can’t take the secondary database out from standby mode. If you’ll do it, you’ll have to rebuild the log shipping.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I see you reply and thanks for the information. I have one more question? couldn't you do the samethings by alter the log shipping scheduled job time whan the job kicks off by delaying the job for three hours for example
MO
-
maury.lemons (4/10/2009)
I see you reply and thanks for the information. I have one more question? couldn't you do the samethings by alter the log shipping scheduled job time whan the job kicks off by delaying the job for three hours for exampleMO
Yes you can. You can also disable the job. The problem is that you might forget to enable the job or to set the time interval to the correct one. This way I just modified the code once (each time change the value of @dt to the future), and I didn’t have to modify anything else after that.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
I have another question? If I have log shipping going on can I take the standby server out of read only mode and recovery the database. Then do some testing then once I am done place the database back in read only mode then kick off the restore job. would that work or what is the process I would have have to do to do something like that. Thanks for you help
-
I just saw your last question and even if it's from long time ago, thought it'd be good to answer it anyways.
Once you restore the database (or logs) with the recovery option, the database can't receive anymore transaction log backups, so you won't be able to work on your testing that way. During the recovery all the REDOs and UNDOs needed to be performed are done, so after that there's no way you can undo any transaction (the D for durability in the ACID properties of transactions)
What you'll need to do is start the logshipping all over again.
Hope this enaswers your questions
Silvia
-
Hi,
I'm using MS SQL 2008 R2.
Please advise how can I temporary disable log shipping without damaging the current log shipping setting or process?
Which option should I use?
Thank you.
- Jack
-
Yep, this answer is dead on. It removed agent jobs from the primary and secondary.
After that I just deleted the secondary database which was in Read Only mode.
Thanx!
Wallace Houston
Sunnyland Farms, Inc."We must endeavor to persevere."
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply