Ola Hallengren Index maintenance on servers with Availability Groups

[mirzahabeebdba]

Ola Hallengren Index maintenance on servers with Availability Groups has failed in secondary node with below message.

Message
Executed as user: SRVR\SQLEng_SPArchO. ...ge 50000) Server: HDB-SP-SQL02\SQLSP02 [SQLSTATE 01000] (Message 50000) Version: 14.0.3401.7 [SQLSTATE 01000] (Message 50000) Edition: Enterprise Edition: Core-based Licensing (64-bit) [SQLSTATE 01000] (Message 50000) Platform: Windows [SQLSTATE 01000] (Message 50000) Procedure: [master].[dbo].[IndexOptimize] [SQLSTATE 01000] (Message 50000) Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 10, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = 1, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @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, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'N', @Execute = 'Y' [SQLSTATE 01000] (Message 50000) Version: 2022-01-02 13:58:13 [SQLSTATE 01000] (Message 50000) Source: https://ola.hallengren.com [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2022-07-12 18:00:02 [SQLSTATE 01000] (Message 50000) Database: [TestDB] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: FULL [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Message 50000) Availability group: Test02NTENT01 [SQLSTATE 01000] (Message 50000) Availability group role: SECONDARY [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2022-07-12 18:00:02 [SQLSTATE 01000] (Message 50000) Database: [DB_202203] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: FULL [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Message 50000) Availability group: Test02NTENT01 [SQLSTATE 01000] (Message 50000) Availability group role: SECONDARY [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2022-07-12 18:00:02 [SQLSTATE 01000] (Message 50000) Database: [IEXECUTION_DropOffSite] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: FULL [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Message 50000) Availability group: Test02NTENT01 [SQLSTATE 01000] (Message 50000) Availability group role: SECONDARY [SQLSTATE 01000] (Message 50000) [SQLSTATE 01000] (Message 50000) Date and time: 2022-07-12 18:00:02 [SQLSTATE 01000] (Message 50000) Database: [TESTDB] [SQLSTATE 01000] (Message 50000) State: ONLINE [SQLSTATE 01000] (Message 50000) Standby: No [SQLSTATE 01000] (Message 50000) Updateability: READ_WRITE [SQLSTATE 01000] (Message 50000) User access: MULTI_USER [SQLSTATE 01000] (Message 50000) Recovery model: FULL [SQLSTATE 01000] (Message 50000) Is accessible: Yes [SQLSTATE 01000] (Message 50000) Availability group: Test02... The step failed.

[#2]

The table CommandLog is missing

---

A pretty clear error message, isn't it?

See https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

for how to create the table on your secondary server.

Olaf

[#3]

I have added already table CommandLog still same issue coming

[#4]

I have updated the error above can you check it once.