Need suggestions on how to fix a broken table partitioning job

Question

Post reply

Need suggestions on how to fix a broken table partitioning job

DataGuy

SSCrazy

Points: 2029
More actions
September 30, 2021 at 8:04 pm

#3935237
Hey Folks,
I inherited a 12TB database that has a table partition job setup on it, however it appears that the job has been failing consistently for a few years. Here is the message when it tries to add a zero partition at the top:
```
Date9/26/2021 12:05:17 AM
LogJob History (Partition Maintence | ps_Signal_TwoYearsWeekly)

Step ID3
ServerPR0DAT089_C5G
Job NamePartition Maintence | ps_Signal_TwoYearsWeekly
Step NameAdding a zero partition buffer at the top
Duration00:00:36
Sql Severity15
Sql Message ID35346
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0

Message
Executed as user: PR0DAT0\srvc.MSSQL.Agent. SPLIT clause of ALTER PARTITION statement failed because the partition is not empty.  Only empty partitions can be split in when a columnstore index exists on the table. Consider an ALTER TABLE SWITCH operation from one of the nonempty partitions on the source table to a temporary staging table and then re-attempt the ALTER PARTITION SPLIT operation. Once completed, use ALTER TABLE SWITCH to move the staging table partition back to the original source table. [SQLSTATE 42000] (Error 35346).  The step failed.
```
The table itself has 18 billion rows and is 1.4TB in size. If the table were small in size we would just drop the CCI, re-run the job, and add the CCI back on, but we assume that by dropping the CCI the space would balloon back to normal size, and would likely run us out of space. The attached pic shows the partition info by filegroup, and as you can see the last partition is packed full of data because the job hasn't been working.
Looking for suggestions on how we might be able to get this working again, as admittedly I'm just now starting to get my hands dirty with table partitioning and still have a lot to learn. TIA!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16025 More actions · Answer 1

error message does tell you what to do -

create a staging table with same structure of the main table, with all required constraints/indexed
do a partition switch from the partition you need to split onto that staging table
split partition
and then do partition switch from staging back to main table to the corresponding partition

as a best practice for partition switching it is ALWAYS advisable to have an empty partition at the end of the chain - so that sometime before the need to use the last partition (or at the time where it would be needed, you do the split before you add data to that partition

DataGuy SSCrazy Points: 2029 More actions · Answer 2

Thanks for the reply, and the job is already doing that, or trying to at least. When it fails, its on the step where it's trying to add space on top, which is the error message I provided.

ALTER PARTITION FUNCTION pf_Signal_TwoYearsWeekly()
SPLIT RANGE (format(DATEADD(DAY, (((7*+2)+1)-DATEPART(dw, getdate())), getdate()), 'yyyyMMdd'))
GO

The part where we leave the lowest partition empty is at the end of the job, and looks like this

/*We always leave the lowest partition empty so we're not splitting on data.
So, let's take the 2nd lowest value and merge both 0 sides on that value.
*/
DECLARE@SQL VARCHAR(500)
SET@SQL = ''

SELECT@SQL =@SQL 
+'alter partition function pf_Signal_TwoYearsWeekly()
merge range ('''
+ convert(varchar(8),pr.value)
+ ''')'
fromsys.partition_functionspf
joinsys.partition_schemesps 
onpf.function_id = ps.function_id
joinsys.partition_range_valuespr 
onpr.function_id = pf.function_id
wherepf.name = 'pf_Signal_TwoYearsWeekly()'
andpr.boundary_id = 2

EXEC(@SQL)

This reply was modified 2 years, 7 months ago by DataGuy.
This reply was modified 2 years, 7 months ago by DataGuy.