search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.88k stars 744 forks source link

Index Optimize job by Ola fails for a Database that has computed columns #156

Closed Hariadigoppula closed 5 years ago

Hariadigoppula commented 5 years ago

I am looking for a resolution as to why the Ola Index Optimize job Is failing for a particular database that has computed columns.

I am using the latest maintenance jobs available on ola website.

Below is one of the errors…

Date and time: 2018-10-26 12:54:15 Server: SERVER_NAME Version: 13.0.5026.0 Edition: Enterprise Edition (64-bit) Platform: Windows Procedure: [DBA_Utility].[dbo].[IndexOptimize] Parameters: @Databases = 'DATABASE_NAME', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y' Version: 2018-07-16 18:32:21 Source: https://ola.hallengren.com Date and time: 2018-10-26 12:54:15 Database: [DATABASE_NAME] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 12:55:24 Command: ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD PARTITION = 655 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 19100, Fragmentation: 96.9843 Msg 50000, Level 16, State 1, Server SERVER_NAME, Procedure CommandExecute, Line 194 Msg 4819, 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: (ServerName, Oct 11 2018 12:00AM, 650, PFIL, 2196, 412108, 0), primary key of second row: (fd9, Oct 12 2018 11:59PM, 651, PFIL, 2228, 530736, 2015494198). Outcome: Failed Duration: 00:00:25 Date and time: 2018-10-26 12:55:49

Below is the job history.

Date 10/26/2018 11:55:37 AM Log Job History (IndexOptimize - USER_DATABASES)

Step ID 1 Server Server_Name Job Name IndexOptimize - USER_DATABASES Step Name IndexOptimize - USER_DATABASES Duration 00:30:24 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message Executed as user: 1DC\svc-crp-prdsql. ...rsion: 13.0.5026.0 Edition: Enterprise Edition (64-bit) Platform: Windows Procedure: [DBA_Utility].[dbo].[IndexOptimize] Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y' Version: 2018-07-16 18:32:21 Source: https://ola.hallengren.com Date and time: 2018-10-26 11:55:37 Database: [DBA_Utility] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: SIMPLE Date and time: 2018-10-26 11:55:38 Database: [dcsc_umc] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 11:55:38 Database: [NSCORE] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 11:55:38 Database: [NSMESSAGING] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 11:55:39 Database: [NSMETRICS] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 11:55:39 Database: [NSSCHEDULE] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 11:55:39 Database: [NSWEB] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: SIMPLE Date and time: 2018-10-26 11:55:40 Database: [SCSPDB] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Date and time: 2018-10-26 11:56:47 Command: ALTER INDEX [CSPEVENT_PK] ON [SCSPDB].[dbo].[CSPEVENT] REBUILD PARTITION = 659 WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF) Comment: ObjectType: Table, IndexType: Clustered, ImageText: Yes, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 70420, Fragmentation: 78.3045 Outcome: Succeeded Duration: 00:00:12 Date and time: 2018-10-26 11:56:59 Date and time: 2018-10-26 11:56:59 Command: ALTER INDEX [CSPEVENT_PK] ON [SCSPDB].[dbo].[CSPEVENT] REBUILD PARTITION = 660 WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF) Comment: ObjectType: Table, IndexType: Clustered, ImageText: Yes, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 67470, Fragmentation: 95.681 Outcome: Succeeded Duration: 00:00:12 Date and time: 2018-10-26 11:57:11 Date and time: 2018-10-26 11:57:11 Command: ALTER INDEX [CSPEVENT_PK] ON [SCSPDB].[dbo].[CSPEVENT] REBUILD PARTITION = 661 WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF) Comment: ObjectType: Table, IndexType: Clustered, ImageText: Yes, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 54587, Fragmentation: 96.021 Outcome: Succeeded Duration: 00:00:10 Date and time: 2018-10-26 11:57:21 Date and time: 2018-10-26 11:57:21 Command: ALTER INDEX [CSPEVENT_PK] ON [SCSPDB].[dbo].[CSPEVENT... Process Exit Code 1. The step failed.

When I execute Rebuild/ReOrg scripts manually, it runs fine. Would really appreciate your inputs on this.

Hariadigoppula commented 5 years ago

Adding to it..

I ran the below exact same command manually, it gave the same error.

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD PARTITION = 655 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

olahallengren commented 5 years ago

This looks like a bug in SQL Server.

Could you try these combinations:

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD PARTITION = 655 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD PARTITION = 655 WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)

What fails and what works?

Hariadigoppula commented 5 years ago

Hi Ola,

Please see the results below. The first one fails and the rest works.

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD PARTITION = 655 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

Msg 4819, Level 16, State 1, Line 1 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: (ServerName, Oct 17 2018 12:00AM, 656, PPRC, 2196, 1617378, 0), primary key of second row: (fd9, Oct 18 2018 11:59PM, 657, PFIL, 2228, 816353, 1620443190). The statement has been terminated.

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD PARTITION = 655 WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)

Commands completed successfully.

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

Commands completed successfully.

ALTER INDEX [IDX_CSPEVENT_AGENTNAME_ASC] ON [DATABASE_NAME].[dbo].[CSPEVENT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)

Commands completed successfully.

Could you please suggest what would be the ideal command to use for the entire database.

Regards, Hari

olahallengren commented 5 years ago

Could you please install the latest CU, and then do the tests again.

olahallengren commented 5 years ago

It looks like this is a known issue in SQL Server: https://support.microsoft.com/en-my/help/3213683/fix-unable-to-rebuild-the-partition-online-for-a-table-that-contains-a

To activate this hotfix, you have to enable the trace flag (TF) 176. This issue is fixed in the following cumulative updates for SQL Server:

I can see that you are already on SQL Server 2016 SP2. Could you try enabling the trace flag that is mentioned in the kb article?

olahallengren commented 5 years ago

You can also use @SortInTempdb = 'Y' as a workaround.

Hariadigoppula commented 5 years ago

Thanks Ola, for sharing the information.

Regards, Hari

From: Ola Hallengren notifications@github.com Sent: Saturday, December 29, 2018 1:16 PM To: olahallengren/sql-server-maintenance-solution sql-server-maintenance-solution@noreply.github.com Cc: Adigoppula, Harikrishna Harikrishna.Adigoppula@firstdata.com; Author author@noreply.github.com Subject: Re: [olahallengren/sql-server-maintenance-solution] Index Optimize job by Ola fails for a Database that has computed columns (#156)

It looks like this is a known issue in SQL Server: https://support.microsoft.com/en-my/help/3213683/fix-unable-to-rebuild-the-partition-online-for-a-table-that-contains-ahttps://urldefense.proofpoint.com/v2/url?u=https-3A__support.microsoft.com_en-2Dmy_help_3213683_fix-2Dunable-2Dto-2Drebuild-2Dthe-2Dpartition-2Donline-2Dfor-2Da-2Dtable-2Dthat-2Dcontains-2Da&d=DwMFaQ&c=ewHkv9vLloTwhsKn5d4bTdoqsmBfyfooQX5O7EQLv5TtBZ1CwcvjU063xndfqI8U&r=tQjkAoKXGdA1sgj52wszFu7VMr6vd3oHMv1yhtA7tnHqx8nLjpIoBA7N-KGvJPVz&m=sa3iO_bZr22rO0uEAbhvpV3PrGvvq5EafFaXTiHMzBE&s=BMRP9TWC_i6994msARbSzLQ0Ht1HmHW6SQcY3-IPwro&e=

To activate this hotfix, you have to enable the trace flag (TF) 176. This issue is fixed in the following cumulative updates for SQL Server:

I can see that you are already on SQL Server 2016 SP2. Could you try enabling the trace flag that is mentioned in the kb article?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_olahallengren_sql-2Dserver-2Dmaintenance-2Dsolution_issues_156-23issuecomment-2D450513910&d=DwMFaQ&c=ewHkv9vLloTwhsKn5d4bTdoqsmBfyfooQX5O7EQLv5TtBZ1CwcvjU063xndfqI8U&r=tQjkAoKXGdA1sgj52wszFu7VMr6vd3oHMv1yhtA7tnHqx8nLjpIoBA7N-KGvJPVz&m=sa3iO_bZr22rO0uEAbhvpV3PrGvvq5EafFaXTiHMzBE&s=VHX4BjiwIdxIVWNtC6ndeiVI3EJqz2F76XM_sV_2Lz4&e=, or mute the threadhttps://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_AqhaGRzwXToWF4wQvlTF466LczUbvn5bks5u979tgaJpZM4YALjd&d=DwMFaQ&c=ewHkv9vLloTwhsKn5d4bTdoqsmBfyfooQX5O7EQLv5TtBZ1CwcvjU063xndfqI8U&r=tQjkAoKXGdA1sgj52wszFu7VMr6vd3oHMv1yhtA7tnHqx8nLjpIoBA7N-KGvJPVz&m=sa3iO_bZr22rO0uEAbhvpV3PrGvvq5EafFaXTiHMzBE&s=ML7toSav0qxEvePZCcAfESpZEbIgCt_k8IgCMQHbhos&e=.

The information in this message may be proprietary and/or confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify First Data immediately by replying to this message and deleting it from your computer.

nicknaz2000 commented 4 years ago

I am getting the following error running Ola's Index Optimization job for a database in Availabiliy Group. Command: ALTER INDEX [IX_CifsEvents_2019-12-22_DirID] ON [calnasprd01].[dbo].[CifsEvents_2019-12-22] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Tabl... Process Exit Code 1. The step failed.

Command: ALTER INDEX [PKC_SDT_DerivedRelationsAll] ON [calfileprd01].[dbo].[SDT_DerivedRelationsAll] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore:... Process Exit Code 1. The step failed.

The version of SQL I have is Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64) Jan 10 2018 15:52:08 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Please advise

olahallengren commented 4 years ago

@nicknaz2000, please create a new issue for this, and also attach the complete output.

diablojeep commented 1 year ago

I have a filtered index on a hash partitioned column. I know, kind of obscure case, right? Well the workaround for sorting in tempdb only works if I run the rebuild from the SQL Server generated rebuild. It has one and only one command for this index that has like 6 partitions. When I run it via IndexOptimize, it separates the calls for 1 per partition and it fails on every one even though they are rebuilt in tempdb.

Works via MS code to rebuild: USE [slsclient] GO ALTER INDEX [ncidx.slsclient.cdata.filtered] ON [dbo].[CDATA] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO

Fails:

Date and time: 2023-03-02 07:43:20 Database context: [slsclient] Command: ALTER INDEX [ncidx.slsclient.cdata.filtered] ON [dbo].[CDATA] REBUILD PARTITION = 1 WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 3129, Fragmentation: 1.72579 Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 234 [Batch Start Line 0] Msg 4819, 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: (9266593:64611152 , swinglifestyle22 , 4214458, 1), primary key of second row: (134959394:64638294 , swinglifestyle22 , 4056788, 2). Outcome: Failed Duration: 00:00:04 Date and time: 2023-03-02 07:43:24

Date and time: 2023-03-02 07:43:24 Database context: [slsclient] Command: ALTER INDEX [ncidx.slsclient.cdata.filtered] ON [dbo].[CDATA] REBUILD PARTITION = 2 WITH (SORT_IN_TEMPDB = ON, ONLINE = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 3128, Fragmentation: 1.5665 Msg 50000, Level 16, State 1, Procedure CommandExecute, Line 234 [Batch Start Line 0] Msg 4819, 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: (87655065:36045089 , swinglifestyle22 , 5274623, 2), primary key of second row: (135549653:89500075 , swinglifestyle22 , 3475551, 3). Outcome: Failed Duration: 00:00:05 Date and time: 2023-03-02 07:43:29

etc.... etc... for each other partition

on Microsoft SQL Server 2016 (SP2-CU16) (KB5000645) - 13.0.5882.1 (X64) Jan 25 2021 21:40:32 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)