In SQL Server, is there any way to find when were the partitions created on the table?

Background: We have around 40 tables which are partitioned along with their indexes. We have total of 4 Filegroups. we have a windows service which is related to partitioning. what it does is, it creates new partitions if the number of records that reached some threshold(other logics are also there) and these partitions are created in one of the 4 filegroups in round robin fashion. we have lot of partitions. I want to know is there anywhere inside SQL Server, does it store the creation time of these partitions?

If not I will be shocked as I feel this is the basic which should be present in SQL Server no matter what. Please provide your inputs.

  • 1
    If the partition has its own file you could check the create date on the file. May 8, 2020 at 14:59
  • @TonyHinkle we are not creating phyiscal files for every partition so that will not help. I will give more info in question now. May 8, 2020 at 15:03
  • The sys.objects and sys.partitions have a create_date and modify_date field that can be obtained from joining object_id. The problem is that object_id is the id of the table that the partitions belong to, so there is not a breakdown of this information by partition in the metadata. (The dates belong to the tables) You'll need a clever method like tony-hinkle suggested. May 8, 2020 at 15:06

1 Answer 1


Perhaps you can use the modify_date of sys.partition_functions. As new partitions for existing tables are created by ALTERing the partition function.

Or mine these out of the default trace.

  • thanks a lot David!! You were right on money. Thanks again!!! :-) May 8, 2020 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.