CarlosGzz-SQL
commented
2 years ago while using the parameter @execute ='N' there is no commands are being execute, therefore no activity will be logged in the CommandLog table, Change parameter to use @Execute ='Y', otherwise you will only get the print statements in the result output
Description of the issue IndexOptimize not writing to Log table when used as TSQL (vs SQLCMD)
SQL Server version and edition Execute (Build 14393: )
SELECT @@VERSIONMicrosoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0Version of the script Version: 2020-12-31 18:58:56
What command are you executing? EXEC dbo.IndexOptimize @Databases = 'Clarity', @Indexes = 'Clarity.DBO.HNO_NOTE_TEXT.PK_HNO_NOTE_TEXT', @FragmentationMedium = 'INDEX_REORGANIZE, INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 10, @FragmentationLevel2 = 40, @LogToTable = 'Y', @TimeLimit = 14400, / 4 hours/ @Delay = 1, /1 second/ @Execute = 'N';
What output are you getting? Nothing in the CommandLog table but this is what I get in the Result of this script :
Date and time: 2022-07-14 11:05:11 Server: PRCOGITODBT7 Version: 15.0.4236.7 Edition: Developer Edition (64-bit) Platform: Windows Procedure: [FAHC_DBA].[dbo].[IndexOptimize] Parameters: @Databases = 'Clarity', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE, INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 10, @FragmentationLevel2 = 40, @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 = 'Clarity.DBO.HNO_NOTE_TEXT.PK_HNO_NOTE_TEXT', @TimeLimit = 14400, @Delay = 1, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @StringDelimiter = ',', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @ExecuteAsUser = NULL, @LogToTable = 'Y', @Execute = 'N' Version: 2020-12-31 18:58:56 Source: https://ola.hallengren.com
Date and time: 2022-07-14 11:05:11 Database: [Clarity] State: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Recovery model: FULL Is accessible: Yes Availability group: HNPRCOGITODBTAG Availability group role: PRIMARY
Date and time: 2022-07-14 11:18:59
Completion time: 2022-07-14T11:18:59.5780481-04:00