0

Basically, our database is created based on primary filegroup alone.

Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which has data files located at different drives for performance optimization. Now when I look into the database it looks like that the primary filegroup size have not been decreased.

FYI, I have partitioned an existing table based on index.

It looks like that the data has been copied records from my primary filegroup to secondary filegroups instead of moving the records.

Is there any fix for this or does the partition increases the database size ?

The table size looks to be doubled in size after partition and is that going to enhance performance occupying a lot of memory ?

Can I remove the duplicating records from primary filegroups after it has been moved to secondary filegroups ?

RajeshKannan
  • 163
  • 2
  • 9

1 Answers1

2

Once SQL Server acquires disk space from the operating system and allocates it to a database it will not voluntarily give it back. It takes time and effort to allocate this space so it is more efficient for the database to hang onto it once it has acquired it. If you genuinely have moved the data and not copied it (you would have to post your SQL for us to be sure) there will be a lot of free space in the files of the PRIMARY filegroup. Have a look at this post for hints how to find the free space in a file.

To force SQL Server to hand back this free space to the operating system you will have to uses a SHRINK command. This is well documented in Books Online. There are potential performance problems with SHRINK, so use it after proper consideration.

Community
  • 1
Michael Green
  • 25,255
  • 13
  • 54
  • 100