Effect of TRUNCATE TABLE command on LOG SHIPPING
I got a question from one of my friend whether Truncate table command will be carried forward to secondary server in log shipping since this command is non logged in SQL Server.
Truncate table command will be carried forward to the secondary server. Next question in your mind will be How its possible, since truncate table command is non logged operation?
Yes, You are correct when you run truncate command it won’t delete the data’s row by row hence those things will not be captured in log file. The actual process for Truncate Table is to de-allocate the pages assigned to a table, this de-allocating part (only this) will be captured in log file, when this log is shipped to secondary server the same thing will happen on redoing the log file so the table will get truncated in secondary server. I’ve attached the sample transaction log content, you can find the command Truncate table which is logged there.
July 18th, 2009 - 10:20
It’s not a non-logged operation – everything it does is fully logged. Instead, you should describe it as a non-DML operation – in that the table rows are not deleted, but in fact the entire set of allocated pages are deallocated. In 2000 SP4 onwards, they’re deallocated by unhooking the IAM chains and then deallocating the individual pages and extents using a background task – a process called deferred-drop, to avoid running out of locks during the deallocation process.
July 18th, 2009 - 12:40
@Paul Randal
Thanks Paul.