Infrastructure at your Service

Microsoft Team

SQL Server 2016: TRUNCATE PARTITIONS with sliding Windows scenarios

Some time ago, I had to deal with a new partitioning scenario that included sliding windows stuff for mainly archiving purpose. Regarding the customer context, I used some management scripts that include this time dropping oldest partition. We didn’t care about data oldest than 2 years. Usually in this case, I use a method that consists in dropping data by switching first the oldest partition to a staging table and then truncate it. Finally we may safely merge oldest partitions and avoid any data movement. At a first glance, it seems to be a complex process for dropping data but until SQL Server 2014 there is no way to do better in order to minimize operation logging.


blog 105 - 0 - partitioning

This week, I had the opportunity to work with SQL Server 2016 to learn about new partition improvements. By the way, the only thing I could find out from my different internet researches concerned the new command TRUNCATE TABLE WITH PARTITIONS.

My first feeling was it is not a very exciting feature in contrast to previous versions that provided a mixture of both performance and maintenance improvements in this field. But after investigating further, I was able to point out some advantages to use this command. Let’s go back to my sliding windows scenario. In order to drop data from my oldest partition I have to:

  • Switch the oldest partition to a staging table
  • Drop data from this staging table with TRUNCATE command in order to minimize transaction logging
  • Execute MERGE command in order to slide all partitions to the left side


What about introducing the new TRUNCATE TABLE command in this scenario?

In fact, it will simplify the above process by replacing step 1 and step 2 by the TRUNCATE command at the partition level. The new scenario becomes:

  • TRUNCATE TABLE at the corresponding partition
  • Execute MERGE command in order to slide all partitions to the left


The only instruction I need to use is as follows:

TRUNCATE TABLE [dbo].[FactOnlineSales]


What about locking?

As expected, SQL Server will use a lock granularity hierarchy with a mixture of Sch-S, Sch-M and X locks regarding the corresponding locked resource. You may see two allocation units in my case because I’m using a partitioned clustered columnstore index in this demo. As a reminder, compressed columnstore segments are stored in LOB.


Object Resource type Resource subtype Resource description Associated entity Lock request mode
OBJECT FactOnlineSales Sch-M
METADATA DATA_SPACE data_space_id = 3 Columnstore2007 (filegroup that relies on partition nb 2) Sch-M
HOBT Partition nb 2 Sch-M
ALLOCATION_UNIT Related to data_space_id = 3 with state = DROPPED (LOB_DATA) X
ALLOCATION_UNIT Related to data_space_id = 3     (IN_ROW_DATA) X
KEY Records in the partition 2 X



What about logging?

Well, if I refer to the corresponding records into the transaction log file, TRUNCATE partition command seems to act as a normal TRUNCATE operation. Firstly, we may notice few records generated related to marking the concerned structures to drop and then the deferred drop mechanism comes into play by deallocating them.


blog 105 - 1 - truncate partitions tlog 1

blog 105 - 1 - truncate partitions tlog 2

blog 105 - 1 - truncate partitions tlog 3

blog 105 - 1 - truncate partitions tlog 4

Happy partitioning!

By David Barbarin





  • Prem Anand says:

    Hi David

    I have requirement in partitioning, In my environment daily we scheduled job to create partitioning which is daily partitioning for two tables.. So far 145 partition created I have a requirement like customer need only latest 90 days partition which means latest 90 days data. I need Scripts to do this step by step method( It could be very useful if we schedule job in weekly basis.). Could you please help on this. Thank you.

    SQL version : 2016 Enterprise edition


    • David Barbarin says:

      Hi Prem,

      To address your need, you may refer to the Microsoft template script that I used for some of my customers (with obviously some modifications that included new SQL Server features).



      • Prem Anand says:

        Thanks for your prompt response David, Could you please assist more on this as am new to partition ,
        I need below steps to perform and it should be in scheduled job,

        1. Switch partition to new archive table
        2. Truncate or drop partition in primary table which is switched, if not able to drop can merge.(Example – consider 10 partitions next day it will create one more like this it will go on.. i need only latest 5 partition to be present at original table remaining 5 need to switch and drop or merge.

        This should be as stored procedure so that i can schedule job. Thanks for your help.


  • Jeremy Langdon says:

    Hi David,

    Is it possible to bulk insert into or select from a partition while another partition is being truncated at the same time? Is there a lock conflict there? I can’t find this info anywhere. I know this was not possible in earlier versions leveraging a staging table/partition switch…. I found out the hard way.


    • David Barbarin says:

      Hi Jeremy,

      Sorry for the delay … very busy since 2 months. Regarding your question, I would say that we cannot bulk-insert to one partition while truncating another partition because the truncate partition still requires Sch-M at the object level whereas bulk insert must acquire a Sch-S to run (both lock modes at the object level are obviously incompatible)


  • Pete says:

    Hi David,

    From your example, does that mean if I truncate a specific partition, SQL Server will still require Sch-M to lock the whole table?

    I have a huge partitioned table with each partition storing a week’s data (roughly 140M records for a week/partition). This table also has traffic with 400 records/sec insertion.

    Since I only need to retain latest 9 weeks/partitions of data, I’d like to automate the partition truncation with a job (clean up the partitions that are old than 9 weeks and recreate the file group to spare disk space) so I don’t need to use partition switch to manually do the cleanup during system maintenance window. But I’m kind of worried about this will block all the real-time insertions for too long. Any suggestions?


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Microsoft Team
Microsoft Team