Description of the issue
Index Maintenance script exits with an error if @indexes parameter specifies an object that contains a full stop/period (.) which while bad practice is not an illegal name as it can be used to create tables/indexes. The error is not raised if the @indexes parameter is changed to 'ALL_INDEXES' (even though the index is included) but in this use case we want to specify the actual index name.
CREATE TABLE [dbo].[TestTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TestColumn] [smallint] NULL,
);
GO
CREATE NONCLUSTERED INDEX [IX_dbo.TestTable_TestColumn] ON [dbo].[TestTable] ([TestColumn] ASC);
GO
I have identified the code that causes the error and it is this:
Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected) AS
(
SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,
StartPosition,
Selected
FROM Indexes3
)
SQL Server version and edition
Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 22621: ) (Hypervisor)
Version of the script
Version: 2022-12-03 17:23:44
What command are you executing?
EXECUTE [DBA].[dbo].[IndexOptimize]
@Databases = 'DataMasking'
,@Indexes = 'DataMasking.dbo.TestTable.IX_dbo.TestTable_TestColumn'
,@UpdateStatistics = 'ALL'
,@LogToTable = 'N' -- Y = Log commands to the table / N = Do not log commands to the table
,@Execute = 'Y' -- Y = Execute commands / N = Only print commands
GO
Description of the issue Index Maintenance script exits with an error if @indexes parameter specifies an object that contains a full stop/period (.) which while bad practice is not an illegal name as it can be used to create tables/indexes. The error is not raised if the @indexes parameter is changed to 'ALL_INDEXES' (even though the index is included) but in this use case we want to specify the actual index name.
CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [TestColumn] [smallint] NULL, ); GO
CREATE NONCLUSTERED INDEX [IX_dbo.TestTable_TestColumn] ON [dbo].[TestTable] ([TestColumn] ASC); GO
I have identified the code that causes the error and it is this:
Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected) AS ( SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName, CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName, CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName, CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName, StartPosition, Selected FROM Indexes3 )
SQL Server version and edition Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0 (Build 22621: ) (Hypervisor)
Version of the script Version: 2022-12-03 17:23:44
What command are you executing? EXECUTE [DBA].[dbo].[IndexOptimize] @Databases = 'DataMasking' ,@Indexes = 'DataMasking.dbo.TestTable.IX_dbo.TestTable_TestColumn' ,@UpdateStatistics = 'ALL' ,@LogToTable = 'N' -- Y = Log commands to the table / N = Do not log commands to the table ,@Execute = 'Y' -- Y = Execute commands / N = Only print commands GO
What output are you getting? Date and time: 2023-09-18 09:17:46 Server: CPC-jason-A59JZ\SQL2019 Version: 15.0.4312.2 Edition: Developer Edition (64-bit) Platform: Windows Procedure: [DBA].[dbo].[IndexOptimize] Parameters: @Databases = 'DataMasking', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @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 = 'ALL', @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = 'DataMasking.dbo.TestTable.IX_dbo.TestTable_TestColumn', @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' Version: 2022-12-03 17:23:44 Source: https://ola.hallengren.com
Msg 50000, Level 16, State 1, Procedure DBA.dbo.IndexOptimize, Line 1181 [Batch Start Line 14] The value for the parameter @Indexes is not supported.
Msg 50000, Level 16, State 1, Procedure DBA.dbo.IndexOptimize, Line 1181 [Batch Start Line 14] The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.
Date and time: 2023-09-18 09:17:46