Truncating A Replicated Table

On a recent project that I was working on we came across an issue where we needed to replicate several databases on a new server.  Much of the ETL had places where tables were truncated and reloaded.  Normally this is not an issue at all, simply TRUNCATE TABLE and away you go.  This does however cause a problem when you have a replicated database.  According to the Microsoft documentation, which I obviously haven’t read all of, you can’t use TRUNCATE TABLE where the tables are referenced by a foreign key constraint, or participate in an idexed view.  The third situation where it is unavailable is where the table is published using transactional replication or merge replication.  The work around for this, again noted by Microsoft, is to simply use the DELETE statement in place of TRUNCATE TABLE.  Granted most of the time you are not going to be switching and using replication in the middle of a project, but in this case let’s just say there were plenty of ETL changes that needed to be made.  One other note on the difference between the two statements: TRUNCATE TABLE will reset your identity column to a default value of 1, DELETE does not do this.  If you need to remove all records from a table and reset the identity column, as I did, you will need to have a second statement to rest the identity column in addition to the DELETE statement.

You can find some more information about the differences between TRUNCATE TABLE and DELETE on the MSDN page for the TRUNCATE TABLE statement here:http://msdn.microsoft.com/en-us/library/ms177570.aspx  I will also place the list of advantages for TRUNCATE TABLE over DELETE below in case you are interested.

1. Less transaction log space is used.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

2. Fewer locks are typically used.

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.

3. Without exception, zero pages are left in the table.

After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

Bradley Schacht

Bradley Schacht is a Data Platform Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as SQLServerCentral.com and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

1 Response

  1. Mike says:

    What did you end up doing in that situation? Did you convert all TRUNCATE statements to DELETE statements or did you use another form of replication? What version of SQL Server were you using?

    I’m looking for a method to replicate a SQL Server 2012 database either in near real-time or at least on a daily basis that allows me to issue TRUNCATE table statements in stored procedures on the publisher database. The subscriber database will be read-only.

Leave a Reply