Partitions and FileGroups

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rgn - 18 Mar 2008 15:26 GMT

I'm writing a SP to automate the creation & maintenance of Partitions and
Filegroups. Every 1st of a month, I have to create a new partition to move
all the previous months data to a new partition and at the end of the year I
have to consoldiate all the years partition into just one Partition.

I have automated the creation part and now I'm in the middle of automating
the deletion of the 11 partitions and moving the data (from all these 11
partitions) into the 12th partition.

Having moved these 11 partitions, I would like to drop the files and
filegroups associated to these partitions. To accomplish this, I need to find
out which partitions were mapped to which filegroups. Is there a way to find
out?

Thanks,
rgn

Reply to this Message

Ryan Stonecipher [MSFT] - 19 Mar 2008 20:23 GMT

I think that the following query should get you what you need. There's a
mapping table called sys.destination_data_spaces that maps the partition
scheme's "data space ID" to the data space of the filegroup:

select P.[partition_id], P.[object_id], P.[index_id], P.[partition_number],
FG.name 'filegroup_name'
from sys.partitions P
join sys.indexes I on P.index_id = I.index_id and P.object_id =
I.object_id
join sys.partition_schemes S on I.data_space_id = S.data_space_id
join sys.destination_data_spaces DDS on S.data_space_id =
DDS.partition_scheme_id
join sys.filegroups FG on DDS.data_space_id = FG.data_space_id
order by P.object_id, P.index_Id, P.partition_number

> I'm writing a SP to automate the creation & maintenance of Partitions and
> Filegroups. Every 1st of a month, I have to create a new partition to move
[quoted text clipped - 15 lines]
> Thanks,
> rgn

Reply to this Message