4

-moved here from SO (no coments there)

Question:

what is a proper way of reclaiming space in big (hundreds of GBs) filegroup with single partition of table that is chronologically ordered and has no index fragmentation and cannot afford no index fragmentation? I hear lot of bad about shrinking (e.g. http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/). Index fragmentation and time complexity of rebuilding is my main concern

Background:

I have a huge table (few TBs) that has following properties:

  • data are stored only in chronological order (column 'C')
  • index is based on dimension 'A' and then the column 'C'
  • table is partitioned based on 'A', each partition has it's own filegroup (with 10% FileGrowth)

So data are only appended to each partition and storing works super quick.

We want to start partition also based on time (e.g. partition per quarter and dimension 'A'; again each partition in separate filegroup). But there are two concerns:

  • We cannot afford wasting with disk space (e.g. having 10% of free space in each historic partition that is never going to be touched again).
  • We cannot absolutely afford any index fragmentation (as with this amount of data our queries would suffer extremely).

It would be nice to be able to be able to just cut the free space from the end of filegroup after each quarter end and then e.g. mark the filegroup as read-only

Community
  • 1
Jan
  • 151
  • 4

1 Answers1

1

For reclaiming free space at the end of the partition:

DBCC SHRINKFILE (FileGroupFileName, TRUNCATEONLY)
GO

For marking the partition read only:

ALTER DATABASE [DBNAME]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [DBNAME] MODIFY FILEGROUP [FileGroupName] READ_ONLY;
GO

ALTER DATABASE [DBNAME]
SET MULTI_USER;
GO
Jan
  • 151
  • 4