Error adding partition boundary

Discussion:

(too old to reply)

nickdu

2009-05-06 17:14:01 UTC

My managed application which runs once a day at 10:00 pm to do some
partitioning maintenance on our DB often encounters an exception. It also
sometimes succeeds. When it encounters the error it actually seems as if it
successfully added the partition boundary value yet decides to fail for some
reason. I say this because when I run the application again it shows the
boundary value exists. This application dumps out the boundary values that
exist and the partitions and # of rows in those partitions before it makes
any modifications. That's how come I can say that the boundary value was
added even though the previous run encountered an exception.

The exception I encounter is:

"Failed due to the following Error: System.Data.SqlClient.SqlException:
Duplicate range boundary values are not allowed in partition function
boundary values list. The boundary value being added is already present at
ordinal 46 of the boundary value list.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
.
.
."

before doing any modifications the tool dumped the current partition
information as follows:

boundaries:

0: 3/30/2009 12:00:00 AM
1: 3/31/2009 12:00:00 AM
2: 4/1/2009 12:00:00 AM
3: 4/2/2009 12:00:00 AM
4: 4/3/2009 12:00:00 AM
5: 4/4/2009 12:00:00 AM
6: 4/5/2009 12:00:00 AM
7: 4/6/2009 12:00:00 AM
8: 4/7/2009 12:00:00 AM
9: 4/8/2009 12:00:00 AM
10: 4/9/2009 12:00:00 AM
11: 4/10/2009 12:00:00 AM
12: 4/11/2009 12:00:00 AM
13: 4/12/2009 12:00:00 AM
14: 4/13/2009 12:00:00 AM
15: 4/14/2009 12:00:00 AM
16: 4/15/2009 12:00:00 AM
17: 4/16/2009 12:00:00 AM
18: 4/17/2009 12:00:00 AM
19: 4/18/2009 12:00:00 AM
20: 4/19/2009 12:00:00 AM
21: 4/20/2009 12:00:00 AM
22: 4/21/2009 12:00:00 AM
23: 4/22/2009 12:00:00 AM
24: 4/23/2009 12:00:00 AM
25: 4/24/2009 12:00:00 AM
26: 4/25/2009 12:00:00 AM
27: 4/26/2009 12:00:00 AM
28: 4/27/2009 12:00:00 AM
29: 4/28/2009 12:00:00 AM
30: 4/29/2009 12:00:00 AM
31: 4/30/2009 12:00:00 AM
32: 5/1/2009 12:00:00 AM
33: 5/2/2009 12:00:00 AM
34: 5/3/2009 12:00:00 AM
35: 5/4/2009 12:00:00 AM
36: 5/5/2009 12:00:00 AM
37: 5/6/2009 12:00:00 AM
38: 5/7/2009 12:00:00 AM
39: 5/8/2009 12:00:00 AM
40: 5/9/2009 12:00:00 AM
41: 5/10/2009 12:00:00 AM
42: 5/11/2009 12:00:00 AM
43: 5/12/2009 12:00:00 AM
44: 5/13/2009 12:00:00 AM
45: 5/14/2009 12:00:00 AM

partitions:

0: 232
1: 8
2: 9
3: 11
4: 11
5: 11
6: 11
7: 11
8: 42
9: 22
10: 13
11: 13
12: 13
13: 12
14: 13
15: 13
16: 15
17: 16
18: 18
19: 16
20: 18
21: 16
22: 17
23: 28
24: 29
25: 41
26: 37
27: 44
28: 20
29: 23
30: 71
31: 75
32: 224475
33: 631345
34: 134608
35: 130413
36: 649225
37: 640533
38: 0
39: 0
40: 0
41: 0
42: 0
43: 0
44: 0
45: 0
46: 0

The part that failed was the code which attempted to add another future day,
5/15/2009. However when I run the tool again it shows that boundary value
5/15/2009 exists and my tool is the only code which should be modifying the
partitions.

The code I use to add a boundary value is:

private void AddBoundary(DateTime date)
{
string sql = "alter partition scheme SlidingWindow NEXT USED [Data] " +
"alter partition function SlidingWindow() split range('{0}')";

sql = string.Format(sql, date.ToString("yyyy-MM-dd"));
Db.ExecuteNonQuery(this._connection, sql, 60);
}

Is there a known reason why this would succeed yet still throw an exception
indicating it failed?

--
Thanks,
Nick

***@community.nospam
remove "nospam" change community. to msn.com

Charles Wang [MSFT]

2009-05-07 08:52:50 UTC

Permalink

Hi Nick,
Welcome to Microsoft MSDN Managed Newsgroup Services. My name is Charles
Wang[MSFT]. It is my pleasure to work with you on this post.

From you description, I understand that your application encountered the
exception error regarding adding partition boundary values. Though you saw
the exception, the boundary value was already added per your observation.
If I have misunderstood, please let me know.

According to the error message, I think that it might happen in two
scenarios:
1. Your application submitted the same boundary value twice;
2. Your application submitted the boundary value once but that value
already exists before.

For further tracking the cause of this issue, I recommend that you add some
print related statements to print out the execution time, execution
parameters values and the existing boundary values before and after each
execution to a text file in your application. To check the existing
boundary values, you can run the following query:
SELECT pr.function_id, pf.[name], pr.boundary_id, pr.parameter_id,
pr.[value]
FROM sys.partition_range_values pr
JOIN sys.partition_functions pf
ON pr.function_id = pf.function_id
WHERE pf.[name]='your partition function name'

By printing out these values, we can have much information for analyzing
this issue.

Please do not hesitate to let me know if you have any other questions or
concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

nickdu

2009-05-07 12:13:01 UTC

Permalink

I don't believe either of the two scenarios you suggest happen. I believe I
only execute the SQL statement once to add the boundary value and that it
does not exist already. I believe the statement throws an exception yet
successfully adds the boundary value.
--
Thanks,
Nick

***@community.nospam
remove "nospam" change community. to msn.com

Post by Charles Wang [MSFT]
Hi Nick,
Welcome to Microsoft MSDN Managed Newsgroup Services. My name is Charles
Wang[MSFT]. It is my pleasure to work with you on this post.
From you description, I understand that your application encountered the
exception error regarding adding partition boundary values. Though you saw
the exception, the boundary value was already added per your observation.
If I have misunderstood, please let me know.
According to the error message, I think that it might happen in two
1. Your application submitted the same boundary value twice;
2. Your application submitted the boundary value once but that value
already exists before.
For further tracking the cause of this issue, I recommend that you add some
print related statements to print out the execution time, execution
parameters values and the existing boundary values before and after each
execution to a text file in your application. To check the existing
SELECT pr.function_id, pf.[name], pr.boundary_id, pr.parameter_id,
pr.[value]
FROM sys.partition_range_values pr
JOIN sys.partition_functions pf
ON pr.function_id = pf.function_id
WHERE pf.[name]='your partition function name'
By printing out these values, we can have much information for analyzing
this issue.
Please do not hesitate to let me know if you have any other questions or
concerns.
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Charles Wang [MSFT]

2009-05-08 04:30:45 UTC

Permalink

Hi Nick,
Thank you for your response.

I understand your concerns on this, however with the current limited
information, it is hard for us to judge what the exact reason of this issue
is. Though you do not believe that the situations I pointed would happen at
your side, may you try adding some print statements in your application or
stored procedures to help audit on this issue?

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: ***@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Dan Guzman

2009-05-07 12:50:21 UTC

Permalink

Post by nickdu
before doing any modifications the tool dumped the current partition

I see 46 partitions listed before the maintenance so it makes sense that
you'll get the error. Note that the ordinal listed in the error message is
1-based so it means that the code is trying to add boundary '5/14/2009
12:00:00 AM' and that already existed. You can run a trace to (dis)prove
the causes Charles suggested.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by nickdu
My managed application which runs once a day at 10:00 pm to do some
partitioning maintenance on our DB often encounters an exception. It also
sometimes succeeds. When it encounters the error it actually seems as if it
successfully added the partition boundary value yet decides to fail for some
reason. I say this because when I run the application again it shows the
boundary value exists. This application dumps out the boundary values that
exist and the partitions and # of rows in those partitions before it makes
any modifications. That's how come I can say that the boundary value was
added even though the previous run encountered an exception.
Duplicate range boundary values are not allowed in partition function
boundary values list. The boundary value being added is already present at
ordinal 46 of the boundary value list.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String
methodName, Boolean async)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
.
.
."
before doing any modifications the tool dumped the current partition
0: 3/30/2009 12:00:00 AM
1: 3/31/2009 12:00:00 AM
2: 4/1/2009 12:00:00 AM
3: 4/2/2009 12:00:00 AM
4: 4/3/2009 12:00:00 AM
5: 4/4/2009 12:00:00 AM
6: 4/5/2009 12:00:00 AM
7: 4/6/2009 12:00:00 AM
8: 4/7/2009 12:00:00 AM
9: 4/8/2009 12:00:00 AM
10: 4/9/2009 12:00:00 AM
11: 4/10/2009 12:00:00 AM
12: 4/11/2009 12:00:00 AM
13: 4/12/2009 12:00:00 AM
14: 4/13/2009 12:00:00 AM
15: 4/14/2009 12:00:00 AM
16: 4/15/2009 12:00:00 AM
17: 4/16/2009 12:00:00 AM
18: 4/17/2009 12:00:00 AM
19: 4/18/2009 12:00:00 AM
20: 4/19/2009 12:00:00 AM
21: 4/20/2009 12:00:00 AM
22: 4/21/2009 12:00:00 AM
23: 4/22/2009 12:00:00 AM
24: 4/23/2009 12:00:00 AM
25: 4/24/2009 12:00:00 AM
26: 4/25/2009 12:00:00 AM
27: 4/26/2009 12:00:00 AM
28: 4/27/2009 12:00:00 AM
29: 4/28/2009 12:00:00 AM
30: 4/29/2009 12:00:00 AM
31: 4/30/2009 12:00:00 AM
32: 5/1/2009 12:00:00 AM
33: 5/2/2009 12:00:00 AM
34: 5/3/2009 12:00:00 AM
35: 5/4/2009 12:00:00 AM
36: 5/5/2009 12:00:00 AM
37: 5/6/2009 12:00:00 AM
38: 5/7/2009 12:00:00 AM
39: 5/8/2009 12:00:00 AM
40: 5/9/2009 12:00:00 AM
41: 5/10/2009 12:00:00 AM
42: 5/11/2009 12:00:00 AM
43: 5/12/2009 12:00:00 AM
44: 5/13/2009 12:00:00 AM
45: 5/14/2009 12:00:00 AM
0: 232
1: 8
2: 9
3: 11
4: 11
5: 11
6: 11
7: 11
8: 42
9: 22
10: 13
11: 13
12: 13
13: 12
14: 13
15: 13
16: 15
17: 16
18: 18
19: 16
20: 18
21: 16
22: 17
23: 28
24: 29
25: 41
26: 37
27: 44
28: 20
29: 23
30: 71
31: 75
32: 224475
33: 631345
34: 134608
35: 130413
36: 649225
37: 640533
38: 0
39: 0
40: 0
41: 0
42: 0
43: 0
44: 0
45: 0
46: 0
The part that failed was the code which attempted to add another future day,
5/15/2009. However when I run the tool again it shows that boundary value
5/15/2009 exists and my tool is the only code which should be modifying the
partitions.
private void AddBoundary(DateTime date)
{
string sql = "alter partition scheme SlidingWindow NEXT USED [Data] " +
"alter partition function SlidingWindow() split range('{0}')";
sql = string.Format(sql, date.ToString("yyyy-MM-dd"));
Db.ExecuteNonQuery(this._connection, sql, 60);
}
Is there a known reason why this would succeed yet still throw an exception
indicating it failed?
--
Thanks,
Nick
remove "nospam" change community. to msn.com

nickdu

2009-05-07 14:22:06 UTC

Permalink

I left out some information (not purposely) that will address your comment
below such that it's not the reason. As you can guess, my maintenance
program which runs every night it maintaining a sliding window of partitions.
It's setup to keep 35 days of history and 10 days into the future (just in
case something happens where it doesn't run for a few days).

The maintenance program first removes the old partitions which are older
than 35 days then it adds the future partitions. Before doing this it dumps
out the current state of the boundaries/partitions. In the case where I
encountered the error it had already successfully, and without an exception,
removed the old date (3/30/2009). It then attempted to add 5/15/2009 and at
this point the statement to add that boundary value failed with the
exception, yet the boundary value appears to have been successfully added
because when I ran the program again, the initial part which dumps the
boundaries and partitions shows 5/15/2009.

Again, this program does run successfully at times. It always seems to run
successfully when I manually run it the next morning after a previous night's
failure. And it's run via a scheduled task so it's running under the same
security credentials both times.
--
Thanks,
Nick

***@community.nospam
remove "nospam" change community. to msn.com

Post by Dan Guzman

Post by nickdu
before doing any modifications the tool dumped the current partition

I see 46 partitions listed before the maintenance so it makes sense that
you'll get the error. Note that the ordinal listed in the error message is
1-based so it means that the code is trying to add boundary '5/14/2009
12:00:00 AM' and that already existed. You can run a trace to (dis)prove
the causes Charles suggested.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Dan Guzman

2009-05-08 11:41:50 UTC

Permalink

Post by nickdu
Again, this program does run successfully at times. It always seems to run
successfully when I manually run it the next morning after a previous night's
failure. And it's run via a scheduled task so it's running under the same
security credentials both times.

I'm not sure I understand how it runs successfully the next morning after a
previous night's failure. If I understood you correctly, all the actions
are correctly performed even when a failure occurs so I would expect a
legitimate duplicate boundary error during the rerun. Is it because a
different date is used for the rerun? In that case, I would expect the
error again during the next regularly scheduled run because the latest
boundary was already created.

I think the only way to get to the bottom of this is to run a SQL trace.
Perhaps the app sometimes runs the partition function split more than once
in some situations.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by nickdu
I left out some information (not purposely) that will address your comment
below such that it's not the reason. As you can guess, my maintenance
program which runs every night it maintaining a sliding window of partitions.
It's setup to keep 35 days of history and 10 days into the future (just in
case something happens where it doesn't run for a few days).
The maintenance program first removes the old partitions which are older
than 35 days then it adds the future partitions. Before doing this it dumps
out the current state of the boundaries/partitions. In the case where I
encountered the error it had already successfully, and without an exception,
removed the old date (3/30/2009). It then attempted to add 5/15/2009 and at
this point the statement to add that boundary value failed with the
exception, yet the boundary value appears to have been successfully added
because when I ran the program again, the initial part which dumps the
boundaries and partitions shows 5/15/2009.
Again, this program does run successfully at times. It always seems to run
successfully when I manually run it the next morning after a previous night's
failure. And it's run via a scheduled task so it's running under the same
security credentials both times.
--
Thanks,
Nick
remove "nospam" change community. to msn.com

Post by Dan Guzman

Post by nickdu
before doing any modifications the tool dumped the current partition

I see 46 partitions listed before the maintenance so it makes sense that
you'll get the error. Note that the ordinal listed in the error message is
1-based so it means that the code is trying to add boundary '5/14/2009
12:00:00 AM' and that already existed. You can run a trace to (dis)prove
the causes Charles suggested.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

nickdu

2009-08-05 12:40:01 UTC

Permalink

Just thought I would update you on the outcome. The results didn't make
sense based on the code, which I wrote. The only way the results made sense
is if there were two instances of my partitioning program running. And as it
turns out there were. They way the environments work here is that there are
"live" and "dark" pods. This provides production testing before releasing
and also fast switching to new release. At any rate, the support people were
familiar with keeping services stopped and disabled in the "dark" pod, but
they overlooked the scheduled tasks. My partitioning program ran as a
scheduled task each night and it was running in both the "live" and "dark"
pods.
--
Thanks,
Nick

***@community.nospam
remove "nospam" change community. to msn.com

Post by Dan Guzman

I'm not sure I understand how it runs successfully the next morning after a
previous night's failure. If I understood you correctly, all the actions
are correctly performed even when a failure occurs so I would expect a
legitimate duplicate boundary error during the rerun. Is it because a
different date is used for the rerun? In that case, I would expect the
error again during the next regularly scheduled run because the latest
boundary was already created.
I think the only way to get to the bottom of this is to run a SQL trace.
Perhaps the app sometimes runs the partition function split more than once
in some situations.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by Dan Guzman

Post by nickdu
before doing any modifications the tool dumped the current partition

I see 46 partitions listed before the maintenance so it makes sense that
you'll get the error. Note that the ordinal listed in the error message is
1-based so it means that the code is trying to add boundary '5/14/2009
12:00:00 AM' and that already existed. You can run a trace to (dis)prove
the causes Charles suggested.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Dan Guzman

2009-08-08 21:03:29 UTC

Permalink

Thanks for the update, Nick.
--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Post by nickdu
Just thought I would update you on the outcome. The results didn't make
sense based on the code, which I wrote. The only way the results made sense
is if there were two instances of my partitioning program running. And as it
turns out there were. They way the environments work here is that there are
"live" and "dark" pods. This provides production testing before releasing
and also fast switching to new release. At any rate, the support people were
familiar with keeping services stopped and disabled in the "dark" pod, but
they overlooked the scheduled tasks. My partitioning program ran as a
scheduled task each night and it was running in both the "live" and "dark"
pods.
--
Thanks,
Nick

Continue reading on narkive:

Search results for 'Error adding partition boundary' (Questions and Answers)

replies

Israel-Palestine?

started 2007-12-14 20:06:47 UTC

society & culture

replies

What was palestine before Israel was developed?

started 2007-12-15 16:42:30 UTC

politics

replies

why wont my new HDD use its full capacity?