Partitions and FileGroups

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
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
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> 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

Partitions and FileGroups

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,
rgnI 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
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> 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

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1
DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1
|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/functions
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>
|||sys.partitions - will give the row-count for the table/individual parititons,
so that is not an issue, Im more interested in a join based query for all the
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>
|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
--F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual parititons,
> so that is not an issue, Im more interested in a join based query for all the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particular
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resides
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:

Partitions / Filegroups

Hi All,
Has anyone managed to link the system views (or any other mechanisms) to
display which partition number is in which filegroup?
e.g. As follows:
Table Name Partition_Number Filegroup_Name No_Rows
Demo_Table 1 OLD_DATA 4,000,000
Demo_Table 2 MED_DATA 3,000,000
Demo_Table 3 CURRENT_DATA 50,000
Thanks in advance,
DBX1DBX1
Have you tried
SELECT * FROM sys.partitions ?
"DBX1" <DBX1@.discussions.microsoft.com> wrote in message
news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
> Hi All,
> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
> Thanks in advance,
> DBX1|||Hi!

> Has anyone managed to link the system views (or any other mechanisms) to
> display which partition number is in which filegroup?
> e.g. As follows:
> Table Name Partition_Number Filegroup_Name No_Rows
> Demo_Table 1 OLD_DATA 4,000,000
> Demo_Table 2 MED_DATA 3,000,000
> Demo_Table 3 CURRENT_DATA 50,000
Partitions do not map one-to-one to filegroups. You can have multiple
partitions on a single filegroup. You can find basic info about partitions
in sys.partitions view or with $PARTITION function, like
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
To find number of rows in different filegroups, the query gets complicated:
WITH
PartitionRowCount(Partition, NumberOfRowsInPartition) AS
(
SELECT $PARTITION.myRangePF1(col1),
COUNT(*)
FROM PartitionTable
GROUP BY $PARTITION.myRangePF1(col1)
),
PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
DataSpaceName) AS
(
SELECT dds.partition_scheme_id,
dds.destination_id,
dds.data_space_id,
ds.name
FROM sys.destination_data_spaces dds
INNER JOIN sys.indexes i
ON dds.partition_scheme_id = i.data_space_id
INNER JOIN sys.data_spaces ds
ON dds.data_space_id = ds.data_space_id
WHERE i.object_id = OBJECT_ID('PartitionTable')
AND i.index_id < 2
)
SELECT pds.DataSpaceName,
SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
FROM PartitionRowCount prc
INNER JOIN PartitionsDataSpaces pds
ON prc.Partition = pds.Partition
GROUP BY pds.DataSpaceName;
I hope I did not miss something.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Yup, but this lists the partition something is in, and not the filegroup a
partition resides in, the closest I have come to making this work is the
following:
-- This is not very tidy code currently, and needs to be refactored
-- Partition_Table_Example : This is the table which we are interested in,
for filtering purposes in this example
--
select DS.destination_id , F.name,
PIX.obj_name, PIX.partition_number, PIX.[rows], PIX.[index_id]
from sys.destination_data_spaces DS
join sys.filegroups F
on DS.data_space_id = F.data_space_id
join (
SELECT OBJECT_NAME(P.[object_id]) as obj_name,
P.partition_number, P.[rows], P.[index_id]
FROM sys.partitions P
WHERE P.[object_id] = OBJECT_ID('[dbo].[Partition_Table_Example]
')
) PIX
on PIX.Partition_number = DS.Partition_ID
-- This works, the interesting thing to note is that Partition_number links
to
-- Destination_ID on the Partition_ID sys.partitions view
--There may be another link required between sys.partitions and
sys.destination_data_spaces if there are multiple partition schemes/function
s
-- This has only been validated where there is a single partition_scheme
"Uri Dimant" wrote:

> DBX1
> Have you tried
> SELECT * FROM sys.partitions ?
>
>
>
> "DBX1" <DBX1@.discussions.microsoft.com> wrote in message
> news:15F0C880-C509-4F68-8810-531DE6A89236@.microsoft.com...
>
>|||sys.partitions - will give the row-count for the table/individual parititons
,
so that is not an issue, Im more interested in a join based query for all th
e
partitioned objects, I have manged to get something close, with the prior
querys for all objects.
It is missing something currently when there are multi-partition
defined/multi-partitioned objects as it returns some incorrect data.
The problem with the enclosed query is it is limited to a single object or
requires adding for obj_ids. and does not return the filegroup a particular
item or partition resides in.
What I am particulary interested in knowing is what sliced partition resides
in which filegroup.
I am aware that there can be multiple partitions in a single filegroup.
this does not list the filegroup names.
"Dejan Sarka" wrote:

> Hi!
>
> Partitions do not map one-to-one to filegroups. You can have multiple
> partitions on a single filegroup. You can find basic info about partitions
> in sys.partitions view or with $PARTITION function, like
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> To find number of rows in different filegroups, the query gets complicated
:
> WITH
> PartitionRowCount(Partition, NumberOfRowsInPartition) AS
> (
> SELECT $PARTITION.myRangePF1(col1),
> COUNT(*)
> FROM PartitionTable
> GROUP BY $PARTITION.myRangePF1(col1)
> ),
> PartitionsDataSpaces (partition_scheme_id, Partition, data_space_id,
> DataSpaceName) AS
> (
> SELECT dds.partition_scheme_id,
> dds.destination_id,
> dds.data_space_id,
> ds.name
> FROM sys.destination_data_spaces dds
> INNER JOIN sys.indexes i
> ON dds.partition_scheme_id = i.data_space_id
> INNER JOIN sys.data_spaces ds
> ON dds.data_space_id = ds.data_space_id
> WHERE i.object_id = OBJECT_ID('PartitionTable')
> AND i.index_id < 2
> )
> SELECT pds.DataSpaceName,
> SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace
> FROM PartitionRowCount prc
> INNER JOIN PartitionsDataSpaces pds
> ON prc.Partition = pds.Partition
> GROUP BY pds.DataSpaceName;
> I hope I did not miss something.
> --
> Dejan Sarka
> http://www.solidqualitylearning.com/blogs/
>
>|||> this does not list the filegroup names.
It lists data space names for filegroups. Data space names include filegroup
& partition scheme names.
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||Anyone able to validate the code below (I think this is producing the data
that I require):
select distinct object_name(SI.object_id),*
-- F.name,SP.rows
from sys.destination_data_spaces DS
join sys.filegroups F on
F.data_space_id = DS.data_space_id
join sys.partition_schemes PS
on PS.data_space_id = DS.partition_scheme_id
join sys.indexes SI on
SI.data_space_id = DS.partition_scheme_id
join sys.partitions SP on
SP.object_id = SI.object_id and
SI.index_id = SP.index_id and
SP.partition_number = DS.destination_id
"DBX1" wrote:
[vbcol=seagreen]
> sys.partitions - will give the row-count for the table/individual paritito
ns,
> so that is not an issue, Im more interested in a join based query for all
the
> partitioned objects, I have manged to get something close, with the prior
> querys for all objects.
> It is missing something currently when there are multi-partition
> defined/multi-partitioned objects as it returns some incorrect data.
> The problem with the enclosed query is it is limited to a single object or
> requires adding for obj_ids. and does not return the filegroup a particula
r
> item or partition resides in.
> What I am particulary interested in knowing is what sliced partition resid
es
> in which filegroup.
> I am aware that there can be multiple partitions in a single filegroup.
> this does not list the filegroup names.
>
> "Dejan Sarka" wrote:
>