x

effect of Truncate on Logshipping

I am planning to implement logshipping between Prod and DR servers.
I heard that we should not use the truncate command on a DB which is using logshipping,Is it True?
Will it break logshipping.
[I have tested logshipping by truncating the table, and i see the table is truncated on the secondary DB and logshipping is running smooth.]
What are the things that I shouldn't do while a DB is logshipping.

more ▼

asked Apr 28, 2011 at 11:17 AM in Default

avatar image

srivivek
535 41 42 47

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

I haven't experienced any issues with truncating tables with log shipping, however if you truncate your transaction log you will break log shipping. Quite possibly that is what someone else was warning you against.

more ▼

answered Apr 28, 2011 at 11:18 AM

avatar image

Tim
40.9k 39 94 168

(comments are locked)
10|1200 characters needed characters left

Besides truncating the transaction log that @TRAD mentioned, a few things that come right to mind are...

  • Don't place the primary database in the SIMPLE recovery model.

  • If the paths to the database files are different between the primary and secondary, don't add files

  • If you use Change Data Capture, you have to recreate CDC if you recover the secondary. KEEP_CDC and NORECOVERY are mutually exclusive restore options.

  • If you log ship a replicated database, the failover is not straightforward from a replication perspective

more ▼

answered Apr 28, 2011 at 06:22 PM

avatar image

KenJ
25k 3 13 20

(comments are locked)
10|1200 characters needed characters left
more ▼

answered Apr 28, 2011 at 10:37 PM

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

Truncating table will not effect on logshipping database, it will propapages the changes to secondary. Truncate command is a minimally logged command which notes in the transactional log about dellocated data pages for that perticular table and the same will be shipped to secondary so you can perform traction of table.

But trucation of log will breaks up the logshipping you need to reinitialize the secondary.

more ▼

answered Apr 29, 2011 at 03:41 AM

avatar image

Manikreddy
414 25 26 31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x30
x11

asked: Apr 28, 2011 at 11:17 AM

Seen: 2774 times

Last Updated: Apr 28, 2011 at 11:17 AM

Copyright 2018 Redgate Software. Privacy Policy