January arrives whether the partition job is ready or not. A new year partition keeps fresh rows out of the catchall range and makes later retention work far less surprising.
Check the Boundary You Have
I check the partition function before I touch a scheme. The boundary values live in sys.partition_range_values, while the function tells you whether equality belongs to the right or left partition. That detail decides which partition must be empty for the split. Compare boundary values with the next January boundary using the same data type as the function parameter. Do not compare a date function to a text label and assume the conversion is harmless. A month-based scheme needs monthly boundaries, not only one boundary per year.
The query below lists date-based partition functions and their latest boundary. Review the function definition and the table mapping before acting. A function can support several tables and indexes through multiple partition schemes. A split affects all of them. If the function has no boundaries yet, the outer join still keeps it visible.
SELECT pf.name AS partition_function,
pf.boundary_value_on_right,
MAX(TRY_CONVERT(date, prv.value)) AS latest_boundary
FROM sys.partition_functions AS pf
JOIN sys.partition_parameters AS pp ON pp.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS prv
ON prv.function_id = pf.function_id
WHERE TYPE_NAME(pp.system_type_id) IN (N'date', N'datetime', N'datetime2')
GROUP BY pf.name, pf.boundary_value_on_right
ORDER BY pf.name;Find a Missing New Year Partition
A date at the first instant of January is a common RANGE RIGHT boundary. Confirm the exact calendar and time zone in your design. A business year starting on another date needs that date instead. Check for a matching boundary, not only a maximum value. The highest boundary can sit years ahead while a middle year was missed during an earlier repair. I also inspect rows near the edge. If January data already reached the last partition, moving it during a split can produce logging and locking that the routine empty split was meant to avoid.
Ask what retention process expects from the new range. A sliding window usually needs a clean future partition and an empty oldest partition for SWITCH operations. Adding a boundary without checking both ends can move the problem into next year's cleanup. The calendar is not the only dependency here.
DECLARE @NextYear date = DATEFROMPARTS(YEAR(SYSDATETIME()) + 1, 1, 1);
SELECT pf.name AS partition_function
FROM sys.partition_functions AS pf
JOIN sys.partition_parameters AS pp ON pp.function_id = pf.function_id
WHERE TYPE_NAME(pp.system_type_id) = N'date'
AND NOT EXISTS
(SELECT 1 FROM sys.partition_range_values AS prv
WHERE prv.function_id = pf.function_id
AND TRY_CONVERT(date, prv.value) = @NextYear);Prepare the Destination Filegroup
Before SPLIT RANGE, a partition scheme needs a NEXT USED filegroup. A scheme created with ALL TO ([PRIMARY]) marks PRIMARY as next used once, and the first split uses up that mark. In my test, the second split on such a scheme stopped with warning 7710 and changed nothing. Inspect every scheme that uses the function and decide where the future range belongs. Make sure the filegroup is online, writable, monitored, and backed up under your recovery plan. Do not set NEXT USED to a filegroup just because it has a pleasant name. The partition can remain there for years.
Set NEXT USED for every affected scheme, then split the function once. The commands below show the shape; replace the sample names only after confirming your own mapping. Run them in a controlled maintenance window and review the resulting partition counts. A failed split should be investigated before a job retries it. Blind retries make a simple calendar change look like a storage mystery.
ALTER PARTITION SCHEME SalesByYearScheme NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION SalesByYearFunction()
SPLIT RANGE ('2027-01-01');
Keep the New Year Partition Empty
An empty range makes the split quick to reason about and keeps data movement out of the job. For RANGE RIGHT, the range immediately to the right of the highest boundary must be empty before a new highest boundary is added. Inspect row counts through sys.dm_db_partition_stats for every heap or clustered index using the scheme. If the range is occupied, stop and plan a deliberate data movement. An automatic December job should not improvise its way through a large table.
I treat unexpected rows as a data-quality signal. They can indicate future-dated values, a bad default, or a previous missed split. Fixing the boundary without understanding those rows leaves the same surprise waiting for the next run. Partition maintenance rewards a little suspicion.
Put December on the Job Calendar
Create a SQL Agent job that computes the next January date at run time and checks whether the boundary exists. It then verifies the target range is empty, prepares NEXT USED, and splits only when all checks pass. Schedule it early enough in December for review and retry before the holiday period. Make the step idempotent: a second run should say the boundary already exists and exit successfully. Record the chosen boundary and partition function in job output. Alert on failure through the usual operator path.
Use the following as the T-SQL step body after adding the empty-range preflight for your tables. Give the Agent job an annual December schedule and run it once manually in a restored copy. The example names one function and scheme; add every scheme tied to that function.
DECLARE @NextYear date = DATEFROMPARTS(YEAR(SYSDATETIME()) + 1, 1, 1);
IF NOT EXISTS
(
SELECT 1
FROM sys.partition_range_values AS prv
WHERE prv.function_id =
(SELECT function_id FROM sys.partition_functions
WHERE name = N'SalesByYearFunction')
AND TRY_CONVERT(date, prv.value) = @NextYear
)
BEGIN
ALTER PARTITION SCHEME SalesByYearScheme NEXT USED [PRIMARY];
DECLARE @ddl nvarchar(max) =
N'ALTER PARTITION FUNCTION SalesByYearFunction() SPLIT RANGE (''' +
CONVERT(nvarchar(10), @NextYear, 23) + N''');';
EXEC sys.sp_executesql @ddl;
PRINT N'Added boundary ' + CONVERT(nvarchar(10), @NextYear, 23);
END
ELSE
PRINT N'Boundary ' + CONVERT(nvarchar(10), @NextYear, 23) + N' already present';The new year partition should come from the date math, so do not hard-code a new literal every December. That is how the annual maintenance task becomes an annual outage rehearsal. The job should be reviewed after any schema change that adds another scheme to the function or changes the filegroup layout.
Guard Against a Second December Run
An Agent job must decide what to do when someone runs it manually after the scheduled run. Check the exact boundary first, then exit with a clear "already present" message. Do not issue SPLIT RANGE twice and let a duplicate-boundary error become the monitoring signal. Record the function name, scheme names, chosen filegroup, and next boundary in job output. I review that output in January as proof the automation did the work before data arrived. If a deployment creates a new partition scheme in November, update the job's scheme inventory before December. The job is only as complete as the list it checks.
Verify the New Year Partition Afterward
Query sys.partition_range_values again and inspect row counts on both sides of the new boundary. Insert a representative future date into a test table with the same function if you need a routing check. Use $PARTITION with the function name to see its destination, then roll the test back. Confirm that the SQL Agent job history records success and that the next scheduled run is present. A successful DDL statement alone does not prove the whole maintenance path is ready.
What will happen when the calendar rolls over? If you can answer with the boundary value, the destination filegroup, the empty range check, and the job schedule, the partition plan is in good shape. I put those four facts in the runbook. January is busy enough without discovering partition metadata by flashlight.
Related reading on this blog: Aligned and Non-Aligned Indexes for Partitioning and Script to Get Partition Info Using DMV.
A partition boundary is not a holiday chore, it is scheduled capacity for the next row.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
