search

olahallengren / sql-server-maintenance-solution

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

Ola Hallengren Index maintenance on servers with Availability Groups #659

Open mirzahabeebdba opened 2 years ago

mirzahabeebdba commented 2 years ago

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: [MCF2_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: [MCF2_IEXECUTION_MigrationDropOff] [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.

wfvdijk commented 2 years ago

Problem and solution are in message: "The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql."

mirzahabeebdba commented 2 years ago

Problem and solution are in message: "The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql."

Thank you, let me check and update you tomorrow...

mirzahabeebdba commented 2 years ago

Problem and solution are in message: "The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql."

I have checked and found that CommandLog table is there already. this IndexOptimize job in AlwaysON secondary databases earlier working fine only from last week onwards having this issue as shared above.

wfvdijk commented 2 years ago

You're sure this table is in database msdb? (because you ran this from MSDB) the query to detect if the table exists is quite simple:

SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog'

mirzahabeebdba commented 2 years ago

You're sure this table is in database msdb? (because you ran this from MSDB) the query to detect if the table exists is quite simple:

SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog'

I have checked this table is in Master database, can I create in MSDB database, but earlier the job was running without ay issue.

wfvdijk commented 2 years ago

My best guess is that you / somebody else installed a newer version of the scripts in a different database, in this case in MSDB. Anyway, in order to have this working create the table in MSDB

mirzahabeebdba commented 2 years ago

My best guess is that you / somebody else installed a newer version of the scripts in a different database, in this case in MSDB. Anyway, in order to have this working create the table in MSDB

Hi, I had added table in MSDB and monitored still same issue job failed... Any other suggestions

misirajudeen commented 2 months ago

Its on SQL Server 2019 - CU24 - Maintenance solution using Ola's free solution. Index and Statistics Jobs were going well in both Primary/Secondary AG nodes. When we configured AG with "Read-Only Intent" for managing the Secondary Read connections, Jobs started to fail in the Secondary nodes. Is this expected behavior? or any workarounds or parameters to be passed in the script executions?