You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered: