Sql-server – Table Partitioning – database file increased after partitioning

partitioningsql server

I have a table that is 100 GB in size. I forgot the exact blog which I read when partitioning tables but when I ran the partition query, my primary table file increased while being able to partition it into a new drive.

The steps I took were:

  1. Create file group
ALTER DATABASE MY_DB
ADD FILEGROUP MY_FILEGROUP
GO
  1. Create secondary file for the filegroup
ALTER DATABASE MY_DB
ADD FILE
(
NAME = my_partition,
FILENAME = 'D:\my_partition.ndf',
size = 3072 KB
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
TO FILEGROUP MY_FILEGROUP
  1. Create partition function
CREATE PARTITION FUNCTION myPartitionFunc (date)
AS RANGE RIGHT FOR VALUES ('20200101')
  1. Create partition scheme
CREATE PARTITION SCHEME myPartitionScheme
AS PARTITION myPartitionFunc
TO (PRIMARY, my_partition)
  1. Partition the table
ALTER TABLE my_schema.my_table DROP CONSTRAINT PK_MY_TABLE
ALTER TABLE my_schema.my_table ADD CONSTRAINT PK_MY_TABLE PRIMARY KEY CLUSTERED
(
DATE ASC,
ID ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON myPartitionSceme(DATE)

After step 5, I was able to partition my database to my two separate drives. My only problem was my primary database file increased after partition.

IE

My initial file is C:\my_initial_file.mdf is 100 GB and will be partitioned to D:\my_partition.ndf

Once partitioned, my D:\my_partition.ndf is 10 GB and my C:\my_initial_file.mdf is 94 GB which still consumed an excess of 4 GB.

My reason for partitioning is because my drive C is getting full and would like to keep the server running until we migrate to elsewhere.

Best Answer

In this instance rather than messing with the complexity of partitioning I'd either:

  • Just move the entire DB over to the drive with more space.
    Detach the DB, move the files over and reattach.
    See https://docs.microsoft.com/en-us/sql/relational-databases/databases/detach-a-database and the other related sections.
    Or backup, drop original, and resore with the files on the other drive.
  • Create a new datafile as you have done, but instead of partitioning tables, just move whole tables and indexes over to the new file group like so:
    CREATE CLUSTERED INDEX PK_MY_TABLE
    ON my_schema.my_table(DATE ASC, ID ASC)
    WITH (DROP_EXISTING = ON, ONLINE = ON)
    ON MY_FILEGROUP

In either case I would undo the partitioning first as it would not be needed, and for paranoia's sake take regular manual backups during the process.

With regard to partitioning, note that all your examples above have done is move the data (the clustered index) for rows one side of your partitioning function. The data for non-clustered indexes will still all be in the original filegroup unless you partition those also. This may explain not as much data moving over as you expected.

Note that is also applies to moving tables between filegroups without partitioning: the non-clustered indexes need to be moved explicitly also, or they will stay where they are. In fact having the indexes in another filegroup to the table will work just fine so you don't have to move them, or you could move then instead of the main table data (the clustered index or heap) and leave that where it is.

Freeing Space

Once all this is done, which ever way you do it (partitioning or moving while tables/indexes to a new filegroup), the file(s) making up your primary filegroup will not automatically shrink to release space to the OS, you will need to do this as an extra step afterwards.

Related Question