{ "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#", "contentVersion": "1.0.0.0", "parameters": { "credentials_ElasticDbJob_SqlUsername": { "value": "ServiceAcct.svc_d" }, "credentials_CentralDataLoad_Sqlusername": { "value": "ServiceAcct.svc_d" }, "credentials_ElasticDbJob_SqlUsername_password": { "value": "" }, "credentials_CentralDataLoad_password": { "value": "" }, "server_ControlServerName": { "value": "sqlsJobServer" }, "servers_ControlServer_externalid": { "value": "/subscriptions/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/resourceGroups/rg-Sql04-cus-dev/providers/Microsoft.Sql/servers/sqlsJobServer" }, "credentials_JobAgentName": { "value": "sqledbjobs-JobServer" }, "resourceGroupName": { "value": "rg-Sql04-cus-dev" }, "JobName1": { "value": "IndexMaintenanceJob" }, "JobName2": { "value": "IndexMaintenanceJobVCore" }, "TargetGroup_IndexMaintenanceGroup": { "value": "IndexMaintenanceGroup" }, "TargetGroup_IndexMaintenanceGroupVCore": { "value": "IndexMaintenanceGroupVCore" }, "DatabaseName_CommandLog":{ "value": "sqldbElasticDBJobs" }, "tagValues": { "value": { "ProjectName": "AzureSqlServers", "ApplicationName": "ElasticJob", "SupportTeam": "DDM", "SupportTeamEmail": "DDM@nebraskablue.com", "BusinessDomain": "Infrastructure", "SharedResource": "Infrastructure", "Classification": "None", "CustomerFacing": "No", "RecoveryTimeObjective": "", "BusinessStakeholderEmail": "" } }, "subscription_id":{ "value": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" }, "StepCreateTable":{ "value": "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandLog]') AND type in (N'U'))\r\nBEGIN\r\nCREATE TABLE [dbo].[CommandLog](\r\n [ID] [int] IDENTITY(1,1) NOT NULL,\r\n [DatabaseName] [sysname] NULL,\r\n [SchemaName] [sysname] NULL,\r\n [ObjectName] [sysname] NULL,\r\n [ObjectType] [char](2) NULL,\r\n [IndexName] [sysname] NULL,\r\n [IndexType] [tinyint] NULL,\r\n [StatisticsName] [sysname] NULL,\r\n [PartitionNumber] [int] NULL,\r\n [ExtendedInfo] [xml] NULL,\r\n [Command] [nvarchar](max) NOT NULL,\r\n [CommandType] [nvarchar](60) NOT NULL,\r\n [StartTime] [datetime] NOT NULL,\r\n [EndTime] [datetime] NULL,\r\n [ErrorNumber] [int] NULL,\r\n [ErrorMessage] [nvarchar](max) NULL,\r\n CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED\r\n(\r\n [ID] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)\r\n)\r\nEND\r\nGO\r\n\r\n" }, "StepCommandExecuteProcedure":{ "value": "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommandExecute]') AND type in (N'P', N'PC'))\r\nBEGIN\r\nEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[CommandExecute] AS'\r\nEND\r\nGO\r\nALTER PROCEDURE [dbo].[CommandExecute]\r\n\r\n@Command nvarchar(max),\r\n@CommandType nvarchar(max),\r\n@Mode int,\r\n@Comment nvarchar(max) = NULL,\r\n@DatabaseName nvarchar(max) = NULL,\r\n@SchemaName nvarchar(max) = NULL,\r\n@ObjectName nvarchar(max) = NULL,\r\n@ObjectType nvarchar(max) = NULL,\r\n@IndexName nvarchar(max) = NULL,\r\n@IndexType int = NULL,\r\n@StatisticsName nvarchar(max) = NULL,\r\n@PartitionNumber int = NULL,\r\n@ExtendedInfo xml = NULL,\r\n@LockMessageSeverity int = 16,\r\n@LogToTable nvarchar(max),\r\n@Execute nvarchar(max)\r\n\r\nAS\r\n\r\nBEGIN\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Source: https://ola.hallengren.com //--\r\n --// License: https://ola.hallengren.com/license.html //--\r\n --// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //--\r\n --// Version: 2019-01-01 19:33:57 //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET NOCOUNT ON\r\n\r\n DECLARE @StartMessage nvarchar(max)\r\n DECLARE @EndMessage nvarchar(max)\r\n DECLARE @ErrorMessage nvarchar(max)\r\n DECLARE @ErrorMessageOriginal nvarchar(max)\r\n DECLARE @Severity int\r\n\r\n DECLARE @StartTime datetime\r\n DECLARE @EndTime datetime\r\n\r\n DECLARE @StartTimeSec datetime\r\n DECLARE @EndTimeSec datetime\r\n\r\n DECLARE @ID int\r\n\r\n DECLARE @Error int\r\n DECLARE @ReturnCode int\r\n\r\n SET @Error = 0\r\n SET @ReturnCode = 0\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Check core requirements //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90\r\n BEGIN\r\n SET @ErrorMessage = 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1\r\n BEGIN\r\n SET @ErrorMessage = 'ANSI_NULLS has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1\r\n BEGIN\r\n SET @ErrorMessage = 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LogToTable = 'Y' AND NOT EXISTS (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')\r\n BEGIN\r\n SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Error <> 0\r\n BEGIN\r\n SET @ReturnCode = @Error\r\n GOTO ReturnCode\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Check input parameters //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF @Command IS NULL OR @Command = ''\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @CommandType is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Mode NOT IN(1,2) OR @Mode IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Mode is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LockMessageSeverity NOT IN(10,16) OR @LockMessageSeverity IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @LockMessageSeverity is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Execute NOT IN('Y','N') OR @Execute IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Error <> 0\r\n BEGIN\r\n SET @ReturnCode = @Error\r\n GOTO ReturnCode\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Log initial information //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @StartTime = GETDATE()\r\n SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)\r\n\r\n SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120)\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Command: ' + @Command\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n IF @Comment IS NOT NULL\r\n BEGIN\r\n SET @StartMessage = 'Comment: ' + @Comment\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n END\r\n\r\n IF @LogToTable = 'Y'\r\n BEGIN\r\n INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)\r\n VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)\r\n END\r\n\r\n SET @ID = SCOPE_IDENTITY()\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Execute command //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF @Mode = 1 AND @Execute = 'Y'\r\n BEGIN\r\n EXECUTE(@Command)\r\n SET @Error = @@ERROR\r\n SET @ReturnCode = @Error\r\n END\r\n\r\n IF @Mode = 2 AND @Execute = 'Y'\r\n BEGIN\r\n BEGIN TRY\r\n EXECUTE(@Command)\r\n END TRY\r\n BEGIN CATCH\r\n SET @Error = ERROR_NUMBER()\r\n SET @ErrorMessageOriginal = ERROR_MESSAGE()\r\n\r\n SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'')\r\n SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END\r\n RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT\r\n\r\n IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)\r\n BEGIN\r\n SET @ReturnCode = ERROR_NUMBER()\r\n END\r\n END CATCH\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Log completing information //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @EndTime = GETDATE()\r\n SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)\r\n\r\n SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END\r\n RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT\r\n\r\n SET @EndMessage = 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108)\r\n RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT\r\n\r\n SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT\r\n\r\n IF @LogToTable = 'Y'\r\n BEGIN\r\n UPDATE dbo.CommandLog\r\n SET EndTime = @EndTime,\r\n ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,\r\n ErrorMessage = @ErrorMessageOriginal\r\n WHERE ID = @ID\r\n END\r\n\r\n ReturnCode:\r\n IF @ReturnCode <> 0\r\n BEGIN\r\n RETURN @ReturnCode\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n\r\nEND\r\nGO\r\n\r\n" }, "SQL_CollectCommandLogData": { "value": "\r\n\tSELECT CL.ID,\r\n\t\t @@SERVERNAME AS AzureSQLServer,\r\n\t\t CL.DatabaseName,\r\n\t\t CL.SchemaName,\r\n\t\t CL.ObjectName,\r\n\t\t CL.ObjectType,\r\n\t\t CL.IndexName,\r\n\t\t CL.IndexType,\r\n\t\t CL.StatisticsName,\r\n\t\t CL.PartitionNumber,\r\n\t\t CL.ExtendedInfo,\r\n\t\t CL.Command,\r\n\t\t CL.CommandType,\r\n\t\t CL.StartTime,\r\n\t\t CL.EndTime,\r\n\t\t CL.ErrorNumber,\r\n\t\t CL.ErrorMessage\r\n\tINTO #CLog\r\n\tFROM dbo.CommandLog AS CL\r\n\t;\r\n\r\n\tDELETE dbo.CommandLog;\r\n\r\n\tSELECT ID,\r\n\t\t AzureSQLServer,\r\n\t\t DatabaseName,\r\n\t\t SchemaName,\r\n\t\t ObjectName,\r\n\t\t ObjectType,\r\n\t\t IndexName,\r\n\t\t IndexType,\r\n\t\t StatisticsName,\r\n\t\t PartitionNumber,\r\n\t\t ExtendedInfo,\r\n\t\t Command,\r\n\t\t CommandType,\r\n\t\t StartTime,\r\n\t\t EndTime,\r\n\t\t ErrorNumber,\r\n\t\t ErrorMessage\r\n\tFROM #CLog\r\n\t;" }, "SQL_RebuildStatistics":{ "value": "DECLARE @DbName SYSNAME = DB_NAME();\r\n\r\nEXECUTE dbo.IndexOptimize @Databases = @DbName,\r\n @FragmentationLow = NULL,\r\n @FragmentationMedium = NULL,\r\n @FragmentationHigh = NULL,\r\n @UpdateStatistics = 'COLUMNS',\r\n @OnlyModifiedStatistics = 'Y',\r\n @LogToTable = 'Y';" }, "StepCreateProcedure": { "value": "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IndexOptimize]') AND type in (N'P', N'PC'))\r\nBEGIN\r\nEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[IndexOptimize] AS'\r\nEND\r\nGO\r\nALTER PROCEDURE [dbo].[IndexOptimize]\r\n\r\n@Databases nvarchar(max) = NULL,\r\n@FragmentationLow nvarchar(max) = NULL,\r\n@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',\r\n@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',\r\n@FragmentationLevel1 int = 5,\r\n@FragmentationLevel2 int = 30,\r\n@MinNumberOfPages int = 1000,\r\n@MaxNumberOfPages int = NULL,\r\n@SortInTempdb nvarchar(max) = 'N',\r\n@MaxDOP int = NULL,\r\n@FillFactor int = NULL,\r\n@PadIndex nvarchar(max) = NULL,\r\n@LOBCompaction nvarchar(max) = 'Y',\r\n@UpdateStatistics nvarchar(max) = NULL,\r\n@OnlyModifiedStatistics nvarchar(max) = 'N',\r\n@StatisticsModificationLevel int = NULL,\r\n@StatisticsSample int = NULL,\r\n@StatisticsResample nvarchar(max) = 'N',\r\n@PartitionLevel nvarchar(max) = 'Y',\r\n@MSShippedObjects nvarchar(max) = 'N',\r\n@Indexes nvarchar(max) = NULL,\r\n@TimeLimit int = NULL,\r\n@Delay int = NULL,\r\n@WaitAtLowPriorityMaxDuration int = NULL,\r\n@WaitAtLowPriorityAbortAfterWait nvarchar(max) = NULL,\r\n@Resumable nvarchar(max) = 'N',\r\n@AvailabilityGroups nvarchar(max) = NULL,\r\n@LockTimeout int = NULL,\r\n@LockMessageSeverity int = 16,\r\n@DatabaseOrder nvarchar(max) = NULL,\r\n@DatabasesInParallel nvarchar(max) = 'N',\r\n@LogToTable nvarchar(max) = 'N',\r\n@Execute nvarchar(max) = 'Y'\r\n\r\nAS\r\n\r\nBEGIN\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Source: https://ola.hallengren.com //--\r\n --// License: https://ola.hallengren.com/license.html //--\r\n --// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //--\r\n --// Version: 2019-01-01 19:33:57 //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET NOCOUNT ON\r\n\r\n SET ARITHABORT ON\r\n\r\n SET NUMERIC_ROUNDABORT OFF\r\n\r\n DECLARE @StartMessage nvarchar(max)\r\n DECLARE @EndMessage nvarchar(max)\r\n DECLARE @DatabaseMessage nvarchar(max)\r\n DECLARE @ErrorMessage nvarchar(max)\r\n DECLARE @Severity int\r\n\r\n DECLARE @StartTime datetime\r\n DECLARE @SchemaName nvarchar(max)\r\n DECLARE @ObjectName nvarchar(max)\r\n DECLARE @VersionTimestamp nvarchar(max)\r\n DECLARE @Parameters nvarchar(max)\r\n\r\n DECLARE @Version numeric(18,10)\r\n DECLARE @HostPlatform nvarchar(max)\r\n DECLARE @AmazonRDS bit\r\n\r\n DECLARE @PartitionLevelStatistics bit\r\n\r\n DECLARE @QueueID int\r\n DECLARE @QueueStartTime datetime\r\n\r\n DECLARE @CurrentDBID int\r\n DECLARE @CurrentDatabaseID int\r\n DECLARE @CurrentDatabaseName nvarchar(max)\r\n DECLARE @CurrentIsDatabaseAccessible bit\r\n DECLARE @CurrentAvailabilityGroup nvarchar(max)\r\n DECLARE @CurrentAvailabilityGroupRole nvarchar(max)\r\n DECLARE @CurrentDatabaseMirroringRole nvarchar(max)\r\n DECLARE @CurrentIsReadOnly bit\r\n\r\n DECLARE @CurrentCommand01 nvarchar(max)\r\n DECLARE @CurrentCommand02 nvarchar(max)\r\n DECLARE @CurrentCommand03 nvarchar(max)\r\n DECLARE @CurrentCommand04 nvarchar(max)\r\n DECLARE @CurrentCommand05 nvarchar(max)\r\n DECLARE @CurrentCommand06 nvarchar(max)\r\n DECLARE @CurrentCommand07 nvarchar(max)\r\n\r\n DECLARE @CurrentCommandOutput06 int\r\n DECLARE @CurrentCommandOutput07 int\r\n\r\n DECLARE @CurrentCommandType06 nvarchar(max)\r\n DECLARE @CurrentCommandType07 nvarchar(max)\r\n\r\n DECLARE @CurrentComment06 nvarchar(max)\r\n DECLARE @CurrentComment07 nvarchar(max)\r\n\r\n DECLARE @CurrentExtendedInfo06 xml\r\n DECLARE @CurrentExtendedInfo07 xml\r\n\r\n DECLARE @CurrentIxID int\r\n DECLARE @CurrentIxOrder int\r\n DECLARE @CurrentSchemaID int\r\n DECLARE @CurrentSchemaName nvarchar(max)\r\n DECLARE @CurrentObjectID int\r\n DECLARE @CurrentObjectName nvarchar(max)\r\n DECLARE @CurrentObjectType nvarchar(max)\r\n DECLARE @CurrentIsMemoryOptimized bit\r\n DECLARE @CurrentIndexID int\r\n DECLARE @CurrentIndexName nvarchar(max)\r\n DECLARE @CurrentIndexType int\r\n DECLARE @CurrentStatisticsID int\r\n DECLARE @CurrentStatisticsName nvarchar(max)\r\n DECLARE @CurrentPartitionID bigint\r\n DECLARE @CurrentPartitionNumber int\r\n DECLARE @CurrentPartitionCount int\r\n DECLARE @CurrentIsPartition bit\r\n DECLARE @CurrentIndexExists bit\r\n DECLARE @CurrentStatisticsExists bit\r\n DECLARE @CurrentIsImageText bit\r\n DECLARE @CurrentIsNewLOB bit\r\n DECLARE @CurrentIsFileStream bit\r\n DECLARE @CurrentIsColumnStore bit\r\n DECLARE @CurrentIsComputed bit\r\n DECLARE @CurrentIsTimestamp bit\r\n DECLARE @CurrentAllowPageLocks bit\r\n DECLARE @CurrentNoRecompute bit\r\n DECLARE @CurrentIsIncremental bit\r\n DECLARE @CurrentRowCount bigint\r\n DECLARE @CurrentModificationCounter bigint\r\n DECLARE @CurrentOnReadOnlyFileGroup bit\r\n DECLARE @CurrentResumableIndexOperation bit\r\n DECLARE @CurrentFragmentationLevel float\r\n DECLARE @CurrentPageCount bigint\r\n DECLARE @CurrentFragmentationGroup nvarchar(max)\r\n DECLARE @CurrentAction nvarchar(max)\r\n DECLARE @CurrentMaxDOP int\r\n DECLARE @CurrentUpdateStatistics nvarchar(max)\r\n DECLARE @CurrentStatisticsSample int\r\n DECLARE @CurrentStatisticsResample nvarchar(max)\r\n DECLARE @CurrentDelay datetime\r\n\r\n DECLARE @tmpDatabases TABLE (ID int IDENTITY,\r\n DatabaseName nvarchar(max),\r\n DatabaseType nvarchar(max),\r\n AvailabilityGroup bit,\r\n StartPosition int,\r\n DatabaseSize bigint,\r\n [Order] int,\r\n Selected bit,\r\n Completed bit,\r\n PRIMARY KEY(Selected, Completed, [Order], ID))\r\n\r\n DECLARE @tmpAvailabilityGroups TABLE (ID int IDENTITY PRIMARY KEY,\r\n AvailabilityGroupName nvarchar(max),\r\n StartPosition int,\r\n Selected bit)\r\n\r\n DECLARE @tmpDatabasesAvailabilityGroups TABLE (DatabaseName nvarchar(max),\r\n AvailabilityGroupName nvarchar(max))\r\n\r\n DECLARE @tmpIndexesStatistics TABLE (ID int IDENTITY,\r\n SchemaID int,\r\n SchemaName nvarchar(max),\r\n ObjectID int,\r\n ObjectName nvarchar(max),\r\n ObjectType nvarchar(max),\r\n IsMemoryOptimized bit,\r\n IndexID int,\r\n IndexName nvarchar(max),\r\n IndexType int,\r\n AllowPageLocks bit,\r\n IsImageText bit,\r\n IsNewLOB bit,\r\n IsFileStream bit,\r\n IsColumnStore bit,\r\n IsComputed bit,\r\n IsTimestamp bit,\r\n OnReadOnlyFileGroup bit,\r\n ResumableIndexOperation bit,\r\n StatisticsID int,\r\n StatisticsName nvarchar(max),\r\n [NoRecompute] bit,\r\n IsIncremental bit,\r\n PartitionID bigint,\r\n PartitionNumber int,\r\n PartitionCount int,\r\n StartPosition int,\r\n [Order] int,\r\n Selected bit,\r\n Completed bit,\r\n PRIMARY KEY(Selected, Completed, [Order], ID))\r\n\r\n DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),\r\n DatabaseType nvarchar(max),\r\n AvailabilityGroup nvarchar(max),\r\n StartPosition int,\r\n Selected bit)\r\n\r\n DECLARE @SelectedAvailabilityGroups TABLE (AvailabilityGroupName nvarchar(max),\r\n StartPosition int,\r\n Selected bit)\r\n\r\n DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),\r\n SchemaName nvarchar(max),\r\n ObjectName nvarchar(max),\r\n IndexName nvarchar(max),\r\n StartPosition int,\r\n Selected bit)\r\n\r\n DECLARE @Actions TABLE ([Action] nvarchar(max))\r\n\r\n INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')\r\n INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')\r\n INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')\r\n\r\n DECLARE @ActionsPreferred TABLE (FragmentationGroup nvarchar(max),\r\n [Priority] int,\r\n [Action] nvarchar(max))\r\n\r\n DECLARE @CurrentActionsAllowed TABLE ([Action] nvarchar(max))\r\n\r\n\r\n DECLARE @CurrentAlterIndexWithClauseArguments TABLE (ID int IDENTITY,\r\n Argument nvarchar(max))\r\n\r\n DECLARE @CurrentAlterIndexWithClause nvarchar(max)\r\n\r\n DECLARE @CurrentUpdateStatisticsWithClauseArguments TABLE (ID int IDENTITY,\r\n Argument nvarchar(max))\r\n\r\n DECLARE @CurrentUpdateStatisticsWithClause nvarchar(max)\r\n\r\n DECLARE @Error int\r\n DECLARE @ReturnCode int\r\n\r\n SET @Error = 0\r\n SET @ReturnCode = 0\r\n\r\n SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))\r\n\r\n IF @Version >= 14\r\n BEGIN\r\n SELECT @HostPlatform = host_platform\r\n FROM sys.dm_os_host_info\r\n END\r\n ELSE\r\n BEGIN\r\n SET @HostPlatform = 'Windows'\r\n END\r\n\r\n SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Log initial information //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @StartTime = GETDATE()\r\n SET @SchemaName = (SELECT schemas.name FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID)\r\n SET @ObjectName = OBJECT_NAME(@@PROCID)\r\n SET @VersionTimestamp = SUBSTRING(OBJECT_DEFINITION(@@PROCID),CHARINDEX('--// Version: ',OBJECT_DEFINITION(@@PROCID)) + LEN('--// Version: ') + 1, 19)\r\n\r\n SET @Parameters = '@Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @MinNumberOfPages = ' + ISNULL(CAST(@MinNumberOfPages AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @MaxNumberOfPages = ' + ISNULL(CAST(@MaxNumberOfPages AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @PadIndex = ' + ISNULL('''' + REPLACE(@PadIndex,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @UpdateStatistics = ' + ISNULL('''' + REPLACE(@UpdateStatistics,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @OnlyModifiedStatistics = ' + ISNULL('''' + REPLACE(@OnlyModifiedStatistics,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @StatisticsModificationLevel = ' + ISNULL(CAST(@StatisticsModificationLevel AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @StatisticsResample = ' + ISNULL('''' + REPLACE(@StatisticsResample,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @MSShippedObjects = ' + ISNULL('''' + REPLACE(@MSShippedObjects,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @Indexes = ' + ISNULL('''' + REPLACE(@Indexes,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @Delay = ' + ISNULL(CAST(@Delay AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @WaitAtLowPriorityMaxDuration = ' + ISNULL(CAST(@WaitAtLowPriorityMaxDuration AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @WaitAtLowPriorityAbortAfterWait = ' + ISNULL('''' + REPLACE(@WaitAtLowPriorityAbortAfterWait,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @Resumable = ' + ISNULL('''' + REPLACE(@Resumable,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @LockMessageSeverity = ' + ISNULL(CAST(@LockMessageSeverity AS nvarchar),'NULL')\r\n SET @Parameters = @Parameters + ', @DatabaseOrder = ' + ISNULL('''' + REPLACE(@DatabaseOrder,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @DatabasesInParallel = ' + ISNULL('''' + REPLACE(@DatabasesInParallel,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')\r\n SET @Parameters = @Parameters + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL')\r\n\r\n SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120)\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max))\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max))\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Platform: ' + @HostPlatform\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName)\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Parameters: ' + @Parameters\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Version: ' + @VersionTimestamp\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n SET @StartMessage = 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Check core requirements //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90\r\n BEGIN\r\n SET @ErrorMessage = 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1\r\n BEGIN\r\n SET @ErrorMessage = 'ANSI_NULLS has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1\r\n BEGIN\r\n SET @ErrorMessage = 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute')\r\n BEGIN\r\n SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'P' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandExecute' AND OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LockMessageSeverity%')\r\n BEGIN\r\n SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LogToTable = 'Y' AND NOT EXISTS (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')\r\n BEGIN\r\n SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @DatabasesInParallel = 'Y' AND NOT EXISTS (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] = 'Queue')\r\n BEGIN\r\n SET @ErrorMessage = 'The table Queue is missing. Download https://ola.hallengren.com/scripts/Queue.sql.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @DatabasesInParallel = 'Y' AND NOT EXISTS (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] = 'QueueDatabase')\r\n BEGIN\r\n SET @ErrorMessage = 'The table QueueDatabase is missing. Download https://ola.hallengren.com/scripts/QueueDatabase.sql.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @@TRANCOUNT <> 0\r\n BEGIN\r\n SET @ErrorMessage = 'The transaction count is not 0.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Error <> 0\r\n BEGIN\r\n SET @ReturnCode = @Error\r\n GOTO Logging\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Select databases //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @Databases = REPLACE(@Databases, CHAR(10), '')\r\n SET @Databases = REPLACE(@Databases, CHAR(13), '')\r\n\r\n WHILE CHARINDEX(', ',@Databases) > 0 SET @Databases = REPLACE(@Databases,', ',',')\r\n WHILE CHARINDEX(' ,',@Databases) > 0 SET @Databases = REPLACE(@Databases,' ,',',')\r\n\r\n SET @Databases = LTRIM(RTRIM(@Databases));\r\n\r\n WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS\r\n (\r\n SELECT 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,\r\n SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem\r\n WHERE @Databases IS NOT NULL\r\n UNION ALL\r\n SELECT CAST(EndPosition AS int) + 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,\r\n SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem\r\n FROM Databases1\r\n WHERE EndPosition < LEN(@Databases) + 1\r\n ),\r\n Databases2 (DatabaseItem, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,\r\n StartPosition,\r\n CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected\r\n FROM Databases1\r\n ),\r\n Databases3 (DatabaseItem, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES','AVAILABILITY_GROUP_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem,\r\n CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType,\r\n CASE WHEN DatabaseItem = 'AVAILABILITY_GROUP_DATABASES' THEN 1 ELSE NULL END AvailabilityGroup,\r\n StartPosition,\r\n Selected\r\n FROM Databases2\r\n ),\r\n Databases4 (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,\r\n DatabaseType,\r\n AvailabilityGroup,\r\n StartPosition,\r\n Selected\r\n FROM Databases3\r\n )\r\n INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected)\r\n SELECT DatabaseName,\r\n DatabaseType,\r\n AvailabilityGroup,\r\n StartPosition,\r\n Selected\r\n FROM Databases4\r\n OPTION (MAXRECURSION 0)\r\n\r\n IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1\r\n BEGIN\r\n INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected)\r\n SELECT name AS AvailabilityGroupName,\r\n 0 AS Selected\r\n FROM sys.availability_groups\r\n\r\n INSERT INTO @tmpDatabasesAvailabilityGroups (DatabaseName, AvailabilityGroupName)\r\n SELECT availability_databases_cluster.database_name, availability_groups.name\r\n FROM sys.availability_databases_cluster availability_databases_cluster\r\n INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id\r\n END\r\n\r\n INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Order], Selected, Completed)\r\n SELECT [name] AS DatabaseName,\r\n CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,\r\n NULL AS AvailabilityGroup,\r\n 0 AS [Order],\r\n 0 AS Selected,\r\n 0 AS Completed\r\n FROM sys.databases\r\n WHERE [name] <> 'tempdb'\r\n AND source_database_id IS NULL\r\n ORDER BY [name] ASC\r\n\r\n UPDATE tmpDatabases\r\n SET AvailabilityGroup = CASE WHEN EXISTS (SELECT * FROM @tmpDatabasesAvailabilityGroups WHERE DatabaseName = tmpDatabases.DatabaseName) THEN 1 ELSE 0 END\r\n FROM @tmpDatabases tmpDatabases\r\n\r\n UPDATE tmpDatabases\r\n SET tmpDatabases.Selected = SelectedDatabases.Selected\r\n FROM @tmpDatabases tmpDatabases\r\n INNER JOIN @SelectedDatabases SelectedDatabases\r\n ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')\r\n AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)\r\n AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL)\r\n WHERE SelectedDatabases.Selected = 1\r\n\r\n UPDATE tmpDatabases\r\n SET tmpDatabases.Selected = SelectedDatabases.Selected\r\n FROM @tmpDatabases tmpDatabases\r\n INNER JOIN @SelectedDatabases SelectedDatabases\r\n ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')\r\n AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)\r\n AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL)\r\n WHERE SelectedDatabases.Selected = 0\r\n\r\n UPDATE tmpDatabases\r\n SET tmpDatabases.StartPosition = SelectedDatabases2.StartPosition\r\n FROM @tmpDatabases tmpDatabases\r\n INNER JOIN (SELECT tmpDatabases.DatabaseName, MIN(SelectedDatabases.StartPosition) AS StartPosition\r\n FROM @tmpDatabases tmpDatabases\r\n INNER JOIN @SelectedDatabases SelectedDatabases\r\n ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]')\r\n AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL)\r\n AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL)\r\n WHERE SelectedDatabases.Selected = 1\r\n GROUP BY tmpDatabases.DatabaseName) SelectedDatabases2\r\n ON tmpDatabases.DatabaseName = SelectedDatabases2.DatabaseName\r\n\r\n IF @Databases IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = ''))\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Databases is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Select availability groups //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF @AvailabilityGroups IS NOT NULL AND @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1\r\n BEGIN\r\n\r\n SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(10), '')\r\n SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(13), '')\r\n\r\n WHILE CHARINDEX(', ',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,', ',',')\r\n WHILE CHARINDEX(' ,',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,' ,',',')\r\n\r\n SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups));\r\n\r\n WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS\r\n (\r\n SELECT 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,\r\n SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) - 1) AS AvailabilityGroupItem\r\n WHERE @AvailabilityGroups IS NOT NULL\r\n UNION ALL\r\n SELECT CAST(EndPosition AS int) + 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,\r\n SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) - EndPosition - 1) AS AvailabilityGroupItem\r\n FROM AvailabilityGroups1\r\n WHERE EndPosition < LEN(@AvailabilityGroups) + 1\r\n ),\r\n AvailabilityGroups2 (AvailabilityGroupItem, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN AvailabilityGroupItem LIKE '-%' THEN RIGHT(AvailabilityGroupItem,LEN(AvailabilityGroupItem) - 1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,\r\n StartPosition,\r\n CASE WHEN AvailabilityGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected\r\n FROM AvailabilityGroups1\r\n ),\r\n AvailabilityGroups3 (AvailabilityGroupItem, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN AvailabilityGroupItem = 'ALL_AVAILABILITY_GROUPS' THEN '%' ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,\r\n StartPosition,\r\n Selected\r\n FROM AvailabilityGroups2\r\n ),\r\n AvailabilityGroups4 (AvailabilityGroupName, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN LEFT(AvailabilityGroupItem,1) = '[' AND RIGHT(AvailabilityGroupItem,1) = ']' THEN PARSENAME(AvailabilityGroupItem,1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,\r\n StartPosition,\r\n Selected\r\n FROM AvailabilityGroups3\r\n )\r\n INSERT INTO @SelectedAvailabilityGroups (AvailabilityGroupName, StartPosition, Selected)\r\n SELECT AvailabilityGroupName, StartPosition, Selected\r\n FROM AvailabilityGroups4\r\n OPTION (MAXRECURSION 0)\r\n\r\n UPDATE tmpAvailabilityGroups\r\n SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected\r\n FROM @tmpAvailabilityGroups tmpAvailabilityGroups\r\n INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups\r\n ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')\r\n WHERE SelectedAvailabilityGroups.Selected = 1\r\n\r\n UPDATE tmpAvailabilityGroups\r\n SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected\r\n FROM @tmpAvailabilityGroups tmpAvailabilityGroups\r\n INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups\r\n ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')\r\n WHERE SelectedAvailabilityGroups.Selected = 0\r\n\r\n UPDATE tmpAvailabilityGroups\r\n SET tmpAvailabilityGroups.StartPosition = SelectedAvailabilityGroups2.StartPosition\r\n FROM @tmpAvailabilityGroups tmpAvailabilityGroups\r\n INNER JOIN (SELECT tmpAvailabilityGroups.AvailabilityGroupName, MIN(SelectedAvailabilityGroups.StartPosition) AS StartPosition\r\n FROM @tmpAvailabilityGroups tmpAvailabilityGroups\r\n INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups\r\n ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')\r\n WHERE SelectedAvailabilityGroups.Selected = 1\r\n GROUP BY tmpAvailabilityGroups.AvailabilityGroupName) SelectedAvailabilityGroups2\r\n ON tmpAvailabilityGroups.AvailabilityGroupName = SelectedAvailabilityGroups2.AvailabilityGroupName\r\n\r\n UPDATE tmpDatabases\r\n SET tmpDatabases.StartPosition = tmpAvailabilityGroups.StartPosition,\r\n tmpDatabases.Selected = 1\r\n FROM @tmpDatabases tmpDatabases\r\n INNER JOIN @tmpDatabasesAvailabilityGroups tmpDatabasesAvailabilityGroups ON tmpDatabases.DatabaseName = tmpDatabasesAvailabilityGroups.DatabaseName\r\n INNER JOIN @tmpAvailabilityGroups tmpAvailabilityGroups ON tmpDatabasesAvailabilityGroups.AvailabilityGroupName = tmpAvailabilityGroups.AvailabilityGroupName\r\n WHERE tmpAvailabilityGroups.Selected = 1\r\n\r\n END\r\n\r\n IF @AvailabilityGroups IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedAvailabilityGroups) OR EXISTS(SELECT * FROM @SelectedAvailabilityGroups WHERE AvailabilityGroupName IS NULL OR AvailabilityGroupName = '') OR @Version < 11 OR SERVERPROPERTY('IsHadrEnabled') = 0)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @AvailabilityGroups is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF (@Databases IS NULL AND @AvailabilityGroups IS NULL)\r\n BEGIN\r\n SET @ErrorMessage = 'You need to specify one of the parameters @Databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL)\r\n BEGIN\r\n SET @ErrorMessage = 'You can only specify one of the parameters @Databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Select indexes //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @Indexes = REPLACE(@Indexes, CHAR(10), '')\r\n SET @Indexes = REPLACE(@Indexes, CHAR(13), '')\r\n\r\n WHILE CHARINDEX(', ',@Indexes) > 0 SET @Indexes = REPLACE(@Indexes,', ',',')\r\n WHILE CHARINDEX(' ,',@Indexes) > 0 SET @Indexes = REPLACE(@Indexes,' ,',',')\r\n\r\n SET @Indexes = LTRIM(RTRIM(@Indexes));\r\n\r\n WITH Indexes1 (StartPosition, EndPosition, IndexItem) AS\r\n (\r\n SELECT 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) AS EndPosition,\r\n SUBSTRING(@Indexes, 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) - 1) AS IndexItem\r\n WHERE @Indexes IS NOT NULL\r\n UNION ALL\r\n SELECT CAST(EndPosition AS int) + 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) AS EndPosition,\r\n SUBSTRING(@Indexes, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) - EndPosition - 1) AS IndexItem\r\n FROM Indexes1\r\n WHERE EndPosition < LEN(@Indexes) + 1\r\n ),\r\n Indexes2 (IndexItem, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN IndexItem LIKE '-%' THEN RIGHT(IndexItem,LEN(IndexItem) - 1) ELSE IndexItem END AS IndexItem,\r\n StartPosition,\r\n CASE WHEN IndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected\r\n FROM Indexes1\r\n ),\r\n Indexes3 (IndexItem, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,\r\n StartPosition,\r\n Selected\r\n FROM Indexes2\r\n ),\r\n Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected) AS\r\n (\r\n SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,\r\n CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,\r\n CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,\r\n CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,\r\n StartPosition,\r\n Selected\r\n FROM Indexes3\r\n )\r\n INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected)\r\n SELECT DatabaseName, SchemaName, ObjectName, IndexName, StartPosition, Selected\r\n FROM Indexes4\r\n OPTION (MAXRECURSION 0);\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Select actions //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n WITH FragmentationLow (StartPosition, EndPosition, [Action]) AS\r\n (\r\n SELECT 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,\r\n SUBSTRING(@FragmentationLow, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) - 1) AS [Action]\r\n WHERE @FragmentationLow IS NOT NULL\r\n UNION ALL\r\n SELECT CAST(EndPosition AS int) + 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,\r\n SUBSTRING(@FragmentationLow, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) - EndPosition - 1) AS [Action]\r\n FROM FragmentationLow\r\n WHERE EndPosition < LEN(@FragmentationLow) + 1\r\n )\r\n INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])\r\n SELECT 'Low' AS FragmentationGroup,\r\n ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],\r\n [Action]\r\n FROM FragmentationLow\r\n OPTION (MAXRECURSION 0);\r\n\r\n WITH FragmentationMedium (StartPosition, EndPosition, [Action]) AS\r\n (\r\n SELECT 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,\r\n SUBSTRING(@FragmentationMedium, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) - 1) AS [Action]\r\n WHERE @FragmentationMedium IS NOT NULL\r\n UNION ALL\r\n SELECT CAST(EndPosition AS int) + 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,\r\n SUBSTRING(@FragmentationMedium, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) - EndPosition - 1) AS [Action]\r\n FROM FragmentationMedium\r\n WHERE EndPosition < LEN(@FragmentationMedium) + 1\r\n )\r\n INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])\r\n SELECT 'Medium' AS FragmentationGroup,\r\n ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],\r\n [Action]\r\n FROM FragmentationMedium\r\n OPTION (MAXRECURSION 0);\r\n\r\n WITH FragmentationHigh (StartPosition, EndPosition, [Action]) AS\r\n (\r\n SELECT 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,\r\n SUBSTRING(@FragmentationHigh, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) - 1) AS [Action]\r\n WHERE @FragmentationHigh IS NOT NULL\r\n UNION ALL\r\n SELECT CAST(EndPosition AS int) + 1 AS StartPosition,\r\n ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,\r\n SUBSTRING(@FragmentationHigh, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) - EndPosition - 1) AS [Action]\r\n FROM FragmentationHigh\r\n WHERE EndPosition < LEN(@FragmentationHigh) + 1\r\n )\r\n INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])\r\n SELECT 'High' AS FragmentationGroup,\r\n ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],\r\n [Action]\r\n FROM FragmentationHigh\r\n OPTION (MAXRECURSION 0)\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Check input parameters //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' AND [Action] NOT IN(SELECT * FROM @Actions))\r\n OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' GROUP BY [Action] HAVING COUNT(*) > 1)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @FragmentationLow is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' AND [Action] NOT IN(SELECT * FROM @Actions))\r\n OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' GROUP BY [Action] HAVING COUNT(*) > 1)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @FragmentationMedium is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'High' AND [Action] NOT IN(SELECT * FROM @Actions))\r\n OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'High' GROUP BY [Action] HAVING COUNT(*) > 1)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @FragmentationHigh is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @MinNumberOfPages < 0 OR @MinNumberOfPages IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @MinNumberOfPages is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @MaxNumberOfPages < 0\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @MaxNumberOfPages is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @SortInTempdb is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @MaxDOP < 0 OR @MaxDOP > 64\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @FillFactor <= 0 OR @FillFactor > 100\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @FillFactor is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @PadIndex NOT IN('Y','N')\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @PadIndex is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LOBCompaction NOT IN('Y','N') OR @LOBCompaction IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @LOBCompaction is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @UpdateStatistics NOT IN('ALL','COLUMNS','INDEX')\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @UpdateStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @OnlyModifiedStatistics NOT IN('Y','N') OR @OnlyModifiedStatistics IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @OnlyModifiedStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @StatisticsModificationLevel <= 0 OR @StatisticsModificationLevel > 100\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @StatisticsModificationLevel is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @OnlyModifiedStatistics = 'Y' AND @StatisticsModificationLevel IS NOT NULL\r\n BEGIN\r\n SET @ErrorMessage = 'You can only specify one of the parameters @OnlyModifiedStatistics and @StatisticsModificationLevel.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @StatisticsSample <= 0 OR @StatisticsSample > 100\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @StatisticsSample is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @StatisticsResample NOT IN('Y','N') OR @StatisticsResample IS NULL OR (@StatisticsResample = 'Y' AND @StatisticsSample IS NOT NULL)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @StatisticsResample is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @PartitionLevel NOT IN('Y','N') OR @PartitionLevel IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @PartitionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @MSShippedObjects NOT IN('Y','N') OR @MSShippedObjects IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @MSShippedObjects is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL) OR (@Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes))\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Indexes is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @TimeLimit < 0\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @TimeLimit is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Delay < 0\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Delay is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @WaitAtLowPriorityMaxDuration < 0 OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @WaitAtLowPriorityAbortAfterWait IS NULL) OR (@WaitAtLowPriorityMaxDuration IS NULL AND @WaitAtLowPriorityAbortAfterWait IS NOT NULL)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @WaitAtLowPriorityAbortAfterWait NOT IN('NONE','SELF','BLOCKERS') OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @WaitAtLowPriorityMaxDuration IS NULL) OR (@WaitAtLowPriorityAbortAfterWait IS NULL AND @WaitAtLowPriorityMaxDuration IS NOT NULL)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityAbortAfterWait is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Resumable NOT IN('Y','N') OR @Resumable IS NULL OR (@Resumable = 'Y' AND NOT (@Version >= 14 OR SERVERPROPERTY('EngineEdition') IN (5,8)))\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Resumable is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Resumable = 'Y' AND @SortInTempdb = 'Y'\r\n BEGIN\r\n SET @ErrorMessage = 'You can only specify one of the parameters @Resumable and @SortInTempdb.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LockTimeout < 0\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @LockTimeout is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LockMessageSeverity NOT IN(10,16)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @LockMessageSeverity is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @DatabaseOrder NOT IN('DATABASE_NAME_ASC','DATABASE_NAME_DESC','DATABASE_SIZE_ASC','DATABASE_SIZE_DESC') OR (@DatabaseOrder IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 5)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @DatabaseOrder is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @DatabasesInParallel NOT IN('Y','N') OR @DatabasesInParallel IS NULL OR (@DatabasesInParallel = 'Y' AND SERVERPROPERTY('EngineEdition') = 5)\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @DatabasesInParallel is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Execute NOT IN('Y','N') OR @Execute IS NULL\r\n BEGIN\r\n SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n IF @Error <> 0\r\n BEGIN\r\n SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @ReturnCode = @Error\r\n GOTO Logging\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Check that selected databases and availability groups exist //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @ErrorMessage = ''\r\n SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '\r\n FROM @SelectedDatabases\r\n WHERE DatabaseName NOT LIKE '%[%]%'\r\n AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)\r\n IF @@ROWCOUNT > 0\r\n BEGIN\r\n SET @ErrorMessage = 'The following databases in the @Databases parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n SET @ErrorMessage = ''\r\n SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '\r\n FROM @SelectedIndexes\r\n WHERE DatabaseName NOT LIKE '%[%]%'\r\n AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases)\r\n IF @@ROWCOUNT > 0\r\n BEGIN\r\n SET @ErrorMessage = 'The following databases in the @Indexes parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n SET @ErrorMessage = ''\r\n SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(AvailabilityGroupName) + ', '\r\n FROM @SelectedAvailabilityGroups\r\n WHERE AvailabilityGroupName NOT LIKE '%[%]%'\r\n AND AvailabilityGroupName NOT IN (SELECT AvailabilityGroupName FROM @tmpAvailabilityGroups)\r\n IF @@ROWCOUNT > 0\r\n BEGIN\r\n SET @ErrorMessage = 'The following availability groups do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n SET @ErrorMessage = ''\r\n SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', '\r\n FROM @SelectedIndexes\r\n WHERE DatabaseName NOT LIKE '%[%]%'\r\n AND DatabaseName IN (SELECT DatabaseName FROM @tmpDatabases)\r\n AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases WHERE Selected = 1)\r\n IF @@ROWCOUNT > 0\r\n BEGIN\r\n SET @ErrorMessage = 'The following databases have been selected in the @Indexes parameter, but not in the @Databases or @AvailabilityGroups parameters: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Should statistics be updated on the partition level? //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n SET @PartitionLevelStatistics = CASE WHEN @PartitionLevel = 'Y' AND ((@Version >= 12.05 AND @Version < 13) OR @Version >= 13.04422 OR SERVERPROPERTY('EngineEdition') IN (5,8)) THEN 1 ELSE 0 END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Update database order //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF @DatabaseOrder IN('DATABASE_SIZE_ASC','DATABASE_SIZE_DESC')\r\n BEGIN\r\n UPDATE tmpDatabases\r\n SET DatabaseSize = (SELECT SUM(size) FROM sys.master_files WHERE [type] = 0 AND database_id = DB_ID(tmpDatabases.DatabaseName))\r\n FROM @tmpDatabases tmpDatabases\r\n END\r\n\r\n IF @DatabaseOrder IS NULL\r\n BEGIN\r\n WITH tmpDatabases AS (\r\n SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY StartPosition ASC, DatabaseName ASC) AS RowNumber\r\n FROM @tmpDatabases tmpDatabases\r\n WHERE Selected = 1\r\n )\r\n UPDATE tmpDatabases\r\n SET [Order] = RowNumber\r\n END\r\n ELSE\r\n IF @DatabaseOrder = 'DATABASE_NAME_ASC'\r\n BEGIN\r\n WITH tmpDatabases AS (\r\n SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName ASC) AS RowNumber\r\n FROM @tmpDatabases tmpDatabases\r\n WHERE Selected = 1\r\n )\r\n UPDATE tmpDatabases\r\n SET [Order] = RowNumber\r\n END\r\n ELSE\r\n IF @DatabaseOrder = 'DATABASE_NAME_DESC'\r\n BEGIN\r\n WITH tmpDatabases AS (\r\n SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName DESC) AS RowNumber\r\n FROM @tmpDatabases tmpDatabases\r\n WHERE Selected = 1\r\n )\r\n UPDATE tmpDatabases\r\n SET [Order] = RowNumber\r\n END\r\n ELSE\r\n IF @DatabaseOrder = 'DATABASE_SIZE_ASC'\r\n BEGIN\r\n WITH tmpDatabases AS (\r\n SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize ASC) AS RowNumber\r\n FROM @tmpDatabases tmpDatabases\r\n WHERE Selected = 1\r\n )\r\n UPDATE tmpDatabases\r\n SET [Order] = RowNumber\r\n END\r\n ELSE\r\n IF @DatabaseOrder = 'DATABASE_SIZE_DESC'\r\n BEGIN\r\n WITH tmpDatabases AS (\r\n SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize DESC) AS RowNumber\r\n FROM @tmpDatabases tmpDatabases\r\n WHERE Selected = 1\r\n )\r\n UPDATE tmpDatabases\r\n SET [Order] = RowNumber\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Update the queue //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n IF @DatabasesInParallel = 'Y'\r\n BEGIN\r\n\r\n BEGIN TRY\r\n\r\n SELECT @QueueID = QueueID\r\n FROM dbo.[Queue]\r\n WHERE SchemaName = @SchemaName\r\n AND ObjectName = @ObjectName\r\n AND [Parameters] = @Parameters\r\n\r\n IF @QueueID IS NULL\r\n BEGIN\r\n BEGIN TRANSACTION\r\n\r\n SELECT @QueueID = QueueID\r\n FROM dbo.[Queue] WITH (UPDLOCK, TABLOCK)\r\n WHERE SchemaName = @SchemaName\r\n AND ObjectName = @ObjectName\r\n AND [Parameters] = @Parameters\r\n\r\n IF @QueueID IS NULL\r\n BEGIN\r\n INSERT INTO dbo.[Queue] (SchemaName, ObjectName, [Parameters])\r\n SELECT @SchemaName, @ObjectName, @Parameters\r\n\r\n SET @QueueID = SCOPE_IDENTITY()\r\n END\r\n\r\n COMMIT TRANSACTION\r\n END\r\n\r\n BEGIN TRANSACTION\r\n\r\n UPDATE [Queue]\r\n SET QueueStartTime = GETDATE(),\r\n SessionID = @@SPID,\r\n RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID),\r\n RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID)\r\n FROM dbo.[Queue] [Queue]\r\n WHERE QueueID = @QueueID\r\n AND NOT EXISTS (SELECT *\r\n FROM sys.dm_exec_requests\r\n WHERE session_id = [Queue].SessionID\r\n AND request_id = [Queue].RequestID\r\n AND start_time = [Queue].RequestStartTime)\r\n AND NOT EXISTS (SELECT *\r\n FROM dbo.QueueDatabase QueueDatabase\r\n INNER JOIN sys.dm_exec_requests ON QueueDatabase.SessionID = session_id AND QueueDatabase.RequestID = request_id AND QueueDatabase.RequestStartTime = start_time\r\n WHERE QueueDatabase.QueueID = @QueueID)\r\n\r\n IF @@ROWCOUNT = 1\r\n BEGIN\r\n INSERT INTO dbo.QueueDatabase (QueueID, DatabaseName)\r\n SELECT @QueueID AS QueueID,\r\n DatabaseName\r\n FROM @tmpDatabases tmpDatabases\r\n WHERE Selected = 1\r\n AND NOT EXISTS (SELECT * FROM dbo.QueueDatabase WHERE DatabaseName = tmpDatabases.DatabaseName AND QueueID = @QueueID)\r\n\r\n DELETE QueueDatabase\r\n FROM dbo.QueueDatabase QueueDatabase\r\n WHERE QueueID = @QueueID\r\n AND NOT EXISTS (SELECT * FROM @tmpDatabases tmpDatabases WHERE DatabaseName = QueueDatabase.DatabaseName AND Selected = 1)\r\n\r\n UPDATE QueueDatabase\r\n SET DatabaseOrder = tmpDatabases.[Order]\r\n FROM dbo.QueueDatabase QueueDatabase\r\n INNER JOIN @tmpDatabases tmpDatabases ON QueueDatabase.DatabaseName = tmpDatabases.DatabaseName\r\n WHERE QueueID = @QueueID\r\n END\r\n\r\n COMMIT TRANSACTION\r\n\r\n SELECT @QueueStartTime = QueueStartTime\r\n FROM dbo.[Queue]\r\n WHERE QueueID = @QueueID\r\n\r\n END TRY\r\n\r\n BEGIN CATCH\r\n IF XACT_STATE() <> 0\r\n BEGIN\r\n ROLLBACK TRANSACTION\r\n END\r\n SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @ReturnCode = ERROR_NUMBER()\r\n GOTO Logging\r\n END CATCH\r\n\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Execute commands //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n WHILE (1 = 1)\r\n BEGIN\r\n\r\n IF @DatabasesInParallel = 'Y'\r\n BEGIN\r\n UPDATE QueueDatabase\r\n SET DatabaseStartTime = NULL,\r\n SessionID = NULL,\r\n RequestID = NULL,\r\n RequestStartTime = NULL\r\n FROM dbo.QueueDatabase QueueDatabase\r\n WHERE QueueID = @QueueID\r\n AND DatabaseStartTime IS NOT NULL\r\n AND DatabaseEndTime IS NULL\r\n AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests WHERE session_id = QueueDatabase.SessionID AND request_id = QueueDatabase.RequestID AND start_time = QueueDatabase.RequestStartTime)\r\n\r\n UPDATE QueueDatabase\r\n SET DatabaseStartTime = GETDATE(),\r\n DatabaseEndTime = NULL,\r\n SessionID = @@SPID,\r\n RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID),\r\n RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID),\r\n @CurrentDatabaseName = DatabaseName\r\n FROM (SELECT TOP 1 DatabaseStartTime,\r\n DatabaseEndTime,\r\n SessionID,\r\n RequestID,\r\n RequestStartTime,\r\n DatabaseName\r\n FROM dbo.QueueDatabase\r\n WHERE QueueID = @QueueID\r\n AND (DatabaseStartTime < @QueueStartTime OR DatabaseStartTime IS NULL)\r\n AND NOT (DatabaseStartTime IS NOT NULL AND DatabaseEndTime IS NULL)\r\n ORDER BY DatabaseOrder ASC\r\n ) QueueDatabase\r\n END\r\n ELSE\r\n BEGIN\r\n SELECT TOP 1 @CurrentDBID = ID,\r\n @CurrentDatabaseName = DatabaseName\r\n FROM @tmpDatabases\r\n WHERE Selected = 1\r\n AND Completed = 0\r\n ORDER BY [Order] ASC\r\n END\r\n\r\n IF @@ROWCOUNT = 0\r\n BEGIN\r\n BREAK\r\n END\r\n\r\n SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)\r\n\r\n IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5\r\n BEGIN\r\n IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)\r\n BEGIN\r\n SET @CurrentIsDatabaseAccessible = 1\r\n END\r\n ELSE\r\n BEGIN\r\n SET @CurrentIsDatabaseAccessible = 0\r\n END\r\n END\r\n\r\n IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1\r\n BEGIN\r\n SELECT @CurrentAvailabilityGroup = availability_groups.name,\r\n @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc\r\n FROM sys.databases databases\r\n INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id\r\n INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id\r\n INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id\r\n WHERE databases.name = @CurrentDatabaseName\r\n END\r\n\r\n IF SERVERPROPERTY('EngineEdition') <> 5\r\n BEGIN\r\n SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)\r\n FROM sys.database_mirroring\r\n WHERE database_id = @CurrentDatabaseID\r\n END\r\n\r\n SELECT @CurrentIsReadOnly = is_read_only\r\n FROM sys.databases\r\n WHERE name = @CurrentDatabaseName\r\n\r\n SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n SET @DatabaseMessage = 'Database: ' + QUOTENAME(@CurrentDatabaseName)\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n SET @DatabaseMessage = 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar)\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n SET @DatabaseMessage = 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n SET @DatabaseMessage = 'Updateability: ' + CASE WHEN @CurrentIsReadOnly = 1 THEN 'READ_ONLY' WHEN @CurrentIsReadOnly = 0 THEN 'READ_WRITE' END\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n SET @DatabaseMessage = 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar)\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n IF @CurrentIsDatabaseAccessible IS NOT NULL\r\n BEGIN\r\n SET @DatabaseMessage = 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n END\r\n\r\n SET @DatabaseMessage = 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar)\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n IF @CurrentAvailabilityGroup IS NOT NULL\r\n BEGIN\r\n SET @DatabaseMessage = 'Availability group: ' + @CurrentAvailabilityGroup\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n\r\n SET @DatabaseMessage = 'Availability group role: ' + @CurrentAvailabilityGroupRole\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n END\r\n\r\n IF @CurrentDatabaseMirroringRole IS NOT NULL\r\n BEGIN\r\n SET @DatabaseMessage = 'Database mirroring role: ' + @CurrentDatabaseMirroringRole\r\n RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT\r\n END\r\n\r\n RAISERROR('',10,1) WITH NOWAIT\r\n\r\n IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'\r\n AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)\r\n AND DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'\r\n BEGIN\r\n\r\n -- Select indexes in the current database\r\n IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)\r\n BEGIN\r\n SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;'\r\n + 'USE ' + QUOTENAME(@CurrentDatabaseName) + ';'\r\n + ' SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, NoRecompute, IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed'\r\n + ' FROM ('\r\n\r\n IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')\r\n BEGIN\r\n SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID'\r\n + ', schemas.[name] AS SchemaName'\r\n + ', objects.[object_id] AS ObjectID'\r\n + ', objects.[name] AS ObjectName'\r\n + ', RTRIM(objects.[type]) AS ObjectType'\r\n + ', ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE '0' END + ' AS IsMemoryOptimized'\r\n + ', indexes.index_id AS IndexID'\r\n + ', indexes.[name] AS IndexName'\r\n + ', indexes.[type] AS IndexType'\r\n + ', indexes.allow_page_locks AS AllowPageLocks'\r\n\r\n + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = objects.object_id AND types.name IN(''image'',''text'',''ntext'')) THEN 1 ELSE 0 END AS IsImageText'\r\n\r\n + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = objects.object_id AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1'\r\n + ' WHEN indexes.[type] = 2 AND EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) THEN 1 ELSE 0 END AS IsNewLOB'\r\n\r\n + ', CASE WHEN indexes.[type] = 1 AND EXISTS(SELECT * FROM sys.columns columns WHERE columns.[object_id] = objects.object_id AND columns.is_filestream = 1) THEN 1 ELSE 0 END AS IsFileStream'\r\n\r\n + ', CASE WHEN EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = objects.object_id AND [type] IN(5,6)) THEN 1 ELSE 0 END AS IsColumnStore'\r\n\r\n + ', CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id WHERE (index_columns.key_ordinal > 0 OR index_columns.partition_ordinal > 0) AND columns.is_computed = 1 AND index_columns.object_id = indexes.object_id AND index_columns.index_id = indexes.index_id) THEN 1 ELSE 0 END AS IsComputed'\r\n\r\n + ', CASE WHEN EXISTS(SELECT * FROM sys.index_columns index_columns INNER JOIN sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN sys.types types ON columns.system_type_id = types.system_type_id WHERE index_columns.[object_id] = objects.object_id AND index_columns.index_id = indexes.index_id AND types.[name] = ''timestamp'') THEN 1 ELSE 0 END AS IsTimestamp'\r\n\r\n + ', CASE WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes2.[object_id] = indexes.[object_id] AND indexes2.[index_id] = indexes.index_id' + CASE WHEN @PartitionLevel = 'Y' THEN ' AND destination_data_spaces.destination_id = partitions.partition_number' ELSE '' END + ') THEN 1'\r\n + ' WHEN EXISTS (SELECT * FROM sys.indexes indexes2 INNER JOIN sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = indexes2.[object_id] AND indexes.[index_id] = indexes2.index_id) THEN 1'\r\n + ' WHEN indexes.[type] = 1 AND EXISTS (SELECT * FROM sys.tables tables INNER JOIN sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = objects.[object_id]) THEN 1 ELSE 0 END AS OnReadOnlyFileGroup'\r\n\r\n + ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'\r\n\r\n + ', stats.stats_id AS StatisticsID'\r\n + ', stats.name AS StatisticsName'\r\n + ', stats.no_recompute AS NoRecompute'\r\n + ', ' + CASE WHEN @Version >= 12 THEN 'stats.is_incremental' ELSE '0' END + ' AS IsIncremental'\r\n + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_id AS PartitionID' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionID' END\r\n + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'partitions.partition_number AS PartitionNumber' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionNumber' END\r\n + ', ' + CASE WHEN @PartitionLevel = 'Y' THEN 'IndexPartitions.partition_count AS PartitionCount' WHEN @PartitionLevel = 'N' THEN 'NULL AS PartitionCount' END\r\n + ', 0 AS [Order]'\r\n + ', 0 AS Selected'\r\n + ', 0 AS Completed'\r\n + ' FROM sys.indexes indexes'\r\n + ' INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]'\r\n + ' INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]'\r\n + ' LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id]'\r\n + ' LEFT OUTER JOIN sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'\r\n IF @PartitionLevel = 'Y'\r\n BEGIN\r\n SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id'\r\n + ' LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(DISTINCT partitions.partition_number) AS partition_count FROM sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'\r\n END\r\n\r\n SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')'\r\n + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END\r\n + ' AND indexes.[type] IN(1,2,3,4,5,6,7)'\r\n + ' AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'\r\n END\r\n\r\n IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL'\r\n BEGIN\r\n SET @CurrentCommand01 = @CurrentCommand01 + ' UNION '\r\n END\r\n\r\n IF @UpdateStatistics IN('ALL','COLUMNS')\r\n BEGIN\r\n SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID'\r\n + ', schemas.[name] AS SchemaName'\r\n + ', objects.[object_id] AS ObjectID'\r\n + ', objects.[name] AS ObjectName'\r\n + ', RTRIM(objects.[type]) AS ObjectType'\r\n + ', ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE '0' END + ' AS IsMemoryOptimized'\r\n + ', NULL AS IndexID, NULL AS IndexName'\r\n + ', NULL AS IndexType'\r\n + ', NULL AS AllowPageLocks'\r\n + ', NULL AS IsImageText'\r\n + ', NULL AS IsNewLOB'\r\n + ', NULL AS IsFileStream'\r\n + ', NULL AS IsColumnStore'\r\n + ', NULL AS IsComputed'\r\n + ', NULL AS IsTimestamp'\r\n + ', NULL AS OnReadOnlyFileGroup'\r\n + ', NULL AS ResumableIndexOperation'\r\n + ', stats.stats_id AS StatisticsID'\r\n + ', stats.name AS StatisticsName'\r\n + ', stats.no_recompute AS NoRecompute'\r\n + ', ' + CASE WHEN @Version >= 12 THEN 'stats.is_incremental' ELSE '0' END + ' AS IsIncremental'\r\n + ', NULL AS PartitionID'\r\n + ', ' + CASE WHEN @PartitionLevelStatistics = 1 THEN 'dm_db_incremental_stats_properties.partition_number' ELSE 'NULL' END + ' AS PartitionNumber'\r\n + ', NULL AS PartitionCount'\r\n + ', 0 AS [Order]'\r\n + ', 0 AS Selected'\r\n + ', 0 AS Completed'\r\n + ' FROM sys.stats stats'\r\n + ' INNER JOIN sys.objects objects ON stats.[object_id] = objects.[object_id]'\r\n + ' INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]'\r\n + ' LEFT OUTER JOIN sys.tables tables ON objects.[object_id] = tables.[object_id]'\r\n\r\n IF @PartitionLevelStatistics = 1\r\n BEGIN\r\n SET @CurrentCommand01 = @CurrentCommand01 + ' OUTER APPLY sys.dm_db_incremental_stats_properties(stats.object_id, stats.stats_id) dm_db_incremental_stats_properties'\r\n END\r\n\r\n SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')'\r\n + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END\r\n + ' AND NOT EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)'\r\n END\r\n\r\n SET @CurrentCommand01 = @CurrentCommand01 + ') IndexesStatistics'\r\n\r\n INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, AllowPageLocks, IsImageText, IsNewLOB, IsFileStream, IsColumnStore, IsComputed, IsTimestamp, OnReadOnlyFileGroup, ResumableIndexOperation, StatisticsID, StatisticsName, [NoRecompute], IsIncremental, PartitionID, PartitionNumber, PartitionCount, [Order], Selected, Completed)\r\n EXECUTE sp_executesql @statement = @CurrentCommand01\r\n SET @Error = @@ERROR\r\n IF @Error <> 0\r\n BEGIN\r\n SET @ReturnCode = @Error\r\n END\r\n END\r\n\r\n IF @Indexes IS NULL\r\n BEGIN\r\n UPDATE tmpIndexesStatistics\r\n SET tmpIndexesStatistics.Selected = 1\r\n FROM @tmpIndexesStatistics tmpIndexesStatistics\r\n END\r\n ELSE\r\n BEGIN\r\n UPDATE tmpIndexesStatistics\r\n SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected\r\n FROM @tmpIndexesStatistics tmpIndexesStatistics\r\n INNER JOIN @SelectedIndexes SelectedIndexes\r\n ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')\r\n WHERE SelectedIndexes.Selected = 1\r\n\r\n UPDATE tmpIndexesStatistics\r\n SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected\r\n FROM @tmpIndexesStatistics tmpIndexesStatistics\r\n INNER JOIN @SelectedIndexes SelectedIndexes\r\n ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')\r\n WHERE SelectedIndexes.Selected = 0\r\n\r\n UPDATE tmpIndexesStatistics\r\n SET tmpIndexesStatistics.StartPosition = SelectedIndexes2.StartPosition\r\n FROM @tmpIndexesStatistics tmpIndexesStatistics\r\n INNER JOIN (SELECT tmpIndexesStatistics.SchemaName, tmpIndexesStatistics.ObjectName, tmpIndexesStatistics.IndexName, tmpIndexesStatistics.StatisticsName, MIN(SelectedIndexes.StartPosition) AS StartPosition\r\n FROM @tmpIndexesStatistics tmpIndexesStatistics\r\n INNER JOIN @SelectedIndexes SelectedIndexes\r\n ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')\r\n WHERE SelectedIndexes.Selected = 1\r\n GROUP BY tmpIndexesStatistics.SchemaName, tmpIndexesStatistics.ObjectName, tmpIndexesStatistics.IndexName, tmpIndexesStatistics.StatisticsName) SelectedIndexes2\r\n ON tmpIndexesStatistics.SchemaName = SelectedIndexes2.SchemaName\r\n AND tmpIndexesStatistics.ObjectName = SelectedIndexes2.ObjectName\r\n AND (tmpIndexesStatistics.IndexName = SelectedIndexes2.IndexName OR tmpIndexesStatistics.IndexName IS NULL)\r\n AND (tmpIndexesStatistics.StatisticsName = SelectedIndexes2.StatisticsName OR tmpIndexesStatistics.StatisticsName IS NULL)\r\n END;\r\n\r\n WITH tmpIndexesStatistics AS (\r\n SELECT SchemaName, ObjectName, [Order], ROW_NUMBER() OVER (ORDER BY ISNULL(ResumableIndexOperation,0) DESC, StartPosition ASC, SchemaName ASC, ObjectName ASC, CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC, IndexType ASC, IndexName ASC, StatisticsName ASC, PartitionNumber ASC) AS RowNumber\r\n FROM @tmpIndexesStatistics tmpIndexesStatistics\r\n WHERE Selected = 1\r\n )\r\n UPDATE tmpIndexesStatistics\r\n SET [Order] = RowNumber\r\n\r\n SET @ErrorMessage = ''\r\n SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + '.' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ', '\r\n FROM @SelectedIndexes SelectedIndexes\r\n WHERE DatabaseName = @CurrentDatabaseName\r\n AND SchemaName NOT LIKE '%[%]%'\r\n AND ObjectName NOT LIKE '%[%]%'\r\n AND IndexName LIKE '%[%]%'\r\n AND NOT EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE SchemaName = SelectedIndexes.SchemaName AND ObjectName = SelectedIndexes.ObjectName)\r\n IF @@ROWCOUNT > 0\r\n BEGIN\r\n SET @ErrorMessage = 'The following objects in the @Indexes parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n SET @ErrorMessage = ''\r\n SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + '.' + QUOTENAME(IndexName) + ', '\r\n FROM @SelectedIndexes SelectedIndexes\r\n WHERE DatabaseName = @CurrentDatabaseName\r\n AND SchemaName NOT LIKE '%[%]%'\r\n AND ObjectName NOT LIKE '%[%]%'\r\n AND IndexName NOT LIKE '%[%]%'\r\n AND NOT EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE SchemaName = SelectedIndexes.SchemaName AND ObjectName = SelectedIndexes.ObjectName AND IndexName = SelectedIndexes.IndexName)\r\n IF @@ROWCOUNT > 0\r\n BEGIN\r\n SET @ErrorMessage = 'The following indexes in the @Indexes parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n WHILE (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)\r\n BEGIN\r\n SELECT TOP 1 @CurrentIxID = ID,\r\n @CurrentIxOrder = [Order],\r\n @CurrentSchemaID = SchemaID,\r\n @CurrentSchemaName = SchemaName,\r\n @CurrentObjectID = ObjectID,\r\n @CurrentObjectName = ObjectName,\r\n @CurrentObjectType = ObjectType,\r\n @CurrentIsMemoryOptimized = IsMemoryOptimized,\r\n @CurrentIndexID = IndexID,\r\n @CurrentIndexName = IndexName,\r\n @CurrentIndexType = IndexType,\r\n @CurrentAllowPageLocks = AllowPageLocks,\r\n @CurrentIsImageText = IsImageText,\r\n @CurrentIsNewLOB = IsNewLOB,\r\n @CurrentIsFileStream = IsFileStream,\r\n @CurrentIsColumnStore = IsColumnStore,\r\n @CurrentIsComputed = IsComputed,\r\n @CurrentIsTimestamp = IsTimestamp,\r\n @CurrentOnReadOnlyFileGroup = OnReadOnlyFileGroup,\r\n @CurrentResumableIndexOperation = ResumableIndexOperation,\r\n @CurrentStatisticsID = StatisticsID,\r\n @CurrentStatisticsName = StatisticsName,\r\n @CurrentNoRecompute = [NoRecompute],\r\n @CurrentIsIncremental = IsIncremental,\r\n @CurrentPartitionID = PartitionID,\r\n @CurrentPartitionNumber = PartitionNumber,\r\n @CurrentPartitionCount = PartitionCount\r\n FROM @tmpIndexesStatistics\r\n WHERE Selected = 1\r\n AND Completed = 0\r\n ORDER BY [Order] ASC\r\n\r\n IF @@ROWCOUNT = 0\r\n BEGIN\r\n BREAK\r\n END\r\n\r\n -- Is the index a partition?\r\n IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1 BEGIN SET @CurrentIsPartition = 0 END ELSE BEGIN SET @CurrentIsPartition = 1 END\r\n\r\n -- Does the index exist?\r\n IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)\r\n BEGIN\r\n SET @CurrentCommand02 = ''\r\n\r\n IF @LockTimeout IS NOT NULL SET @CurrentCommand02 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '\r\n SET @CurrentCommand02 = @CurrentCommand02 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; '\r\n\r\n IF @CurrentIsPartition = 0 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM sys.indexes indexes INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'') AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType) BEGIN SET @ParamIndexExists = 1 END'\r\n IF @CurrentIsPartition = 1 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM sys.indexes indexes INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] INNER JOIN sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id WHERE objects.[type] IN(''U'',''V'') AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType AND partitions.partition_id = @ParamPartitionID AND partitions.partition_number = @ParamPartitionNumber) BEGIN SET @ParamIndexExists = 1 END'\r\n\r\n BEGIN TRY\r\n EXECUTE sp_executesql @statement = @CurrentCommand02, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamIndexID int, @ParamIndexName sysname, @ParamIndexType int, @ParamPartitionID bigint, @ParamPartitionNumber int, @ParamIndexExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamIndexID = @CurrentIndexID, @ParamIndexName = @CurrentIndexName, @ParamIndexType = @CurrentIndexType, @ParamPartitionID = @CurrentPartitionID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamIndexExists = @CurrentIndexExists OUTPUT\r\n\r\n IF @CurrentIndexExists IS NULL\r\n BEGIN\r\n SET @CurrentIndexExists = 0\r\n GOTO NoAction\r\n END\r\n END TRY\r\n BEGIN CATCH\r\n SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CASE WHEN ERROR_NUMBER() = 1222 THEN ' The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index exists.' ELSE '' END + CHAR(13) + CHAR(10) + ' '\r\n SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END\r\n RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT\r\n\r\n IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)\r\n BEGIN\r\n SET @ReturnCode = ERROR_NUMBER()\r\n END\r\n\r\n GOTO NoAction\r\n END CATCH\r\n END\r\n\r\n -- Does the statistics exist?\r\n IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL\r\n BEGIN\r\n SET @CurrentCommand03 = ''\r\n\r\n IF @LockTimeout IS NOT NULL SET @CurrentCommand03 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '\r\n SET @CurrentCommand03 = @CurrentCommand03 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; '\r\n\r\n SET @CurrentCommand03 = @CurrentCommand03 + 'IF EXISTS(SELECT * FROM sys.stats stats INNER JOIN sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND stats.stats_id = @ParamStatisticsID AND stats.[name] = @ParamStatisticsName) BEGIN SET @ParamStatisticsExists = 1 END'\r\n\r\n BEGIN TRY\r\n EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamStatisticsID int, @ParamStatisticsName sysname, @ParamStatisticsExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsName = @CurrentStatisticsName, @ParamStatisticsExists = @CurrentStatisticsExists OUTPUT\r\n\r\n IF @CurrentStatisticsExists IS NULL\r\n BEGIN\r\n SET @CurrentStatisticsExists = 0\r\n GOTO NoAction\r\n END\r\n END TRY\r\n BEGIN CATCH\r\n SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CASE WHEN ERROR_NUMBER() = 1222 THEN ' The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the statistics exists.' ELSE '' END + CHAR(13) + CHAR(10) + ' '\r\n SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END\r\n RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT\r\n\r\n IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)\r\n BEGIN\r\n SET @ReturnCode = ERROR_NUMBER()\r\n END\r\n\r\n GOTO NoAction\r\n END CATCH\r\n END\r\n\r\n -- Has the data in the statistics been modified since the statistics was last updated?\r\n IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL\r\n BEGIN\r\n SET @CurrentCommand04 = ''\r\n\r\n IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '\r\n SET @CurrentCommand04 = @CurrentCommand04 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; '\r\n\r\n IF @PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1\r\n BEGIN\r\n SET @CurrentCommand04 = @CurrentCommand04 + 'SELECT @ParamRowCount = [rows], @ParamModificationCounter = modification_counter FROM sys.dm_db_incremental_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE partition_number = @ParamPartitionNumber'\r\n END\r\n ELSE\r\n IF (@Version >= 10.504000 AND @Version < 11) OR @Version >= 11.03000\r\n BEGIN\r\n SET @CurrentCommand04 = @CurrentCommand04 + 'SELECT @ParamRowCount = [rows], @ParamModificationCounter = modification_counter FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID)'\r\n END\r\n ELSE\r\n BEGIN\r\n SET @CurrentCommand04 = @CurrentCommand04 + 'SELECT @ParamRowCount = rowcnt, @ParamModificationCounter = rowmodctr FROM sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID'\r\n END\r\n\r\n BEGIN TRY\r\n EXECUTE sp_executesql @statement = @CurrentCommand04, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamPartitionNumber int, @ParamRowCount bigint OUTPUT, @ParamModificationCounter bigint OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamRowCount = @CurrentRowCount OUTPUT, @ParamModificationCounter = @CurrentModificationCounter OUTPUT\r\n\r\n IF @CurrentRowCount IS NULL SET @CurrentRowCount = 0\r\n IF @CurrentModificationCounter IS NULL SET @CurrentModificationCounter = 0\r\n END TRY\r\n BEGIN CATCH\r\n SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CASE WHEN ERROR_NUMBER() = 1222 THEN ' The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. The rows and modification_counter could not be checked.' ELSE '' END + CHAR(13) + CHAR(10) + ' '\r\n SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END\r\n RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT\r\n\r\n IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)\r\n BEGIN\r\n SET @ReturnCode = ERROR_NUMBER()\r\n END\r\n\r\n GOTO NoAction\r\n END CATCH\r\n END\r\n\r\n -- Is the index fragmented?\r\n IF @CurrentIndexID IS NOT NULL\r\n AND @CurrentOnReadOnlyFileGroup = 0\r\n AND EXISTS(SELECT * FROM @ActionsPreferred)\r\n AND (EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3) OR @MinNumberOfPages > 0 OR @MaxNumberOfPages IS NOT NULL)\r\n BEGIN\r\n SET @CurrentCommand05 = ''\r\n\r\n IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '\r\n\r\n SET @CurrentCommand05 = @CurrentCommand05 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'\r\n\r\n BEGIN TRY\r\n EXECUTE sp_executesql @statement = @CurrentCommand05, @params = N'@ParamDatabaseID int, @ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT', @ParamDatabaseID = @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount = @CurrentPageCount OUTPUT\r\n END TRY\r\n BEGIN CATCH\r\n SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CASE WHEN ERROR_NUMBER() = 1222 THEN ' The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. The page_count and avg_fragmentation_in_percent could not be checked.' ELSE '' END + CHAR(13) + CHAR(10) + ' '\r\n SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END\r\n RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT\r\n\r\n IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10)\r\n BEGIN\r\n SET @ReturnCode = ERROR_NUMBER()\r\n END\r\n\r\n GOTO NoAction\r\n END CATCH\r\n END\r\n\r\n -- Select fragmentation group\r\n IF @CurrentIndexID IS NOT NULL AND @CurrentOnReadOnlyFileGroup = 0 AND EXISTS(SELECT * FROM @ActionsPreferred)\r\n BEGIN\r\n SET @CurrentFragmentationGroup = CASE\r\n WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN 'High'\r\n WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'\r\n WHEN @CurrentFragmentationLevel < @FragmentationLevel1 THEN 'Low'\r\n END\r\n END\r\n\r\n -- Which actions are allowed?\r\n IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)\r\n BEGIN\r\n IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)\r\n BEGIN\r\n INSERT INTO @CurrentActionsAllowed ([Action])\r\n VALUES ('INDEX_REORGANIZE')\r\n END\r\n IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)\r\n BEGIN\r\n INSERT INTO @CurrentActionsAllowed ([Action])\r\n VALUES ('INDEX_REBUILD_OFFLINE')\r\n END\r\n IF @CurrentOnReadOnlyFileGroup = 0\r\n AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)\r\n AND (@CurrentIsPartition = 0 OR @Version >= 12)\r\n AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)\r\n OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)\r\n OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)\r\n OR (@CurrentIndexType = 2 AND @Version >= 11))\r\n AND (@CurrentIsColumnStore = 0 OR @Version < 11)\r\n AND SERVERPROPERTY('EngineEdition') IN (3,5,8)\r\n BEGIN\r\n INSERT INTO @CurrentActionsAllowed ([Action])\r\n VALUES ('INDEX_REBUILD_ONLINE')\r\n END\r\n END\r\n\r\n -- Decide action\r\n IF @CurrentIndexID IS NOT NULL\r\n AND EXISTS(SELECT * FROM @ActionsPreferred)\r\n AND (@CurrentPageCount >= @MinNumberOfPages OR @MinNumberOfPages = 0)\r\n AND (@CurrentPageCount <= @MaxNumberOfPages OR @MaxNumberOfPages IS NULL)\r\n AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n IF EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3)\r\n BEGIN\r\n SELECT @CurrentAction = [Action]\r\n FROM @ActionsPreferred\r\n WHERE FragmentationGroup = @CurrentFragmentationGroup\r\n AND [Priority] = (SELECT MIN([Priority])\r\n FROM @ActionsPreferred\r\n WHERE FragmentationGroup = @CurrentFragmentationGroup\r\n AND [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))\r\n END\r\n ELSE\r\n BEGIN\r\n SELECT @CurrentAction = [Action]\r\n FROM @ActionsPreferred\r\n WHERE [Priority] = (SELECT MIN([Priority])\r\n FROM @ActionsPreferred\r\n WHERE [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))\r\n END\r\n END\r\n\r\n IF @CurrentResumableIndexOperation = 1\r\n BEGIN\r\n SET @CurrentAction = 'INDEX_REBUILD_ONLINE'\r\n END\r\n\r\n -- Workaround for limitation in SQL Server, http://support.microsoft.com/kb/2292737\r\n IF @CurrentIndexID IS NOT NULL\r\n BEGIN\r\n SET @CurrentMaxDOP = @MaxDOP\r\n\r\n IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0\r\n BEGIN\r\n SET @CurrentMaxDOP = 1\r\n END\r\n END\r\n\r\n -- Update statistics?\r\n IF @CurrentStatisticsID IS NOT NULL\r\n AND ((@UpdateStatistics = 'ALL' AND (@CurrentIndexType IN (1,2,3,4,7) OR @CurrentIndexID IS NULL)) OR (@UpdateStatistics = 'INDEX' AND @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN (1,2,3,4,7)) OR (@UpdateStatistics = 'COLUMNS' AND @CurrentIndexID IS NULL))\r\n AND ((@OnlyModifiedStatistics = 'N' AND @StatisticsModificationLevel IS NULL) OR (@OnlyModifiedStatistics = 'Y' AND @CurrentModificationCounter > 0) OR ((@CurrentModificationCounter * 1. / NULLIF(@CurrentRowCount,0)) * 100 >= @StatisticsModificationLevel) OR (@StatisticsModificationLevel IS NOT NULL AND @CurrentModificationCounter > 0 AND (@CurrentModificationCounter >= SQRT(@CurrentRowCount * 1000))) OR (@CurrentIsMemoryOptimized = 1 AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') IN (5,8))))\r\n AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND (@CurrentPartitionNumber = @CurrentPartitionCount OR (@PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1))))\r\n BEGIN\r\n SET @CurrentUpdateStatistics = 'Y'\r\n END\r\n ELSE\r\n BEGIN\r\n SET @CurrentUpdateStatistics = 'N'\r\n END\r\n\r\n SET @CurrentStatisticsSample = @StatisticsSample\r\n SET @CurrentStatisticsResample = @StatisticsResample\r\n\r\n -- Memory-optimized tables only supports FULLSCAN and RESAMPLE in SQL Server 2014\r\n IF @CurrentIsMemoryOptimized = 1 AND NOT (@Version >= 13 OR SERVERPROPERTY('EngineEdition') IN (5,8)) AND (@CurrentStatisticsSample <> 100 OR @CurrentStatisticsSample IS NULL)\r\n BEGIN\r\n SET @CurrentStatisticsSample = NULL\r\n SET @CurrentStatisticsResample = 'Y'\r\n END\r\n\r\n -- Incremental statistics only supports RESAMPLE\r\n IF @PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1\r\n BEGIN\r\n SET @CurrentStatisticsSample = NULL\r\n SET @CurrentStatisticsResample = 'Y'\r\n END\r\n\r\n -- Create index comment\r\n IF @CurrentIndexID IS NOT NULL\r\n BEGIN\r\n SET @CurrentComment06 = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' WHEN @CurrentIndexType = 5 THEN 'Clustered Columnstore' WHEN @CurrentIndexType = 6 THEN 'NonClustered Columnstore' WHEN @CurrentIndexType = 7 THEN 'NonClustered Hash' ELSE 'N/A' END + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'ImageText: ' + CASE WHEN @CurrentIsImageText = 1 THEN 'Yes' WHEN @CurrentIsImageText = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'NewLOB: ' + CASE WHEN @CurrentIsNewLOB = 1 THEN 'Yes' WHEN @CurrentIsNewLOB = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'FileStream: ' + CASE WHEN @CurrentIsFileStream = 1 THEN 'Yes' WHEN @CurrentIsFileStream = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n IF @Version >= 11 SET @CurrentComment06 = @CurrentComment06 + 'ColumnStore: ' + CASE WHEN @CurrentIsColumnStore = 1 THEN 'Yes' WHEN @CurrentIsColumnStore = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n IF @Version >= 14 AND @Resumable = 'Y' SET @CurrentComment06 = @CurrentComment06 + 'Computed: ' + CASE WHEN @CurrentIsComputed = 1 THEN 'Yes' WHEN @CurrentIsComputed = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n IF @Version >= 14 AND @Resumable = 'Y' SET @CurrentComment06 = @CurrentComment06 + 'Timestamp: ' + CASE WHEN @CurrentIsTimestamp = 1 THEN 'Yes' WHEN @CurrentIsTimestamp = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'PageCount: ' + ISNULL(CAST(@CurrentPageCount AS nvarchar),'N/A') + ', '\r\n SET @CurrentComment06 = @CurrentComment06 + 'Fragmentation: ' + ISNULL(CAST(@CurrentFragmentationLevel AS nvarchar),'N/A')\r\n END\r\n\r\n IF @CurrentIndexID IS NOT NULL AND (@CurrentPageCount IS NOT NULL OR @CurrentFragmentationLevel IS NOT NULL)\r\n BEGIN\r\n SET @CurrentExtendedInfo06 = (SELECT *\r\n FROM (SELECT CAST(@CurrentPageCount AS nvarchar) AS [PageCount],\r\n CAST(@CurrentFragmentationLevel AS nvarchar) AS Fragmentation\r\n ) ExtendedInfo FOR XML RAW('ExtendedInfo'), ELEMENTS)\r\n END\r\n\r\n -- Create statistics comment\r\n IF @CurrentStatisticsID IS NOT NULL\r\n BEGIN\r\n SET @CurrentComment07 = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '\r\n SET @CurrentComment07 = @CurrentComment07 + 'IndexType: ' + CASE WHEN @CurrentIndexID IS NOT NULL THEN 'Index' ELSE 'Column' END + ', '\r\n IF @CurrentIndexID IS NOT NULL SET @CurrentComment07 = @CurrentComment07 + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' WHEN @CurrentIndexType = 5 THEN 'Clustered Columnstore' WHEN @CurrentIndexType = 6 THEN 'NonClustered Columnstore' WHEN @CurrentIndexType = 7 THEN 'NonClustered Hash' ELSE 'N/A' END + ', '\r\n SET @CurrentComment07 = @CurrentComment07 + 'Incremental: ' + CASE WHEN @CurrentIsIncremental = 1 THEN 'Y' WHEN @CurrentIsIncremental = 0 THEN 'N' ELSE 'N/A' END + ', '\r\n SET @CurrentComment07 = @CurrentComment07 + 'RowCount: ' + ISNULL(CAST(@CurrentRowCount AS nvarchar),'N/A') + ', '\r\n SET @CurrentComment07 = @CurrentComment07 + 'ModificationCounter: ' + ISNULL(CAST(@CurrentModificationCounter AS nvarchar),'N/A')\r\n END\r\n\r\n IF @CurrentStatisticsID IS NOT NULL AND (@CurrentRowCount IS NOT NULL OR @CurrentModificationCounter IS NOT NULL)\r\n BEGIN\r\n SET @CurrentExtendedInfo07 = (SELECT *\r\n FROM (SELECT CAST(@CurrentRowCount AS nvarchar) AS [RowCount],\r\n CAST(@CurrentModificationCounter AS nvarchar) AS ModificationCounter\r\n ) ExtendedInfo FOR XML RAW('ExtendedInfo'), ELEMENTS)\r\n END\r\n\r\n IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)\r\n BEGIN\r\n SET @CurrentCommandType06 = 'ALTER_INDEX'\r\n\r\n SET @CurrentCommand06 = ''\r\n IF @LockTimeout IS NOT NULL SET @CurrentCommand06 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '\r\n SET @CurrentCommand06 = @CurrentCommand06 + 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)\r\n IF @CurrentResumableIndexOperation = 1 SET @CurrentCommand06 = @CurrentCommand06 + ' RESUME'\r\n IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @CurrentResumableIndexOperation = 0 SET @CurrentCommand06 = @CurrentCommand06 + ' REBUILD'\r\n IF @CurrentAction IN('INDEX_REORGANIZE') AND @CurrentResumableIndexOperation = 0 SET @CurrentCommand06 = @CurrentCommand06 + ' REORGANIZE'\r\n IF @CurrentIsPartition = 1 AND @CurrentResumableIndexOperation = 0 SET @CurrentCommand06 = @CurrentCommand06 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)\r\n\r\n IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @SortInTempdb = 'Y' AND @CurrentIndexType IN(1,2,3,4) AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'SORT_IN_TEMPDB = ON'\r\n END\r\n\r\n IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @SortInTempdb = 'N' AND @CurrentIndexType IN(1,2,3,4) AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'SORT_IN_TEMPDB = OFF'\r\n END\r\n\r\n IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'ONLINE = ON' + CASE WHEN @WaitAtLowPriorityMaxDuration IS NOT NULL THEN ' (WAIT_AT_LOW_PRIORITY (MAX_DURATION = ' + CAST(@WaitAtLowPriorityMaxDuration AS nvarchar) + ', ABORT_AFTER_WAIT = ' + UPPER(@WaitAtLowPriorityAbortAfterWait) + '))' ELSE '' END\r\n END\r\n\r\n IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'ONLINE = OFF'\r\n END\r\n\r\n IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @CurrentMaxDOP IS NOT NULL\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)\r\n END\r\n\r\n IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)\r\n END\r\n\r\n IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') AND @PadIndex = 'Y' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'PAD_INDEX = ON'\r\n END\r\n\r\n IF (@Version >= 14 OR SERVERPROPERTY('EngineEdition') IN (5,8)) AND @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentResumableIndexOperation = 0\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT CASE WHEN @Resumable = 'Y' AND @CurrentIndexType IN(1,2) AND @CurrentIsComputed = 0 AND @CurrentIsTimestamp = 0 THEN 'RESUMABLE = ON' ELSE 'RESUMABLE = OFF' END\r\n END\r\n\r\n IF (@Version >= 14 OR SERVERPROPERTY('EngineEdition') IN (5,8)) AND @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentResumableIndexOperation = 0 AND @Resumable = 'Y' AND @CurrentIndexType IN(1,2) AND @CurrentIsComputed = 0 AND @CurrentIsTimestamp = 0 AND @TimeLimit IS NOT NULL\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'MAX_DURATION = ' + CAST(DATEDIFF(MINUTE,GETDATE(),DATEADD(ss,@TimeLimit,@StartTime)) AS nvarchar(max))\r\n END\r\n\r\n IF @CurrentAction IN('INDEX_REORGANIZE') AND @LOBCompaction = 'Y'\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'LOB_COMPACTION = ON'\r\n END\r\n\r\n IF @CurrentAction IN('INDEX_REORGANIZE') AND @LOBCompaction = 'N'\r\n BEGIN\r\n INSERT INTO @CurrentAlterIndexWithClauseArguments (Argument)\r\n SELECT 'LOB_COMPACTION = OFF'\r\n END\r\n\r\n IF EXISTS (SELECT * FROM @CurrentAlterIndexWithClauseArguments)\r\n BEGIN\r\n SET @CurrentAlterIndexWithClause = ' WITH ('\r\n\r\n SELECT @CurrentAlterIndexWithClause = @CurrentAlterIndexWithClause + Argument + ', '\r\n FROM @CurrentAlterIndexWithClauseArguments\r\n ORDER BY ID ASC\r\n\r\n SET @CurrentAlterIndexWithClause = RTRIM(@CurrentAlterIndexWithClause)\r\n\r\n SET @CurrentAlterIndexWithClause = LEFT(@CurrentAlterIndexWithClause,LEN(@CurrentAlterIndexWithClause) - 1)\r\n\r\n SET @CurrentAlterIndexWithClause = @CurrentAlterIndexWithClause + ')'\r\n END\r\n\r\n IF @CurrentAlterIndexWithClause IS NOT NULL SET @CurrentCommand06 = @CurrentCommand06 + @CurrentAlterIndexWithClause\r\n\r\n EXECUTE @CurrentCommandOutput06 = [dbo].[CommandExecute] @Command = @CurrentCommand06, @CommandType = @CurrentCommandType06, @Mode = 2, @Comment = @CurrentComment06, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo06, @LockMessageSeverity = @LockMessageSeverity, @LogToTable = @LogToTable, @Execute = @Execute\r\n SET @Error = @@ERROR\r\n IF @Error <> 0 SET @CurrentCommandOutput06 = @Error\r\n IF @CurrentCommandOutput06 <> 0 SET @ReturnCode = @CurrentCommandOutput06\r\n\r\n IF @Delay > 0\r\n BEGIN\r\n SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')\r\n WAITFOR DELAY @CurrentDelay\r\n END\r\n END\r\n\r\n SET @CurrentMaxDOP = @MaxDOP\r\n\r\n IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = 'Y' AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)\r\n BEGIN\r\n SET @CurrentCommandType07 = 'UPDATE_STATISTICS'\r\n\r\n SET @CurrentCommand07 = ''\r\n IF @LockTimeout IS NOT NULL SET @CurrentCommand07 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '\r\n SET @CurrentCommand07 = @CurrentCommand07 + 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentStatisticsName)\r\n\r\n IF @CurrentMaxDOP IS NOT NULL AND ((@Version >= 12.06024 AND @Version < 13) OR (@Version >= 13.05026 AND @Version < 14) OR @Version >= 14.030154)\r\n BEGIN\r\n INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)\r\n SELECT 'MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)\r\n END\r\n\r\n IF @CurrentStatisticsSample = 100\r\n BEGIN\r\n INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)\r\n SELECT 'FULLSCAN'\r\n END\r\n\r\n IF @CurrentStatisticsSample IS NOT NULL AND @CurrentStatisticsSample <> 100\r\n BEGIN\r\n INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)\r\n SELECT 'SAMPLE ' + CAST(@CurrentStatisticsSample AS nvarchar) + ' PERCENT'\r\n END\r\n\r\n IF @CurrentStatisticsResample = 'Y'\r\n BEGIN\r\n INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)\r\n SELECT 'RESAMPLE'\r\n END\r\n\r\n IF @CurrentNoRecompute = 1\r\n BEGIN\r\n INSERT INTO @CurrentUpdateStatisticsWithClauseArguments (Argument)\r\n SELECT 'NORECOMPUTE'\r\n END\r\n\r\n IF EXISTS (SELECT * FROM @CurrentUpdateStatisticsWithClauseArguments)\r\n BEGIN\r\n SET @CurrentUpdateStatisticsWithClause = ' WITH'\r\n\r\n SELECT @CurrentUpdateStatisticsWithClause = @CurrentUpdateStatisticsWithClause + ' ' + Argument + ','\r\n FROM @CurrentUpdateStatisticsWithClauseArguments\r\n ORDER BY ID ASC\r\n\r\n SET @CurrentUpdateStatisticsWithClause = LEFT(@CurrentUpdateStatisticsWithClause,LEN(@CurrentUpdateStatisticsWithClause) - 1)\r\n END\r\n\r\n IF @CurrentUpdateStatisticsWithClause IS NOT NULL SET @CurrentCommand07 = @CurrentCommand07 + @CurrentUpdateStatisticsWithClause\r\n\r\n IF @PartitionLevelStatistics = 1 AND @CurrentIsIncremental = 1 AND @CurrentPartitionNumber IS NOT NULL SET @CurrentCommand07 = @CurrentCommand07 + ' ON PARTITIONS(' + CAST(@CurrentPartitionNumber AS nvarchar(max)) + ')'\r\n\r\n EXECUTE @CurrentCommandOutput07 = [dbo].[CommandExecute] @Command = @CurrentCommand07, @CommandType = @CurrentCommandType07, @Mode = 2, @Comment = @CurrentComment07, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @StatisticsName = @CurrentStatisticsName, @ExtendedInfo = @CurrentExtendedInfo07, @LockMessageSeverity = @LockMessageSeverity, @LogToTable = @LogToTable, @Execute = @Execute\r\n SET @Error = @@ERROR\r\n IF @Error <> 0 SET @CurrentCommandOutput07 = @Error\r\n IF @CurrentCommandOutput07 <> 0 SET @ReturnCode = @CurrentCommandOutput07\r\n END\r\n\r\n NoAction:\r\n\r\n -- Update that the index or statistics is completed\r\n UPDATE @tmpIndexesStatistics\r\n SET Completed = 1\r\n WHERE Selected = 1\r\n AND Completed = 0\r\n AND [Order] = @CurrentIxOrder\r\n AND ID = @CurrentIxID\r\n\r\n -- Clear variables\r\n SET @CurrentCommand02 = NULL\r\n SET @CurrentCommand03 = NULL\r\n SET @CurrentCommand04 = NULL\r\n SET @CurrentCommand05 = NULL\r\n SET @CurrentCommand06 = NULL\r\n SET @CurrentCommand07 = NULL\r\n\r\n SET @CurrentCommandOutput06 = NULL\r\n SET @CurrentCommandOutput07 = NULL\r\n\r\n SET @CurrentCommandType06 = NULL\r\n SET @CurrentCommandType07 = NULL\r\n\r\n SET @CurrentComment06 = NULL\r\n SET @CurrentComment07 = NULL\r\n\r\n SET @CurrentExtendedInfo06 = NULL\r\n SET @CurrentExtendedInfo07 = NULL\r\n\r\n SET @CurrentIxID = NULL\r\n SET @CurrentIxOrder = NULL\r\n SET @CurrentSchemaID = NULL\r\n SET @CurrentSchemaName = NULL\r\n SET @CurrentObjectID = NULL\r\n SET @CurrentObjectName = NULL\r\n SET @CurrentObjectType = NULL\r\n SET @CurrentIsMemoryOptimized = NULL\r\n SET @CurrentIndexID = NULL\r\n SET @CurrentIndexName = NULL\r\n SET @CurrentIndexType = NULL\r\n SET @CurrentStatisticsID = NULL\r\n SET @CurrentStatisticsName = NULL\r\n SET @CurrentPartitionID = NULL\r\n SET @CurrentPartitionNumber = NULL\r\n SET @CurrentPartitionCount = NULL\r\n SET @CurrentIsPartition = NULL\r\n SET @CurrentIndexExists = NULL\r\n SET @CurrentStatisticsExists = NULL\r\n SET @CurrentIsImageText = NULL\r\n SET @CurrentIsNewLOB = NULL\r\n SET @CurrentIsFileStream = NULL\r\n SET @CurrentIsColumnStore = NULL\r\n SET @CurrentIsComputed = NULL\r\n SET @CurrentIsTimestamp = NULL\r\n SET @CurrentAllowPageLocks = NULL\r\n SET @CurrentNoRecompute = NULL\r\n SET @CurrentIsIncremental = NULL\r\n SET @CurrentRowCount = NULL\r\n SET @CurrentModificationCounter = NULL\r\n SET @CurrentOnReadOnlyFileGroup = NULL\r\n SET @CurrentResumableIndexOperation = NULL\r\n SET @CurrentFragmentationLevel = NULL\r\n SET @CurrentPageCount = NULL\r\n SET @CurrentFragmentationGroup = NULL\r\n SET @CurrentAction = NULL\r\n SET @CurrentMaxDOP = NULL\r\n SET @CurrentUpdateStatistics = NULL\r\n SET @CurrentStatisticsSample = NULL\r\n SET @CurrentStatisticsResample = NULL\r\n SET @CurrentAlterIndexWithClause = NULL\r\n SET @CurrentUpdateStatisticsWithClause = NULL\r\n\r\n DELETE FROM @CurrentActionsAllowed\r\n DELETE FROM @CurrentAlterIndexWithClauseArguments\r\n DELETE FROM @CurrentUpdateStatisticsWithClauseArguments\r\n\r\n END\r\n\r\n END\r\n\r\n IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'SUSPECT'\r\n BEGIN\r\n SET @ErrorMessage = 'The database ' + QUOTENAME(@CurrentDatabaseName) + ' is in a SUSPECT state.' + CHAR(13) + CHAR(10) + ' '\r\n RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT\r\n SET @Error = @@ERROR\r\n END\r\n\r\n -- Update that the database is completed\r\n IF @DatabasesInParallel = 'Y'\r\n BEGIN\r\n UPDATE dbo.QueueDatabase\r\n SET DatabaseEndTime = GETDATE()\r\n WHERE QueueID = @QueueID\r\n AND DatabaseName = @CurrentDatabaseName\r\n END\r\n ELSE\r\n BEGIN\r\n UPDATE @tmpDatabases\r\n SET Completed = 1\r\n WHERE Selected = 1\r\n AND Completed = 0\r\n AND ID = @CurrentDBID\r\n END\r\n\r\n -- Clear variables\r\n SET @CurrentDBID = NULL\r\n SET @CurrentDatabaseID = NULL\r\n SET @CurrentDatabaseName = NULL\r\n SET @CurrentIsDatabaseAccessible = NULL\r\n SET @CurrentAvailabilityGroup = NULL\r\n SET @CurrentAvailabilityGroupRole = NULL\r\n SET @CurrentDatabaseMirroringRole = NULL\r\n SET @CurrentIsReadOnly = NULL\r\n\r\n SET @CurrentCommand01 = NULL\r\n\r\n DELETE FROM @tmpIndexesStatistics\r\n\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n --// Log completing information //--\r\n ----------------------------------------------------------------------------------------------------\r\n\r\n Logging:\r\n SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)\r\n RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT\r\n\r\n IF @ReturnCode <> 0\r\n BEGIN\r\n RETURN @ReturnCode\r\n END\r\n\r\n ----------------------------------------------------------------------------------------------------\r\n\r\nEND\r\n\r\nGO\r\n\r\n\r\n" } } }