10

It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :)

A few times I have ran into a weird scenario; when I run:

SELECT *
FROM ph.FileGroupDetail
ORDER BY partition_number
Go

There is the same filegroup showing twice with 2 different partition_numbers, 1 correctly at the end with a range value, the other at the start with a null range_value.

click here for enlarge image

enter image description here

Couple of questions:

  1. How is this happening, where have I gone wrong?

  2. How do I resolve the issue, that means how to get rid of the one at the start as I already have an empty partition at the beginning.

I've tried deleting the file (worked when it was empty) and filegroup, but filegroup said it couldn't be deleted.

Can someone explain please how this has happened and how to get rid of the partition 2 entry?

  • 3
    Steve, can you post your partition scheme and function? – PseudoToad Sep 22 '15 at 21:07
  • @steve - really would be useful for you to post that partition scheme and function as Gizmo requested. – Thronk Nov 6 '15 at 3:00
  • 1
    Can you expose also partition_id from sys.partitions? – user_0 Nov 6 '15 at 10:32
3
+50

The results indicate at some point an explicit NULL partition boundary was added to the function when the partition scheme NEXT USED filegroup was set to DailyAlbertFG30. Also, I don't see DailyAlbertFG2 used. Perhaps there was once a partition on that filegroup that was subsequently merged.

Below is a script that shows how a FG30 partition with the NULL boundary can be created. The NULL boundary might have been added accidentally.

CREATE PARTITION FUNCTION DailyAlbertPF1 (datetime2(3)) AS RANGE RIGHT FOR VALUES();
GO
CREATE PARTITION SCHEME DailyAlbertPS1 AS PARTITION DailyAlbertPF1 ALL TO ([DailyAlbertFG1]);
GO
CREATE TABLE dbo.FactAgentAlbertPortalSessionEntries
    (
      DateTimeColumn datetime2(3)
    )
ON  DailyAlbertPS1(DateTimeColumn);
GO

DECLARE @FileGroupNumber int = 1;
DECLARE @DateTimeBoundary datetime2(3) = '2015-04-15T00:00:00.000';
DECLARE @SQL nvarchar(MAX);
WHILE @DateTimeBoundary <= '2015-05-14T00:00:00.000'
BEGIN
    SET @SQL = N'ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG' + CAST(@FileGroupNumber AS nvarchar(5)) + N';';
    EXEC(@SQL);
    ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
    SET @DateTimeBoundary = DATEADD(day, 1, @DateTimeBoundary);
    SET @FileGroupNumber += 1;
END;
--add NULL boundary on DailyAlbertFG30
SET @DateTimeBoundary = NULL;
ALTER PARTITION SCHEME DailyAlbertPS1 NEXT USED DailyAlbertFG30;
ALTER PARTITION FUNCTION DailyAlbertPF1() SPLIT RANGE(@DateTimeBoundary);
GO
|improve this answer|||||
0
  1. I don't know. This is not an unusual case per-se. You can freely assign partitions to filegroups. It does not have to be 1:1. Maybe you accidentally rebuilt to the wrong FG, or you had a bad configuration for a short period of time. This irregularity is nothing to worry about, but you can of course clean it up.
  2. You can get rid of one of the empty partitions by merging one of them into the other. There are great guides on the web on how to do this. This is easy to do and instant in this case. You can also move partitions to a desired FG by rebuilding them. Rebuilding empty partitions is instant.

When you are done cleaning up and you find empty FGs you can drop them as well.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.