asked
delete and truncate in log shipping
hi all, what is difference between delete and truncate operation in log shipping?what are the changes in the secondary database with these operations ?
You can't / shouldn't use truncate in a log shipping solution because it is a minimal logged operation and that would break the logshipping. Delete on the other hand is slower but is fully supported in a logshipping solution. Deletes in primary database will also delete from the target database. > In addition, TRUNCATE TABLE statements > cannot be used for tables involved in > replication or log shipping, since > both depend on the transaction log to > keep remote databases consistent. > TRUNCATE TABLE cannot used be used > when a foreign key references the > table to be truncated, since TRUNCATE > statements do not fire triggers. This > could result in inconsistent data > because ON DELETE/UPDATE triggers > would not fire. If all table rows need > to be deleted and there is a foreign > key referencing the table, you must > drop the index and recreate it. If a > TRUNCATE TABLE statement is issued > against a table that has foreign key > references, the following error is > returned [
http://www.mssqltips.com/tip.asp?tip=1080][1] [1]:
http://www.mssqltips.com/tip.asp?tip=1080
As a matter of fact I simplified my answer. SQL server only records the page deallocations in the transaction log, and "Removes all rows from a table without logging the individual row deletes." ( see
http://msdn.microsoft.com/en-us/library/ms177570.aspx )
That MSSQL Tip is incorrect on the issue of TRUNCATE TABLE and Log Shipping. The target will deallocate the same pages as the source database per the record in the transaction log. The three specific scenarios in which TRUNCATE TABLE will not work properly are listed in the MSDN article from the previous comment: A table referenced by a foreign key, a table participating in an indexed view, and a table that is published via transactional replication or merge replication.
TRUNCATE is a logged operation in SQL Server 200x. If you are using FULL RECOVERY you will still be able to restore the database as normal from your log backups after you do a TRUNCATE. The only difference is that TRUNCATE logs whole pages at a time while DELETE logs each row deletion. TRUNCATE is sometimes called a "minimally logged" operation but that's really a misleading term because the same information is being logged, it's just that it happens faster.
and TRUNCATE doesn't bother to fire any deletion TRIGGERs... I know @Håkan Winther mentioned that earlier, but it's important.
Yes truncate will not affect on logshipping...it's just deallocates data pages allocated for specific table the same thing will be logged in the log file and applied to stndby server.
Thanks for all the replies, I have tested logshipping by truncating the table, and i see the table is truncated on the secondary DB and logshipping is running smooth. Now can i use truncate on DB(tables) which are using logshipping? What are the cautions that I should take while using logshipping.
