Failure adding a new partition range to a partition function.

Long Post (so you know what I've already tried).
I'll start off with the definitions of existing objects, but in summary, I'm
attempting to add a new range to a partition function, and expect this to be
an almost instantaneous operation, but it is not (and, at the moment, I can'
t
get it to complete at all).
The partition scheme started off with two groups:
CREATE PARTITION SCHEME [schemePartitonFile] AS PARTITION [fnPartiti
onFile]
TO ([MeterGroup1], [MeterGroup2])
We have managed to fill the second group (MeterGroup2) (poor planning), and
so need to add a new partition.
MeterGroup2 was specified not to grow past (the size of the LUN it was on -
0.5GB).
The partition function we are using is based on a FileID (FID), and is
currently defined as:
CREATE PARTITION FUNCTION [fnPartitionFile](int) AS RANGE LEFT FOR VALUE
S
(8000)
So, all files < 8000 go into MeterGroup1, and all Files >= 8000 go into
meter group 2.
There are four tables using the scheme - for example:
CREATE TABLE [dbo].[tbReadIntDevDtl](
[FID] [int] NOT NULL,
[Block] [smallint] NOT NULL,
[Period] [tinyint] NOT NULL,
[IntervalValue] [int] NOT NULL,
[Quality] [char](1) NOT NULL,
CONSTRAINT [PK_tbReadIntDevDtl] PRIMARY KEY CLUSTERED
(
[FID] ASC,
[Block] ASC,
[Period] ASC
)ON [schemePartitonFile]([FID])
)
So with MeterGroup2 filled, we would like to add a new group to the
partition scheme, and direct all new files to that group.
Checked the current max FID: 31976
What we expect to be able to do is:
ALTER PARTITION SCHEME schemePartitonFile NEXT USED MeterGroup3
...followed by...
ALTER PARTITION FUNCTION fnPartitionFile() SPLIT RANGE (31977)
Given that the current max FID is 31976, we would expect to be able to run
the alter partition function statement, and it would not need to do any
physical movement of data, and should complete pretty much instantly,
resulting in the following scheme...
FID < 8000 in Partition 1
FID >= 8000 AND FID < 31977 in Partition 2
FID >= 31977 in Partition 3
At first, I immediately got the following error:
Could not allocate space for object
'dbo.tbReadIntDevDtl'.'PK_tbReadIntDevDtl' in database 'SageEMS' because the
'MeterGroup2' filegroup is full. Create disk space by deleting unneeded
files, dropping objects in the filegroup, adding additional files to the
filegroup, or setting autogrowth on for existing files in the filegroup.
I increased the size of MeterGroup2 to use the 0.5GB of free space on the
LUN, and then the process would run for around 45 minutes, then start rollin
g
back, ending with an error that the database was out of log.
I increased the log from 15GB to 36GB, and now the process runs for around
45 minutes before rolling back, and ends with the same error that we got at
first - can't increase space in MeterGroup2.
To verify that my understanding of the process of splitting partitions is
correct, I created a new scheme, a new function, and a new copy of the table
in question - identical to existing scheme/function/table in structure.
I then put in the first and last 1000 rows for each range (e.g. top 1000 *
order by FID asc where FID < 8000, then top 1000 * order by FID desc where
FID < 8000, followed by top 1000 * order by FID asc where FID >= 8000 and to
p
1000 * order by FID desc where FID >= 8000.
As expected, this indicates 2000 rows in each of the two partitions.
Splitting the new partition, and checking the rows shows (as expected) 2000
rows in the first two partitions, and none in the third.
select index_id, partition_number, rows
from sys.partitions
where object_id = object_id('tbReadIntDevDtl')
index_id partition_number rows
-- -- --
1 1 2000
1 2 2000
1 3 0
What could be causing this issue. Have I got something fundamentally wrong
in my understanding of how RANGE SPLIT works?
Thanks
Bernard
P.S. Please, no answers suggesting I should switch partitions, because that
isn't what I'm trying to do. We will switch older partitions out, but here
we simply want to direct new records into a new partition.> Given that the current max FID is 31976, we would expect to be able to run
> the alter partition function statement, and it would not need to do any
> physical movement of data, and should complete pretty much instantly,
> resulting in the following scheme...
No, what you need to do is to always have an empty partition "at the end". A
nd when you want to
split, you split so you have two empty partitions. I.e., there will be no da
ta movement when you
split one empty partition into two empty partitions. Same basic principle go
es for when you purge
data (if you are going to do that). In other words, never populate your "unb
ound"/"edge" partitions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in mess
age
news:C9A212E7-1A8D-4054-81C5-4E2DA6214025@.microsoft.com...
> Long Post (so you know what I've already tried).
> I'll start off with the definitions of existing objects, but in summary, I
'm
> attempting to add a new range to a partition function, and expect this to
be
> an almost instantaneous operation, but it is not (and, at the moment, I ca
n't
> get it to complete at all).
> The partition scheme started off with two groups:
> CREATE PARTITION SCHEME [schemePartitonFile] AS PARTITION [fnParti
tionFile]
> TO ([MeterGroup1], [MeterGroup2])
> We have managed to fill the second group (MeterGroup2) (poor planning), an
d
> so need to add a new partition.
> MeterGroup2 was specified not to grow past (the size of the LUN it was on
-
> 0.5GB).
> The partition function we are using is based on a FileID (FID), and is
> currently defined as:
> CREATE PARTITION FUNCTION [fnPartitionFile](int) AS RANGE LEFT FOR VAL
UES
> (8000)
> So, all files < 8000 go into MeterGroup1, and all Files >= 8000 go into
> meter group 2.
> There are four tables using the scheme - for example:
> CREATE TABLE [dbo].[tbReadIntDevDtl](
> [FID] [int] NOT NULL,
> [Block] [smallint] NOT NULL,
> [Period] [tinyint] NOT NULL,
> [IntervalValue] [int] NOT NULL,
> [Quality] [char](1) NOT NULL,
> CONSTRAINT [PK_tbReadIntDevDtl] PRIMARY KEY CLUSTERED
> (
> [FID] ASC,
> [Block] ASC,
> [Period] ASC
> )ON [schemePartitonFile]([FID])
> )
> So with MeterGroup2 filled, we would like to add a new group to the
> partition scheme, and direct all new files to that group.
> Checked the current max FID: 31976
> What we expect to be able to do is:
> ALTER PARTITION SCHEME schemePartitonFile NEXT USED MeterGroup3
> ...followed by...
> ALTER PARTITION FUNCTION fnPartitionFile() SPLIT RANGE (31977)
> Given that the current max FID is 31976, we would expect to be able to run
> the alter partition function statement, and it would not need to do any
> physical movement of data, and should complete pretty much instantly,
> resulting in the following scheme...
> FID < 8000 in Partition 1
> FID >= 8000 AND FID < 31977 in Partition 2
> FID >= 31977 in Partition 3
> At first, I immediately got the following error:
> Could not allocate space for object
> 'dbo.tbReadIntDevDtl'.'PK_tbReadIntDevDtl' in database 'SageEMS' because t
he
> 'MeterGroup2' filegroup is full. Create disk space by deleting unneeded
> files, dropping objects in the filegroup, adding additional files to the
> filegroup, or setting autogrowth on for existing files in the filegroup.
> I increased the size of MeterGroup2 to use the 0.5GB of free space on the
> LUN, and then the process would run for around 45 minutes, then start roll
ing
> back, ending with an error that the database was out of log.
> I increased the log from 15GB to 36GB, and now the process runs for around
> 45 minutes before rolling back, and ends with the same error that we got a
t
> first - can't increase space in MeterGroup2.
> To verify that my understanding of the process of splitting partitions is
> correct, I created a new scheme, a new function, and a new copy of the tab
le
> in question - identical to existing scheme/function/table in structure.
> I then put in the first and last 1000 rows for each range (e.g. top 1000 *
> order by FID asc where FID < 8000, then top 1000 * order by FID desc where
> FID < 8000, followed by top 1000 * order by FID asc where FID >= 8000 and
top
> 1000 * order by FID desc where FID >= 8000.
> As expected, this indicates 2000 rows in each of the two partitions.
> Splitting the new partition, and checking the rows shows (as expected) 200
0
> rows in the first two partitions, and none in the third.
> select index_id, partition_number, rows
> from sys.partitions
> where object_id = object_id('tbReadIntDevDtl')
> index_id partition_number rows
> -- -- --
> 1 1 2000
> 1 2 2000
> 1 3 0
> What could be causing this issue. Have I got something fundamentally wron
g
> in my understanding of how RANGE SPLIT works?
> Thanks
> Bernard
> P.S. Please, no answers suggesting I should switch partitions, because th
at
> isn't what I'm trying to do. We will switch older partitions out, but her
e
> we simply want to direct new records into a new partition.|||Beranrd
Check out this great article
http://www.simple-talk.com/sql/sql-...r-20
05/
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in
message news:C9A212E7-1A8D-4054-81C5-4E2DA6214025@.microsoft.com...
> Long Post (so you know what I've already tried).
> I'll start off with the definitions of existing objects, but in summary,
> I'm
> attempting to add a new range to a partition function, and expect this to
> be
> an almost instantaneous operation, but it is not (and, at the moment, I
> can't
> get it to complete at all).
> The partition scheme started off with two groups:
> CREATE PARTITION SCHEME [schemePartitonFile] AS PARTITION
> [fnPartitionFile]
> TO ([MeterGroup1], [MeterGroup2])
> We have managed to fill the second group (MeterGroup2) (poor planning),
> and
> so need to add a new partition.
> MeterGroup2 was specified not to grow past (the size of the LUN it was
> on -
> 0.5GB).
> The partition function we are using is based on a FileID (FID), and is
> currently defined as:
> CREATE PARTITION FUNCTION [fnPartitionFile](int) AS RANGE LEFT FOR VAL
UES
> (8000)
> So, all files < 8000 go into MeterGroup1, and all Files >= 8000 go into
> meter group 2.
> There are four tables using the scheme - for example:
> CREATE TABLE [dbo].[tbReadIntDevDtl](
> [FID] [int] NOT NULL,
> [Block] [smallint] NOT NULL,
> [Period] [tinyint] NOT NULL,
> [IntervalValue] [int] NOT NULL,
> [Quality] [char](1) NOT NULL,
> CONSTRAINT [PK_tbReadIntDevDtl] PRIMARY KEY CLUSTERED
> (
> [FID] ASC,
> [Block] ASC,
> [Period] ASC
> )ON [schemePartitonFile]([FID])
> )
> So with MeterGroup2 filled, we would like to add a new group to the
> partition scheme, and direct all new files to that group.
> Checked the current max FID: 31976
> What we expect to be able to do is:
> ALTER PARTITION SCHEME schemePartitonFile NEXT USED MeterGroup3
> ...followed by...
> ALTER PARTITION FUNCTION fnPartitionFile() SPLIT RANGE (31977)
> Given that the current max FID is 31976, we would expect to be able to run
> the alter partition function statement, and it would not need to do any
> physical movement of data, and should complete pretty much instantly,
> resulting in the following scheme...
> FID < 8000 in Partition 1
> FID >= 8000 AND FID < 31977 in Partition 2
> FID >= 31977 in Partition 3
> At first, I immediately got the following error:
> Could not allocate space for object
> 'dbo.tbReadIntDevDtl'.'PK_tbReadIntDevDtl' in database 'SageEMS' because
> the
> 'MeterGroup2' filegroup is full. Create disk space by deleting unneeded
> files, dropping objects in the filegroup, adding additional files to the
> filegroup, or setting autogrowth on for existing files in the filegroup.
> I increased the size of MeterGroup2 to use the 0.5GB of free space on the
> LUN, and then the process would run for around 45 minutes, then start
> rolling
> back, ending with an error that the database was out of log.
> I increased the log from 15GB to 36GB, and now the process runs for around
> 45 minutes before rolling back, and ends with the same error that we got
> at
> first - can't increase space in MeterGroup2.
> To verify that my understanding of the process of splitting partitions is
> correct, I created a new scheme, a new function, and a new copy of the
> table
> in question - identical to existing scheme/function/table in structure.
> I then put in the first and last 1000 rows for each range (e.g. top 1000 *
> order by FID asc where FID < 8000, then top 1000 * order by FID desc where
> FID < 8000, followed by top 1000 * order by FID asc where FID >= 8000 and
> top
> 1000 * order by FID desc where FID >= 8000.
> As expected, this indicates 2000 rows in each of the two partitions.
> Splitting the new partition, and checking the rows shows (as expected)
> 2000
> rows in the first two partitions, and none in the third.
> select index_id, partition_number, rows
> from sys.partitions
> where object_id = object_id('tbReadIntDevDtl')
> index_id partition_number rows
> -- -- --
> 1 1 2000
> 1 2 2000
> 1 3 0
> What could be causing this issue. Have I got something fundamentally
> wrong
> in my understanding of how RANGE SPLIT works?
> Thanks
> Bernard
> P.S. Please, no answers suggesting I should switch partitions, because
> that
> isn't what I'm trying to do. We will switch older partitions out, but
> here
> we simply want to direct new records into a new partition.|||Thanks for the reply, Tibor.
That leads to a couple of follow up questions:
With RANGE LEFT, it would appear that the edge partition can't ever have
data (if I want to be able to quickly change ranges), which means that I
can't have a strategy where I fill an edge partition until full, and then
create a new partition and adjust the ranges so new data flows into it.
This is the strategy that I would like to use: we want to be able to load
data into "bucket" (last partition) where we always fill the last bucket
until (nearly) full, then start filling the next bucket. Each bucket is
sized to hold a reasonable amount of data (around 1 billion rows, or
thereabouts), but we can't accurately predict when we will fill a bucket.
We will eventually switch out then delete old data (once it is 7 years after
it was loaded) - but our date partitioning is less to do with the date that
the data is for, and more to do with the date it was loaded.
Any suggestions?
Thanks
"Tibor Karaszi" wrote:

> No, what you need to do is to always have an empty partition "at the end".
And when you want to
> split, you split so you have two empty partitions. I.e., there will be no
data movement when you
> split one empty partition into two empty partitions. Same basic principle
goes for when you purge
> data (if you are going to do that). In other words, never populate your "u
nbound"/"edge" partitions.
>|||I'm not sure I follow you, Bernard...

> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full
This is what I have problems with. How can you say "until full" for somethin
g which is unrestricted?
I assume that the values you add (what you are partitioning over) are either
increasing or
decreasing. Otherwise, you wouldn't know that they go to the "last" or "firs
t" partition. I also
assume that they are restricted in some sense, else you wouldn't be able to
do a SPLIT later on.
Based on above, it seems that the values might as well go into a predefined
unrestricted partition.
But perhaps it is just me being dense (wouldn't be the first time ;-) ).
To the best of my knowledge, the partition that you SPLIT or partitions that
you MERGE need to be
empty to ensure meta-data only operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in mess
age
news:DF45FCAC-2A4B-4481-927C-A17485CEC65C@.microsoft.com...
> Thanks for the reply, Tibor.
> That leads to a couple of follow up questions:
> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full, and then
> create a new partition and adjust the ranges so new data flows into it.
> This is the strategy that I would like to use: we want to be able to load
> data into "bucket" (last partition) where we always fill the last bucket
> until (nearly) full, then start filling the next bucket. Each bucket is
> sized to hold a reasonable amount of data (around 1 billion rows, or
> thereabouts), but we can't accurately predict when we will fill a bucket.
> We will eventually switch out then delete old data (once it is 7 years aft
er
> it was loaded) - but our date partitioning is less to do with the date tha
t
> the data is for, and more to do with the date it was loaded.
> Any suggestions?
> Thanks
>
> "Tibor Karaszi" wrote:
>
>|||> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full, and then
> create a new partition and adjust the ranges so new data flows into it.
Regardless of the RANGE specification, you have the same issue. Like Tibor
mentioned, if you want to split a partition without data movement, plan so
that the last partition is always empty.

> This is the strategy that I would like to use: we want to be able to load
> data into "bucket" (last partition) where we always fill the last bucket
> until (nearly) full, then start filling the next bucket. Each bucket is
> sized to hold a reasonable amount of data (around 1 billion rows, or
> thereabouts), but we can't accurately predict when we will fill a bucket.
For a sliding window, I suggest you create your partition function to allow
for 2 empty (n and n - 1) partitions. Once you start filling the second
from last one (n - 1), split the last partition (n, which is still empty) to
prepare for the next time around. When you are ready to delete/archive the
oldest partition, simply switch out the first partition and merge it with
the second.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bernard Sheppard" <BernardSheppard@.discussions.microsoft.com> wrote in
message news:DF45FCAC-2A4B-4481-927C-A17485CEC65C@.microsoft.com...
> Thanks for the reply, Tibor.
> That leads to a couple of follow up questions:
> With RANGE LEFT, it would appear that the edge partition can't ever have
> data (if I want to be able to quickly change ranges), which means that I
> can't have a strategy where I fill an edge partition until full, and then
> create a new partition and adjust the ranges so new data flows into it.
> This is the strategy that I would like to use: we want to be able to load
> data into "bucket" (last partition) where we always fill the last bucket
> until (nearly) full, then start filling the next bucket. Each bucket is
> sized to hold a reasonable amount of data (around 1 billion rows, or
> thereabouts), but we can't accurately predict when we will fill a bucket.
> We will eventually switch out then delete old data (once it is 7 years
> after
> it was loaded) - but our date partitioning is less to do with the date
> that
> the data is for, and more to do with the date it was loaded.
> Any suggestions?
> Thanks
>
> "Tibor Karaszi" wrote:
>
>|||Hi Tibor, thanks, you've answered my question (with your very last statement
).
Cheers.
"Tibor Karaszi" wrote:

> I'm not sure I follow you, Bernard...
>
> This is what I have problems with. How can you say "until full" for someth
ing which is unrestricted?
>
Physically full - each partition will (almost) fill a mounted disk (a SAN
LUN). Whilst I could distribute a filegroup across multiple LUNs, it makes
for more difficult management, and makes life more difficult for piecemeal
restores. If I can stick with a single filegroup per LUN, then my
file/filegroup/partitioning strategy can be automated.
I'm partitioning for a few reasons - recoverability, ability to archive
(read delete, or alternatively move to slow storage (e.g. read only on NTFS
compression), ability to restore subsets of data (e.g. just data loaded in
the last x month) into other environments, etc, ability to utilise multiple
LUNs on a SAN for maximum parallelism, etc.
My main table will grow to several terabytes and hundreds of billions of
rows. Distributing this (evenly) over many small LUNs will allow maintenanc
e
activities such as backup, restore, integrity checks, etc to run at maximum
speed.
I used to use DPVs on SQL Server 2000.

> I assume that the values you add (what you are partitioning over) are eith
er increasing or
> decreasing. Otherwise, you wouldn't know that they go to the "last" or "fi
rst" partition. I also
> assume that they are restricted in some sense, else you wouldn't be able t
o do a SPLIT later on.
>
Absolutely correct. My raw value that I am partitioning over is an identity
column that relates the data back to the batch in which it was recieved -
this ID is then used as part of the PK (and partition scheme input) for the
lower level data.
This means that I am only ever inserting into the last partition (and, for
that matter, physically in order due to the clustered index).

> Based on above, it seems that the values might as well go into a predefine
d unrestricted partition.
> But perhaps it is just me being dense (wouldn't be the first time ;-) ).
Yes, except I then can't partition across (fixed) size LUNSs.

> To the best of my knowledge, the partition that you SPLIT or partitions
that you MERGE need to be
> empty to ensure meta-data only operations.
That has hit the nail on the head, and tests I've done have confirmed this.
I've read (and re-read) BOL, and whilst it does describe where the new range
value goes (e.g. on LEFT, becomes part of the new partition), and it
indicates that if you split an existing range, data that falls into the new
range gets moved, it doesn't seem to state (at least clearly enough for me t
o
tell) that, even if there is no data at the split point, if there is any dat
a
in the partition being split, it is not a meta-data only operation.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/