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:
- Create file group
ALTER DATABASE MY_DB ADD FILEGROUP MY_FILEGROUP GO
- 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
- Create partition function
CREATE PARTITION FUNCTION myPartitionFunc (date) AS RANGE RIGHT FOR VALUES ('20200101')
- Create partition scheme
CREATE PARTITION SCHEME myPartitionScheme AS PARTITION myPartitionFunc TO (PRIMARY, my_partition)
- 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.
My initial file is
C:\my_initial_file.mdf is 100 GB and will be partitioned to
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.