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.
Difference between TRUNCATE and DELETE command
Lets see the difference between DELETE and TRUNCATE command as below
DELETE |
TRUNCATE |
|
DML Statement |
DDL Statement |
|
Data Rollback is possible, all the row by row data deletion will be recorded in transaction log |
Rollback is possible for entire table and not at row level, since data page reallocation will be logged in transaction log Eg.) BEGIN TRAN TRUNCATE Table Tablename ROLLBACK |
|
No Commit is performed neither before nor after. (Because it is a DML Statement) |
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement) |
|
Row level locking will happen while performing deletion |
Table level locking will be happen while performing truncation |
|
A delete does not move the High Water Mark of the table back to zero, it retains its original position. |
A truncate moves the High Water Mark of the table back to zero |
|
WHERE statement can be included |
WHERE statement cannot be included |
|
Activates DML trigger |
Doesn’t activate DDL trigger |
|
Able to execute the command on the table that contains foreign key |
Not able to execute the command on the table that contains foreign key |
|
Consume more resources since need to process row by row and all these are logged operation |
Consume less resource since it will process the full table and its non-logged operation |
Lets discuss the above points using an example