July 3, 2012 at 3:10 am
Hi all,
I was looking around and trying to find known issues with using either Mirroring or Log Shipping. From what i read if a app runs Truncate table, then it would break the the secondary version of the database as it wouldn't be in transaction log. Are there any other gotchas or things to look out for.
Thanks
July 3, 2012 at 3:22 am
Would be interested to know where you read that a truncate would break it as its not logged in the transactions log.
Truncate is a minimally logged operation, it is logged in the transaction log, but unlike a delete where it marks each row as deleting, it just marks the deallocation of the pages.
Try it, back up your DB (full), make a TX backup, truncate a table, make another TX backup, then do a restore of the DB, and the first TX log, table still full of data, then restore the second TX log, table now has no data.
I'm not a mirroring expert, but in terms of logshipping, the only main thing I would say is, dont have a anything other than the logshipping process backing up your transaction logs.
July 3, 2012 at 3:41 am
Hi and thank you! you always answer my questions its really appreciated!
I was googling yesterday and it came up on a few post over at stackoverflow (cant find them now :/).. there was also this one
http://ask.sqlservercentral.com/questions/16159/delete-and-truncate-in-log-shipping.html
but that post always gets cleared up later.
You make a good point with i should have tried it myself before posting.. Need to get in the habit of that.. much better way to learn..
Thank you again.
July 3, 2012 at 7:04 am
Firstly, this statement from the ask sql server central site is incorrect
TRUNCATE TABLE statements cannot be used for tables involved in replication or log shipping
Log shipping does not replicate atb the object level it replicates the whole database!
Nett result = don't believe everything you read
n00bDBA (7/3/2012)
Hi all,I was looking around and trying to find known issues with using either Mirroring or Log Shipping. From what i read if a app runs Truncate table, then it would break the the secondary version of the database as it wouldn't be in transaction log. Are there any other gotchas or things to look out for.
Thanks
In mirroring and log shipping the transactions are sent to the secondary\mirror and then applied there, albeit by different mechanisms but thats essentially what happens.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 3, 2012 at 7:18 am
n00bDBA (7/3/2012)
From what i read if a app runs Truncate table, then it would break the the secondary version of the database as it wouldn't be in transaction log.
Truncate is a logged operation. There are no unlogged data modifications in SQL Server (and technically, truncate table is fully logged)
Switching recovery models will break either (switching to simple breaks log shipping, switching to simple or bulk-logged breaks mirroring), restoring the database, reverting to a snapshot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2012 at 7:25 am
n00bDBA (7/3/2012)
Hi all,I was looking around and trying to find known issues with using either Mirroring or Log Shipping. From what i read if a app runs Truncate table, then it would break the the secondary version of the database as it wouldn't be in transaction log. Are there any other gotchas or things to look out for.
Thanks
1. As Anthony said above, ensure that log backups are done by log shipping backup job only.
2. Do not change the recovery model of the database to SIMPLE
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply