Sunday, January 31, 2010

Will Truncate Command be carried forward to Secondary Server in Log Shipping?

Truncate command will not delete data row by row hence deleted data will not be logged in Transaction Log. In Log shipping, transaction log backups will be transferred to the secondary server. So the natural question is whether truncate command will be carried forward to Secondary Server.

Let us examine, what is happening to transaction log during the truncate command. Following is the part of transaction log you will see when you issue a truncate statement.

 

Current LSN Operation Context
0000001c:00000014:0005 LOP_MODIFY_ROW LCX_PFS
0000001c:00000014:0006 LOP_MODIFY_ROW LCX_PFS
0000001c:00000014:0007 LOP_FORMAT_PAGE LCX_IAM
0000001c:00000014:0008 LOP_HOBT_DELTA LCX_NULL
0000001c:00000014:0009 LOP_MODIFY_ROW LCX_IAM

PFS – Page Free Space

IAM – Index Allocation Maps

The actual process for Truncate Table is to de-allocate the pages assigned to a table, this de-allocating part will be captured in log file. So in the, transaction log backup, this statement will be captured and will be transferred to the secondary server. This means that Truncate command will be carried forward to the Secondary Server.

Similarly, in mirroring, transaction log backup restoring, transactional replication truncate statement will be transferred to the intend targets.

PN: table is de-allocated by unhooking the IAM chains and then de-allocating the individual pages and extents using a background task – a process called deferred-drop, to avoid running out of locks during the de-allocation process.

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete