olahallengren
commented
4 years ago The output in the SQL Server Agent Job History does not contain the error. Could you please configure an output - file, if you don't already have it, run the job, and then attach the output - file to the issue.
Hello. Running the following job in the last two weeks we have gotten two errors posted. It doesn't look like there is a conflict of jobs running. Notice that the job has been running longer than previous weeks on both instances.
sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "EXECUTE [dbo].[IndexOptimize] @Databases = 'AVAILABILITY_GROUP_DATABASES', @FragmentationLow=NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @LogToTable = 'Y'" -b
-------Getting the following error in the output Date 12/28/2019 12:00:00 AM Log Job History (_DBA_USER_DATABASES_IndexOptimize)
Step ID 1 Server CALSQLASPRD03 Job Name _DBA_USER_DATABASES_IndexOptimize Step Name _DBA_USER_DATABASES_AG_IndexOptimize Duration 03:42:09 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message Executed as user: CPINET\05SQLASPRD. ... Version: 12.0.5571.0 Edition: Enterprise Edition: Core-based Licensing (64-bit) Procedure: [msdb].[dbo].[IndexOptimize] Parameters: @Databases = 'AVAILABILITY_GROUP_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @AvailabilityGroups = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y' Source: https://ola.hallengren.com Date and time: 2019-12-28 00:00:03 Database: [CALCPIPRD01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-28 00:00:33 Command: ALTER INDEX [IX_Parent] ON [CALCPIPRD01].[dbo].[SDT_DerivedRelations] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 2172, Fragmentation: 85.6354 Outcome: Succeeded Duration: 00:00:03 Date and time: 2019-12-28 00:00:36 Date and time: 2019-12-28 00:00:37 Database: [calfileprd01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-28 00:01:48 Command: ALTER INDEX [IX_Parent] ON [calfileprd01].[dbo].[SDT_DerivedRelations] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 8821, Fragmentation: 93.0507 Outcome: Succeeded Duration: 00:00:06 Date and time: 2019-12-28 00:01:54 Date and time: 2019-12-28 00:02:15 Command: ALTER INDEX [ix_ParentID] ON [calfileprd01].[dbo].[SDT_DirIDs] REORGANIZE WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1291, Fragmentation: 5.65453 Outcome: Succeeded Duration: 00:01:20 Date and time: 2019-12-28 00:03:35 Date and time: 2019-12-28 00:04:09 Database: [CALKRN8ADEV01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-28 00:04:42 Database: [CALKRN8DBDEV01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-28 00:05:09 Database: [CALKRN8DBUAT01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-28 00:05:26 Database: [calnasprd01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-28 00:07:04 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.
-------the week before. We go the following error. Date 12/21/2019 12:00:00 AM Log Job History (_DBA_USER_DATABASES_IndexOptimize)
Step ID 1 Server CALSQLASPRD03 Job Name _DBA_USER_DATABASES_IndexOptimize Step Name _DBA_USER_DATABASES_AG_IndexOptimize Duration 04:43:47 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message Executed as user: CPINET\05SQLASPRD. ... Version: 12.0.5571.0 Edition: Enterprise Edition: Core-based Licensing (64-bit) Procedure: [msdb].[dbo].[IndexOptimize] Parameters: @Databases = 'AVAILABILITY_GROUP_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @AvailabilityGroups = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y' Source: https://ola.hallengren.com Date and time: 2019-12-21 00:00:03 Database: [CALCPIPRD01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-21 00:00:28 Command: ALTER INDEX [IX_Parent] ON [CALCPIPRD01].[dbo].[SDT_DerivedRelations] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 2173, Fragmentation: 85.3659 Outcome: Succeeded Duration: 00:00:11 Date and time: 2019-12-21 00:00:39 Date and time: 2019-12-21 00:00:42 Database: [calfileprd01] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible: Yes Recovery model: FULL Availability group: VaronisAG Availability group role: PRIMARY Date and time: 2019-12-21 00:03:36 Command: ALTER INDEX [PK_PT_EventsStats_detailed_1] ON [calfileprd01].[dbo].[PT_EventsStats_detailed_1] REBUILD PARTITION = 4 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1887, Fragmentation: 89.3482 Outcome: Succeeded Duration: 00:00:08 Date and time: 2019-12-21 00:03:44 Date and time: 2019-12-21 00:03:45 Command: ALTER INDEX [IDX_PT_EventsStats_new_1_time] ON [calfileprd01].[dbo].[PT_EventsStats_new_1] REORGANIZE PARTITION = 4 WITH (LOB_COMPACTION = ON) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1050, Fragmentation: 10.0952 Outcome: Succeeded Duration: 00:00:41 Date and time: 2019-12-21 00:04:26 Date and time: 2019-12-21 00:04:27 Command: ALTER INDEX [PK_PT_EventsStats_new_1] ON [calfileprd01].[dbo].[PT_EventsStats_new_1] REBUILD PARTITION = 4 WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1297, Fragmentation: 92.0586 Outcome: Succeeded Duration: 00:00:02 Date and time: 2019-12-21 00:04:29 Date and time: 2019-12-21 00:04:42 Command: ALTER INDEX [IX_Parent] ON [calfileprd01].[dbo].[SDT_DerivedRelations] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON) Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 8815, Fragmentation: 93.0346 Outcome: Succeeded Duration: 00:00:18 Date and time: 2019-12-21 00:05:00 Date and time: 2019-12-21 00:05:00 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.
version of the SQL 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)