CODEWITHSUNDEEP
sqlazure-sql-database
Steve Meyer
Steve Meyer

Reputation: 41

"Cannot bulk load. Sort order incorrect..." while rebuilding index partition

We are running Ola Hallengrens database maintenance scripts against several databases in Azure. The last few week we ran into the same error on execution. It persists if we execute the command on our own.

The table is versioned, and partitioned on a computed column. Both were recently added around the time the error first appeared.

Command:

ALTER INDEX [IX_AdvertisementUnits_LastModified] ON [dbo].[AdvertisementUnits] REBUILD PARTITION = 46 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Error message:

Cannot bulk load. The bulk data stream was incorrectly specified as sorted or the data violates a uniqueness constraint imposed by the target table. Sort order incorrect for the following two rows: primary key of first row: (2020-06-10 17:37:00.7631521 +02:00, 2020-03-02, 2020-03-03, 37414493), primary key of second row: (2020-04-05 19:10:25.4284962 +02:00, 2020-04-06, 2020-04-05, 38193312).

Azure compatibility_level: 140

I could not find any solution that got close to our problem, I have not tried to rebuild the entire index because I might lose the ability to reproduce the problem.

Upvotes: 0

Views: 3213

Answers (1)

Leon Yue
Leon Yue

Reputation: 16401

Congratulations you solved the issue in the end:

"In the end we just recreated the index with 'on [primary]' so the index is not partitioned. This "solved" the problem since the index should not have been partitioned in the first place."

@Steve Meyer provided more details in the comments.

I help you post it as an answer and this can be beneficial to other community members.

Upvotes: 1

Related Questions