USE master GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck] @Databases nvarchar(max) = NULL, @CheckCommands nvarchar(max) = 'CHECKDB', @PhysicalOnly nvarchar(max) = 'N', @DataPurity nvarchar(max) = 'N', @NoIndex nvarchar(max) = 'N', @ExtendedLogicalChecks nvarchar(max) = 'N', @TabLock nvarchar(max) = 'N', @FileGroups nvarchar(max) = NULL, @Objects nvarchar(max) = NULL, @MaxDOP int = NULL, @AvailabilityGroups nvarchar(max) = NULL, @AvailabilityGroupReplicas nvarchar(max) = 'ALL', @Updateability nvarchar(max) = 'ALL', @TimeLimit int = NULL, @LockTimeout int = NULL, @LockMessageSeverity int = 16, @StringDelimiter nvarchar(max) = ',', @DatabaseOrder nvarchar(max) = NULL, @DatabasesInParallel nvarchar(max) = 'N', @LogToTable nvarchar(max) = 'N', @Execute nvarchar(max) = 'Y' AS BEGIN ---------------------------------------------------------------------------------------------------- --// Source: https://ola.hallengren.com //-- --// License: https://ola.hallengren.com/license.html //-- --// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //-- --// Version: 2020-01-26 14:06:53 //-- ---------------------------------------------------------------------------------------------------- SET NOCOUNT ON DECLARE @StartMessage nvarchar(max) DECLARE @EndMessage nvarchar(max) DECLARE @DatabaseMessage nvarchar(max) DECLARE @ErrorMessage nvarchar(max) DECLARE @Severity int DECLARE @StartTime datetime2 = SYSDATETIME() DECLARE @SchemaName nvarchar(max) = OBJECT_SCHEMA_NAME(@@PROCID) DECLARE @ObjectName nvarchar(max) = OBJECT_NAME(@@PROCID) DECLARE @VersionTimestamp nvarchar(max) = SUBSTRING(OBJECT_DEFINITION(@@PROCID),CHARINDEX('--// Version: ',OBJECT_DEFINITION(@@PROCID)) + LEN('--// Version: ') + 1, 19) DECLARE @Parameters nvarchar(max) DECLARE @HostPlatform nvarchar(max) DECLARE @QueueID int DECLARE @QueueStartTime datetime2 DECLARE @CurrentDBID int DECLARE @CurrentDatabaseName nvarchar(max) DECLARE @CurrentDatabase_sp_executesql nvarchar(max) DECLARE @CurrentUserAccess nvarchar(max) DECLARE @CurrentIsReadOnly bit DECLARE @CurrentDatabaseState nvarchar(max) DECLARE @CurrentInStandby bit DECLARE @CurrentRecoveryModel nvarchar(max) DECLARE @CurrentIsDatabaseAccessible bit DECLARE @CurrentAvailabilityGroup nvarchar(max) DECLARE @CurrentAvailabilityGroupRole nvarchar(max) DECLARE @CurrentAvailabilityGroupBackupPreference nvarchar(max) DECLARE @CurrentIsPreferredBackupReplica bit DECLARE @CurrentDatabaseMirroringRole nvarchar(max) DECLARE @CurrentFGID int DECLARE @CurrentFileGroupID int DECLARE @CurrentFileGroupName nvarchar(max) DECLARE @CurrentFileGroupExists bit DECLARE @CurrentOID int DECLARE @CurrentSchemaID int DECLARE @CurrentSchemaName nvarchar(max) DECLARE @CurrentObjectID int DECLARE @CurrentObjectName nvarchar(max) DECLARE @CurrentObjectType nvarchar(max) DECLARE @CurrentObjectExists bit DECLARE @CurrentDatabaseContext nvarchar(max) DECLARE @CurrentCommand nvarchar(max) DECLARE @CurrentCommandOutput int DECLARE @CurrentCommandType nvarchar(max) DECLARE @Errors TABLE (ID int IDENTITY PRIMARY KEY, [Message] nvarchar(max) NOT NULL, Severity int NOT NULL, [State] int) DECLARE @CurrentMessage nvarchar(max) DECLARE @CurrentSeverity int DECLARE @CurrentState int DECLARE @tmpDatabases TABLE (ID int IDENTITY, DatabaseName nvarchar(max), DatabaseType nvarchar(max), AvailabilityGroup bit, [Snapshot] bit, StartPosition int, LastCommandTime datetime2, DatabaseSize bigint, LastGoodCheckDbTime datetime2, [Order] int, Selected bit, Completed bit, PRIMARY KEY(Selected, Completed, [Order], ID)) DECLARE @tmpAvailabilityGroups TABLE (ID int IDENTITY PRIMARY KEY, AvailabilityGroupName nvarchar(max), StartPosition int, Selected bit) DECLARE @tmpDatabasesAvailabilityGroups TABLE (DatabaseName nvarchar(max), AvailabilityGroupName nvarchar(max)) DECLARE @tmpFileGroups TABLE (ID int IDENTITY, FileGroupID int, FileGroupName nvarchar(max), StartPosition int, [Order] int, Selected bit, Completed bit, PRIMARY KEY(Selected, Completed, [Order], ID)) DECLARE @tmpObjects TABLE (ID int IDENTITY, SchemaID int, SchemaName nvarchar(max), ObjectID int, ObjectName nvarchar(max), ObjectType nvarchar(max), StartPosition int, [Order] int, Selected bit, Completed bit, PRIMARY KEY(Selected, Completed, [Order], ID)) DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max), DatabaseType nvarchar(max), AvailabilityGroup nvarchar(max), StartPosition int, Selected bit) DECLARE @SelectedAvailabilityGroups TABLE (AvailabilityGroupName nvarchar(max), StartPosition int, Selected bit) DECLARE @SelectedFileGroups TABLE (DatabaseName nvarchar(max), FileGroupName nvarchar(max), StartPosition int, Selected bit) DECLARE @SelectedObjects TABLE (DatabaseName nvarchar(max), SchemaName nvarchar(max), ObjectName nvarchar(max), StartPosition int, Selected bit) DECLARE @SelectedCheckCommands TABLE (CheckCommand nvarchar(max)) DECLARE @Error int = 0 DECLARE @ReturnCode int = 0 DECLARE @EmptyLine nvarchar(max) = CHAR(9) DECLARE @Version numeric(18,10) = 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)) IF @Version >= 14 BEGIN SELECT @HostPlatform = host_platform FROM sys.dm_os_host_info END ELSE BEGIN SET @HostPlatform = 'Windows' END DECLARE @AmazonRDS bit = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END ---------------------------------------------------------------------------------------------------- --// Log initial information //-- ---------------------------------------------------------------------------------------------------- SET @Parameters = '@Databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL') SET @Parameters += ', @CheckCommands = ' + ISNULL('''' + REPLACE(@CheckCommands,'''','''''') + '''','NULL') SET @Parameters += ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL') SET @Parameters += ', @DataPurity = ' + ISNULL('''' + REPLACE(@DataPurity,'''','''''') + '''','NULL') SET @Parameters += ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL') SET @Parameters += ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL') SET @Parameters += ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','NULL') SET @Parameters += ', @FileGroups = ' + ISNULL('''' + REPLACE(@FileGroups,'''','''''') + '''','NULL') SET @Parameters += ', @Objects = ' + ISNULL('''' + REPLACE(@Objects,'''','''''') + '''','NULL') SET @Parameters += ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL') SET @Parameters += ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL') SET @Parameters += ', @AvailabilityGroupReplicas = ' + ISNULL('''' + REPLACE(@AvailabilityGroupReplicas,'''','''''') + '''','NULL') SET @Parameters += ', @Updateability = ' + ISNULL('''' + REPLACE(@Updateability,'''','''''') + '''','NULL') SET @Parameters += ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL') SET @Parameters += ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL') SET @Parameters += ', @LockMessageSeverity = ' + ISNULL(CAST(@LockMessageSeverity AS nvarchar),'NULL') SET @Parameters += ', @StringDelimiter = ' + ISNULL('''' + REPLACE(@StringDelimiter,'''','''''') + '''','NULL') SET @Parameters += ', @DatabaseOrder = ' + ISNULL('''' + REPLACE(@DatabaseOrder,'''','''''') + '''','NULL') SET @Parameters += ', @DatabasesInParallel = ' + ISNULL('''' + REPLACE(@DatabasesInParallel,'''','''''') + '''','NULL') SET @Parameters += ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL') SET @Parameters += ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max)) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Platform: ' + @HostPlatform RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Parameters: ' + @Parameters RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Version: ' + @VersionTimestamp RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Source: https://ola.hallengren.com' RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT RAISERROR(@EmptyLine,10,1) WITH NOWAIT ---------------------------------------------------------------------------------------------------- --// Check core requirements //-- ---------------------------------------------------------------------------------------------------- IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.', 16, 1 END IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'ANSI_NULLS has to be set to ON for the stored procedure.', 16, 1 END IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.', 16, 1 END 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') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.', 16, 1 END 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 '%@DatabaseContext%') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.', 16, 1 END 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') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.', 16, 1 END 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') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The table Queue is missing. Download https://ola.hallengren.com/scripts/Queue.sql.', 16, 1 END 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') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The table QueueDatabase is missing. Download https://ola.hallengren.com/scripts/QueueDatabase.sql.', 16, 1 END IF @@TRANCOUNT <> 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The transaction count is not 0.', 16, 1 END ---------------------------------------------------------------------------------------------------- --// Select databases //-- ---------------------------------------------------------------------------------------------------- SET @Databases = REPLACE(@Databases, CHAR(10), '') SET @Databases = REPLACE(@Databases, CHAR(13), '') WHILE CHARINDEX(@StringDelimiter + ' ', @Databases) > 0 SET @Databases = REPLACE(@Databases, @StringDelimiter + ' ', @StringDelimiter) WHILE CHARINDEX(' ' + @StringDelimiter, @Databases) > 0 SET @Databases = REPLACE(@Databases, ' ' + @StringDelimiter, @StringDelimiter) SET @Databases = LTRIM(RTRIM(@Databases)); WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS ( SELECT 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, 1), 0), LEN(@Databases) + 1) AS EndPosition, SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, 1), 0), LEN(@Databases) + 1) - 1) AS DatabaseItem WHERE @Databases IS NOT NULL UNION ALL SELECT CAST(EndPosition AS int) + 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition, SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem FROM Databases1 WHERE EndPosition < LEN(@Databases) + 1 ), Databases2 (DatabaseItem, StartPosition, Selected) AS ( SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem, StartPosition, CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM Databases1 ), Databases3 (DatabaseItem, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS ( SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_DATABASES','AVAILABILITY_GROUP_DATABASES') THEN '%' ELSE DatabaseItem END AS DatabaseItem, CASE WHEN DatabaseItem = 'SYSTEM_DATABASES' THEN 'S' WHEN DatabaseItem = 'USER_DATABASES' THEN 'U' ELSE NULL END AS DatabaseType, CASE WHEN DatabaseItem = 'AVAILABILITY_GROUP_DATABASES' THEN 1 ELSE NULL END AvailabilityGroup, StartPosition, Selected FROM Databases2 ), Databases4 (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected) AS ( SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem, DatabaseType, AvailabilityGroup, StartPosition, Selected FROM Databases3 ) INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected) SELECT DatabaseName, DatabaseType, AvailabilityGroup, StartPosition, Selected FROM Databases4 OPTION (MAXRECURSION 0) IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1 BEGIN INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected) SELECT name AS AvailabilityGroupName, 0 AS Selected FROM sys.availability_groups INSERT INTO @tmpDatabasesAvailabilityGroups (DatabaseName, AvailabilityGroupName) SELECT databases.name, availability_groups.name FROM sys.databases databases INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON databases.replica_id = dm_hadr_availability_replica_states.replica_id INNER JOIN sys.availability_groups availability_groups ON dm_hadr_availability_replica_states.group_id = availability_groups.group_id END INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Snapshot], [Order], Selected, Completed) SELECT [name] AS DatabaseName, CASE WHEN name IN('master','msdb','model') OR is_distributor = 1 THEN 'S' ELSE 'U' END AS DatabaseType, NULL AS AvailabilityGroup, CASE WHEN source_database_id IS NOT NULL THEN 1 ELSE 0 END AS [Snapshot], 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.databases ORDER BY [name] ASC UPDATE tmpDatabases SET AvailabilityGroup = CASE WHEN EXISTS (SELECT * FROM @tmpDatabasesAvailabilityGroups WHERE DatabaseName = tmpDatabases.DatabaseName) THEN 1 ELSE 0 END FROM @tmpDatabases tmpDatabases UPDATE tmpDatabases SET tmpDatabases.Selected = SelectedDatabases.Selected FROM @tmpDatabases tmpDatabases INNER JOIN @SelectedDatabases SelectedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]') AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL) AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL) AND NOT ((tmpDatabases.DatabaseName = 'tempdb' OR tmpDatabases.[Snapshot] = 1) AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName) WHERE SelectedDatabases.Selected = 1 UPDATE tmpDatabases SET tmpDatabases.Selected = SelectedDatabases.Selected FROM @tmpDatabases tmpDatabases INNER JOIN @SelectedDatabases SelectedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]') AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL) AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL) AND NOT ((tmpDatabases.DatabaseName = 'tempdb' OR tmpDatabases.[Snapshot] = 1) AND tmpDatabases.DatabaseName <> SelectedDatabases.DatabaseName) WHERE SelectedDatabases.Selected = 0 UPDATE tmpDatabases SET tmpDatabases.StartPosition = SelectedDatabases2.StartPosition FROM @tmpDatabases tmpDatabases INNER JOIN (SELECT tmpDatabases.DatabaseName, MIN(SelectedDatabases.StartPosition) AS StartPosition FROM @tmpDatabases tmpDatabases INNER JOIN @SelectedDatabases SelectedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SelectedDatabases.DatabaseName,'_','[_]') AND (tmpDatabases.DatabaseType = SelectedDatabases.DatabaseType OR SelectedDatabases.DatabaseType IS NULL) AND (tmpDatabases.AvailabilityGroup = SelectedDatabases.AvailabilityGroup OR SelectedDatabases.AvailabilityGroup IS NULL) WHERE SelectedDatabases.Selected = 1 GROUP BY tmpDatabases.DatabaseName) SelectedDatabases2 ON tmpDatabases.DatabaseName = SelectedDatabases2.DatabaseName IF @Databases IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = '')) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Databases is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- --// Select availability groups //-- ---------------------------------------------------------------------------------------------------- IF @AvailabilityGroups IS NOT NULL AND @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1 BEGIN SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(10), '') SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(13), '') WHILE CHARINDEX(@StringDelimiter + ' ', @AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, @StringDelimiter + ' ', @StringDelimiter) WHILE CHARINDEX(' ' + @StringDelimiter, @AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, ' ' + @StringDelimiter, @StringDelimiter) SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups)); WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS ( SELECT 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition, SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) - 1) AS AvailabilityGroupItem WHERE @AvailabilityGroups IS NOT NULL UNION ALL SELECT CAST(EndPosition AS int) + 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition, SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) - EndPosition - 1) AS AvailabilityGroupItem FROM AvailabilityGroups1 WHERE EndPosition < LEN(@AvailabilityGroups) + 1 ), AvailabilityGroups2 (AvailabilityGroupItem, StartPosition, Selected) AS ( SELECT CASE WHEN AvailabilityGroupItem LIKE '-%' THEN RIGHT(AvailabilityGroupItem,LEN(AvailabilityGroupItem) - 1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem, StartPosition, CASE WHEN AvailabilityGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM AvailabilityGroups1 ), AvailabilityGroups3 (AvailabilityGroupItem, StartPosition, Selected) AS ( SELECT CASE WHEN AvailabilityGroupItem = 'ALL_AVAILABILITY_GROUPS' THEN '%' ELSE AvailabilityGroupItem END AS AvailabilityGroupItem, StartPosition, Selected FROM AvailabilityGroups2 ), AvailabilityGroups4 (AvailabilityGroupName, StartPosition, Selected) AS ( SELECT CASE WHEN LEFT(AvailabilityGroupItem,1) = '[' AND RIGHT(AvailabilityGroupItem,1) = ']' THEN PARSENAME(AvailabilityGroupItem,1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem, StartPosition, Selected FROM AvailabilityGroups3 ) INSERT INTO @SelectedAvailabilityGroups (AvailabilityGroupName, StartPosition, Selected) SELECT AvailabilityGroupName, StartPosition, Selected FROM AvailabilityGroups4 OPTION (MAXRECURSION 0) UPDATE tmpAvailabilityGroups SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected FROM @tmpAvailabilityGroups tmpAvailabilityGroups INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]') WHERE SelectedAvailabilityGroups.Selected = 1 UPDATE tmpAvailabilityGroups SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected FROM @tmpAvailabilityGroups tmpAvailabilityGroups INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]') WHERE SelectedAvailabilityGroups.Selected = 0 UPDATE tmpAvailabilityGroups SET tmpAvailabilityGroups.StartPosition = SelectedAvailabilityGroups2.StartPosition FROM @tmpAvailabilityGroups tmpAvailabilityGroups INNER JOIN (SELECT tmpAvailabilityGroups.AvailabilityGroupName, MIN(SelectedAvailabilityGroups.StartPosition) AS StartPosition FROM @tmpAvailabilityGroups tmpAvailabilityGroups INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]') WHERE SelectedAvailabilityGroups.Selected = 1 GROUP BY tmpAvailabilityGroups.AvailabilityGroupName) SelectedAvailabilityGroups2 ON tmpAvailabilityGroups.AvailabilityGroupName = SelectedAvailabilityGroups2.AvailabilityGroupName UPDATE tmpDatabases SET tmpDatabases.StartPosition = tmpAvailabilityGroups.StartPosition, tmpDatabases.Selected = 1 FROM @tmpDatabases tmpDatabases INNER JOIN @tmpDatabasesAvailabilityGroups tmpDatabasesAvailabilityGroups ON tmpDatabases.DatabaseName = tmpDatabasesAvailabilityGroups.DatabaseName INNER JOIN @tmpAvailabilityGroups tmpAvailabilityGroups ON tmpDatabasesAvailabilityGroups.AvailabilityGroupName = tmpAvailabilityGroups.AvailabilityGroupName WHERE tmpAvailabilityGroups.Selected = 1 END 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) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @AvailabilityGroups is not supported.', 16, 1 END IF (@Databases IS NULL AND @AvailabilityGroups IS NULL) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'You need to specify one of the parameters @Databases and @AvailabilityGroups.', 16, 2 END IF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'You can only specify one of the parameters @Databases and @AvailabilityGroups.', 16, 3 END ---------------------------------------------------------------------------------------------------- --// Select filegroups //-- ---------------------------------------------------------------------------------------------------- SET @FileGroups = REPLACE(@FileGroups, CHAR(10), '') SET @FileGroups = REPLACE(@FileGroups, CHAR(13), '') WHILE CHARINDEX(@StringDelimiter + ' ', @FileGroups) > 0 SET @FileGroups = REPLACE(@FileGroups, @StringDelimiter + ' ', @StringDelimiter) WHILE CHARINDEX(' ' + @StringDelimiter, @FileGroups) > 0 SET @FileGroups = REPLACE(@FileGroups, ' ' + @StringDelimiter, @StringDelimiter) SET @FileGroups = LTRIM(RTRIM(@FileGroups)); WITH FileGroups1 (StartPosition, EndPosition, FileGroupItem) AS ( SELECT 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FileGroups, 1), 0), LEN(@FileGroups) + 1) AS EndPosition, SUBSTRING(@FileGroups, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FileGroups, 1), 0), LEN(@FileGroups) + 1) - 1) AS FileGroupItem WHERE @FileGroups IS NOT NULL UNION ALL SELECT CAST(EndPosition AS int) + 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) AS EndPosition, SUBSTRING(@FileGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) - EndPosition - 1) AS FileGroupItem FROM FileGroups1 WHERE EndPosition < LEN(@FileGroups) + 1 ), FileGroups2 (FileGroupItem, StartPosition, Selected) AS ( SELECT CASE WHEN FileGroupItem LIKE '-%' THEN RIGHT(FileGroupItem,LEN(FileGroupItem) - 1) ELSE FileGroupItem END AS FileGroupItem, StartPosition, CASE WHEN FileGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM FileGroups1 ), FileGroups3 (FileGroupItem, StartPosition, Selected) AS ( SELECT CASE WHEN FileGroupItem = 'ALL_FILEGROUPS' THEN '%.%' ELSE FileGroupItem END AS FileGroupItem, StartPosition, Selected FROM FileGroups2 ), FileGroups4 (DatabaseName, FileGroupName, StartPosition, Selected) AS ( SELECT CASE WHEN PARSENAME(FileGroupItem,4) IS NULL AND PARSENAME(FileGroupItem,3) IS NULL THEN PARSENAME(FileGroupItem,2) ELSE NULL END AS DatabaseName, CASE WHEN PARSENAME(FileGroupItem,4) IS NULL AND PARSENAME(FileGroupItem,3) IS NULL THEN PARSENAME(FileGroupItem,1) ELSE NULL END AS FileGroupName, StartPosition, Selected FROM FileGroups3 ) INSERT INTO @SelectedFileGroups (DatabaseName, FileGroupName, StartPosition, Selected) SELECT DatabaseName, FileGroupName, StartPosition, Selected FROM FileGroups4 OPTION (MAXRECURSION 0) ---------------------------------------------------------------------------------------------------- --// Select objects //-- ---------------------------------------------------------------------------------------------------- SET @Objects = REPLACE(@Objects, CHAR(10), '') SET @Objects = REPLACE(@Objects, CHAR(13), '') WHILE CHARINDEX(@StringDelimiter + ' ', @Objects) > 0 SET @Objects = REPLACE(@Objects, @StringDelimiter + ' ', @StringDelimiter) WHILE CHARINDEX(' ' + @StringDelimiter, @Objects) > 0 SET @Objects = REPLACE(@Objects, ' ' + @StringDelimiter, @StringDelimiter) SET @Objects = LTRIM(RTRIM(@Objects)); WITH Objects1 (StartPosition, EndPosition, ObjectItem) AS ( SELECT 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Objects, 1), 0), LEN(@Objects) + 1) AS EndPosition, SUBSTRING(@Objects, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Objects, 1), 0), LEN(@Objects) + 1) - 1) AS ObjectItem WHERE @Objects IS NOT NULL UNION ALL SELECT CAST(EndPosition AS int) + 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) AS EndPosition, SUBSTRING(@Objects, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) - EndPosition - 1) AS ObjectItem FROM Objects1 WHERE EndPosition < LEN(@Objects) + 1 ), Objects2 (ObjectItem, StartPosition, Selected) AS ( SELECT CASE WHEN ObjectItem LIKE '-%' THEN RIGHT(ObjectItem,LEN(ObjectItem) - 1) ELSE ObjectItem END AS ObjectItem, StartPosition, CASE WHEN ObjectItem LIKE '-%' THEN 0 ELSE 1 END AS Selected FROM Objects1 ), Objects3 (ObjectItem, StartPosition, Selected) AS ( SELECT CASE WHEN ObjectItem = 'ALL_OBJECTS' THEN '%.%.%' ELSE ObjectItem END AS ObjectItem, StartPosition, Selected FROM Objects2 ), Objects4 (DatabaseName, SchemaName, ObjectName, StartPosition, Selected) AS ( SELECT CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,3) ELSE NULL END AS DatabaseName, CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,2) ELSE NULL END AS SchemaName, CASE WHEN PARSENAME(ObjectItem,4) IS NULL THEN PARSENAME(ObjectItem,1) ELSE NULL END AS ObjectName, StartPosition, Selected FROM Objects3 ) INSERT INTO @SelectedObjects (DatabaseName, SchemaName, ObjectName, StartPosition, Selected) SELECT DatabaseName, SchemaName, ObjectName, StartPosition, Selected FROM Objects4 OPTION (MAXRECURSION 0) ---------------------------------------------------------------------------------------------------- --// Select check commands //-- ---------------------------------------------------------------------------------------------------- SET @CheckCommands = REPLACE(@CheckCommands, @StringDelimiter + ' ', @StringDelimiter); WITH CheckCommands (StartPosition, EndPosition, CheckCommand) AS ( SELECT 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) AS EndPosition, SUBSTRING(@CheckCommands, 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) - 1) AS CheckCommand WHERE @CheckCommands IS NOT NULL UNION ALL SELECT CAST(EndPosition AS int) + 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @CheckCommands, EndPosition + 1), 0), LEN(@CheckCommands) + 1) AS EndPosition, SUBSTRING(@CheckCommands, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(@StringDelimiter, @CheckCommands, EndPosition + 1), 0), LEN(@CheckCommands) + 1) - EndPosition - 1) AS CheckCommand FROM CheckCommands WHERE EndPosition < LEN(@CheckCommands) + 1 ) INSERT INTO @SelectedCheckCommands (CheckCommand) SELECT CheckCommand FROM CheckCommands OPTION (MAXRECURSION 0) ---------------------------------------------------------------------------------------------------- --// Check input parameters //-- ---------------------------------------------------------------------------------------------------- IF EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand NOT IN('CHECKDB','CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG')) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @CheckCommands is not supported.', 16, 1 END IF EXISTS (SELECT * FROM @SelectedCheckCommands GROUP BY CheckCommand HAVING COUNT(*) > 1) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @CheckCommands is not supported.', 16, 2 END IF NOT EXISTS (SELECT * FROM @SelectedCheckCommands) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @CheckCommands is not supported.' , 16, 3 END IF EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKDB')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG')) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @CheckCommands is not supported.', 16, 4 END IF EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKALLOC','CHECKTABLE')) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @CheckCommands is not supported.', 16, 5 END ---------------------------------------------------------------------------------------------------- IF @PhysicalOnly NOT IN ('Y','N') OR @PhysicalOnly IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @PhysicalOnly is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @DataPurity NOT IN ('Y','N') OR @DataPurity IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DataPurity is not supported.', 16, 1 END IF @PhysicalOnly = 'Y' AND @DataPurity = 'Y' BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The parameters @PhysicalOnly and @DataPurity cannot be used together.', 16, 2 END ---------------------------------------------------------------------------------------------------- IF @NoIndex NOT IN ('Y','N') OR @NoIndex IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @NoIndex is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @ExtendedLogicalChecks is not supported.', 16, 1 END IF @PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y' BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The parameters @PhysicalOnly and @ExtendedLogicalChecks cannot be used together.', 16, 2 END ---------------------------------------------------------------------------------------------------- IF @TabLock NOT IN ('Y','N') OR @TabLock IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @TabLock is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF EXISTS(SELECT * FROM @SelectedFileGroups WHERE DatabaseName IS NULL OR FileGroupName IS NULL) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @FileGroups is not supported.', 16, 1 END IF @FileGroups IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedFileGroups) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @FileGroups is not supported.', 16, 2 END IF @FileGroups IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @FileGroups is not supported.', 16, 3 END ---------------------------------------------------------------------------------------------------- IF EXISTS(SELECT * FROM @SelectedObjects WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Objects is not supported.', 16, 1 END IF (@Objects IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedObjects)) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Objects is not supported.', 16, 2 END IF (@Objects IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE')) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Objects is not supported.', 16, 3 END ---------------------------------------------------------------------------------------------------- IF @MaxDOP < 0 OR @MaxDOP > 64 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @MaxDOP is not supported.', 16, 1 END IF @MaxDOP IS NOT NULL AND NOT (@Version >= 12.050000 OR SERVERPROPERTY('EngineEdition') IN (5, 8)) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @MaxDOP is not supported. MAXDOP is not available in this version of SQL Server.', 16, 2 END ---------------------------------------------------------------------------------------------------- IF @AvailabilityGroupReplicas NOT IN('ALL','PRIMARY','SECONDARY','PREFERRED_BACKUP_REPLICA') OR @AvailabilityGroupReplicas IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @AvailabilityGroupReplicas is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @Updateability NOT IN('READ_ONLY','READ_WRITE','ALL') OR @Updateability IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Updateability is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @TimeLimit < 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @TimeLimit is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @LockTimeout < 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @LockTimeout is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @LockMessageSeverity NOT IN(10, 16) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @LockMessageSeverity is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @StringDelimiter IS NULL OR LEN(@StringDelimiter) > 1 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @StringDelimiter is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @DatabaseOrder NOT IN('DATABASE_NAME_ASC','DATABASE_NAME_DESC','DATABASE_SIZE_ASC','DATABASE_SIZE_DESC','DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC','REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabaseOrder is not supported.', 16, 1 END IF @DatabaseOrder IN('DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC') AND NOT ((@Version >= 12.06024 AND @Version < 13) OR (@Version >= 13.05026 AND @Version < 14) OR @Version >= 14.0302916) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabaseOrder is not supported. DATABASEPROPERTYEX(''DatabaseName'', ''LastGoodCheckDbTime'') is not available in this version of SQL Server.', 16, 2 END IF @DatabaseOrder IN('REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') AND @LogToTable = 'N' BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabaseOrder is not supported. You need to provide the parameter @LogToTable = ''Y''.', 16, 3 END IF @DatabaseOrder IN('DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC','REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') AND @CheckCommands <> 'CHECKDB' BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabaseOrder is not supported. You need to provide the parameter @CheckCommands = ''CHECKDB''.', 16, 4 END IF @DatabaseOrder IS NOT NULL AND SERVERPROPERTY('EngineEdition') = 5 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabaseOrder is not supported. This parameter is not supported in Azure SQL Database.', 16, 5 END ---------------------------------------------------------------------------------------------------- IF @DatabasesInParallel NOT IN('Y','N') OR @DatabasesInParallel IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 1 END IF @DatabasesInParallel = 'Y' AND SERVERPROPERTY('EngineEdition') = 5 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabasesInParallel is not supported. This parameter is not supported in Azure SQL Database.', 16, 2 END ---------------------------------------------------------------------------------------------------- IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @LogToTable is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF @Execute NOT IN('Y','N') OR @Execute IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Execute is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- IF EXISTS(SELECT * FROM @Errors) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The documentation is available at https://ola.hallengren.com/sql-server-integrity-check.html.', 16, 1 END ---------------------------------------------------------------------------------------------------- --// Check that selected databases and availability groups exist //-- ---------------------------------------------------------------------------------------------------- SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', ' FROM @SelectedDatabases WHERE DatabaseName NOT LIKE '%[%]%' AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases) IF @@ROWCOUNT > 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The following databases in the @Databases parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1 END SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', ' FROM @SelectedFileGroups WHERE DatabaseName NOT LIKE '%[%]%' AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases) IF @@ROWCOUNT > 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The following databases in the @FileGroups parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1 END SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', ' FROM @SelectedObjects WHERE DatabaseName NOT LIKE '%[%]%' AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases) IF @@ROWCOUNT > 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The following databases in the @Objects parameter do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1 END SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(AvailabilityGroupName) + ', ' FROM @SelectedAvailabilityGroups WHERE AvailabilityGroupName NOT LIKE '%[%]%' AND AvailabilityGroupName NOT IN (SELECT AvailabilityGroupName FROM @tmpAvailabilityGroups) IF @@ROWCOUNT > 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The following availability groups do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1 END SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', ' FROM @SelectedFileGroups WHERE DatabaseName NOT LIKE '%[%]%' AND DatabaseName IN (SELECT DatabaseName FROM @tmpDatabases) AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases WHERE Selected = 1) IF @@ROWCOUNT > 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The following databases have been selected in the @FileGroups parameter, but not in the @Databases or @AvailabilityGroups parameters: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1 END SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + ', ' FROM @SelectedObjects WHERE DatabaseName NOT LIKE '%[%]%' AND DatabaseName IN (SELECT DatabaseName FROM @tmpDatabases) AND DatabaseName NOT IN (SELECT DatabaseName FROM @tmpDatabases WHERE Selected = 1) IF @@ROWCOUNT > 0 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The following databases have been selected in the @Objects parameter, but not in the @Databases or @AvailabilityGroups parameters: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.', 10, 1 END ---------------------------------------------------------------------------------------------------- --// Check @@SERVERNAME //-- ---------------------------------------------------------------------------------------------------- IF @@SERVERNAME <> CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) AND SERVERPROPERTY('IsHadrEnabled') = 1 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The @@SERVERNAME does not match SERVERPROPERTY(''ServerName''). See ' + CASE WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server' WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/rename-a-sql-server-failover-cluster-instance' END + '.', 16, 1 END ---------------------------------------------------------------------------------------------------- --// Raise errors //-- ---------------------------------------------------------------------------------------------------- DECLARE ErrorCursor CURSOR FAST_FORWARD FOR SELECT [Message], Severity, [State] FROM @Errors ORDER BY [ID] ASC OPEN ErrorCursor FETCH ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState WHILE @@FETCH_STATUS = 0 BEGIN RAISERROR('%s', @CurrentSeverity, @CurrentState, @CurrentMessage) WITH NOWAIT RAISERROR(@EmptyLine, 10, 1) WITH NOWAIT FETCH NEXT FROM ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState END CLOSE ErrorCursor DEALLOCATE ErrorCursor IF EXISTS (SELECT * FROM @Errors WHERE Severity >= 16) BEGIN SET @ReturnCode = 50000 GOTO Logging END ---------------------------------------------------------------------------------------------------- --// Update database order //-- ---------------------------------------------------------------------------------------------------- IF @DatabaseOrder IN('DATABASE_SIZE_ASC','DATABASE_SIZE_DESC') BEGIN UPDATE tmpDatabases SET DatabaseSize = (SELECT SUM(CAST(size AS bigint)) FROM sys.master_files WHERE [type] = 0 AND database_id = DB_ID(tmpDatabases.DatabaseName)) FROM @tmpDatabases tmpDatabases END IF @DatabaseOrder IN('DATABASE_LAST_GOOD_CHECK_ASC','DATABASE_LAST_GOOD_CHECK_DESC') BEGIN UPDATE tmpDatabases SET LastGoodCheckDbTime = NULLIF(CAST(DATABASEPROPERTYEX (DatabaseName,'LastGoodCheckDbTime') AS datetime2),'1900-01-01 00:00:00.000') FROM @tmpDatabases tmpDatabases END IF @DatabaseOrder IN('REPLICA_LAST_GOOD_CHECK_ASC','REPLICA_LAST_GOOD_CHECK_DESC') BEGIN UPDATE tmpDatabases SET LastCommandTime = MaxStartTime FROM @tmpDatabases tmpDatabases INNER JOIN (SELECT DatabaseName, MAX(StartTime) AS MaxStartTime FROM dbo.CommandLog WHERE CommandType = 'DBCC_CHECKDB' AND ErrorNumber = 0 GROUP BY DatabaseName) CommandLog ON tmpDatabases.DatabaseName = CommandLog.DatabaseName COLLATE DATABASE_DEFAULT END IF @DatabaseOrder IS NULL BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY StartPosition ASC, DatabaseName ASC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'DATABASE_NAME_ASC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName ASC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'DATABASE_NAME_DESC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseName DESC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'DATABASE_SIZE_ASC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize ASC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'DATABASE_SIZE_DESC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY DatabaseSize DESC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'DATABASE_LAST_GOOD_CHECK_ASC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LastGoodCheckDbTime ASC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'DATABASE_LAST_GOOD_CHECK_DESC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LastGoodCheckDbTime DESC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'REPLICA_LAST_GOOD_CHECK_ASC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LastCommandTime ASC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ELSE IF @DatabaseOrder = 'REPLICA_LAST_GOOD_CHECK_DESC' BEGIN WITH tmpDatabases AS ( SELECT DatabaseName, [Order], ROW_NUMBER() OVER (ORDER BY LastCommandTime DESC) AS RowNumber FROM @tmpDatabases tmpDatabases WHERE Selected = 1 ) UPDATE tmpDatabases SET [Order] = RowNumber END ---------------------------------------------------------------------------------------------------- --// Update the queue //-- ---------------------------------------------------------------------------------------------------- IF @DatabasesInParallel = 'Y' BEGIN BEGIN TRY SELECT @QueueID = QueueID FROM dbo.[Queue] WHERE SchemaName = @SchemaName AND ObjectName = @ObjectName AND [Parameters] = @Parameters IF @QueueID IS NULL BEGIN BEGIN TRANSACTION SELECT @QueueID = QueueID FROM dbo.[Queue] WITH (UPDLOCK, HOLDLOCK) WHERE SchemaName = @SchemaName AND ObjectName = @ObjectName AND [Parameters] = @Parameters IF @QueueID IS NULL BEGIN INSERT INTO dbo.[Queue] (SchemaName, ObjectName, [Parameters]) SELECT @SchemaName, @ObjectName, @Parameters SET @QueueID = SCOPE_IDENTITY() END COMMIT TRANSACTION END BEGIN TRANSACTION UPDATE [Queue] SET QueueStartTime = SYSDATETIME(), SessionID = @@SPID, RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID), RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID) FROM dbo.[Queue] [Queue] WHERE QueueID = @QueueID AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests WHERE session_id = [Queue].SessionID AND request_id = [Queue].RequestID AND start_time = [Queue].RequestStartTime) AND NOT EXISTS (SELECT * FROM dbo.QueueDatabase QueueDatabase INNER JOIN sys.dm_exec_requests ON QueueDatabase.SessionID = session_id AND QueueDatabase.RequestID = request_id AND QueueDatabase.RequestStartTime = start_time WHERE QueueDatabase.QueueID = @QueueID) IF @@ROWCOUNT = 1 BEGIN INSERT INTO dbo.QueueDatabase (QueueID, DatabaseName) SELECT @QueueID AS QueueID, DatabaseName FROM @tmpDatabases tmpDatabases WHERE Selected = 1 AND NOT EXISTS (SELECT * FROM dbo.QueueDatabase WHERE DatabaseName = tmpDatabases.DatabaseName AND QueueID = @QueueID) DELETE QueueDatabase FROM dbo.QueueDatabase QueueDatabase WHERE QueueID = @QueueID AND NOT EXISTS (SELECT * FROM @tmpDatabases tmpDatabases WHERE DatabaseName = QueueDatabase.DatabaseName AND Selected = 1) UPDATE QueueDatabase SET DatabaseOrder = tmpDatabases.[Order] FROM dbo.QueueDatabase QueueDatabase INNER JOIN @tmpDatabases tmpDatabases ON QueueDatabase.DatabaseName = tmpDatabases.DatabaseName WHERE QueueID = @QueueID END COMMIT TRANSACTION SELECT @QueueStartTime = QueueStartTime FROM dbo.[Queue] WHERE QueueID = @QueueID END TRY BEGIN CATCH IF XACT_STATE() <> 0 BEGIN ROLLBACK TRANSACTION END SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT RAISERROR(@EmptyLine,10,1) WITH NOWAIT SET @ReturnCode = ERROR_NUMBER() GOTO Logging END CATCH END ---------------------------------------------------------------------------------------------------- --// Execute commands //-- ---------------------------------------------------------------------------------------------------- WHILE (1 = 1) BEGIN IF @DatabasesInParallel = 'Y' BEGIN UPDATE QueueDatabase SET DatabaseStartTime = NULL, SessionID = NULL, RequestID = NULL, RequestStartTime = NULL FROM dbo.QueueDatabase QueueDatabase WHERE QueueID = @QueueID AND DatabaseStartTime IS NOT NULL AND DatabaseEndTime IS NULL AND NOT EXISTS (SELECT * FROM sys.dm_exec_requests WHERE session_id = QueueDatabase.SessionID AND request_id = QueueDatabase.RequestID AND start_time = QueueDatabase.RequestStartTime) UPDATE QueueDatabase SET DatabaseStartTime = SYSDATETIME(), DatabaseEndTime = NULL, SessionID = @@SPID, RequestID = (SELECT request_id FROM sys.dm_exec_requests WHERE session_id = @@SPID), RequestStartTime = (SELECT start_time FROM sys.dm_exec_requests WHERE session_id = @@SPID), @CurrentDatabaseName = DatabaseName FROM (SELECT TOP 1 DatabaseStartTime, DatabaseEndTime, SessionID, RequestID, RequestStartTime, DatabaseName FROM dbo.QueueDatabase WHERE QueueID = @QueueID AND (DatabaseStartTime < @QueueStartTime OR DatabaseStartTime IS NULL) AND NOT (DatabaseStartTime IS NOT NULL AND DatabaseEndTime IS NULL) ORDER BY DatabaseOrder ASC ) QueueDatabase END ELSE BEGIN SELECT TOP 1 @CurrentDBID = ID, @CurrentDatabaseName = DatabaseName FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0 ORDER BY [Order] ASC END IF @@ROWCOUNT = 0 BEGIN BREAK END SET @CurrentDatabase_sp_executesql = QUOTENAME(@CurrentDatabaseName) + '.sys.sp_executesql' BEGIN SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),120) RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'Database: ' + QUOTENAME(@CurrentDatabaseName) RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT END SELECT @CurrentUserAccess = user_access_desc, @CurrentIsReadOnly = is_read_only, @CurrentDatabaseState = state_desc, @CurrentInStandby = is_in_standby, @CurrentRecoveryModel = recovery_model_desc FROM sys.databases WHERE [name] = @CurrentDatabaseName BEGIN SET @DatabaseMessage = 'State: ' + @CurrentDatabaseState RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'Standby: ' + CASE WHEN @CurrentInStandby = 1 THEN 'Yes' ELSE 'No' END RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'Updateability: ' + CASE WHEN @CurrentIsReadOnly = 1 THEN 'READ_ONLY' WHEN @CurrentIsReadOnly = 0 THEN 'READ_WRITE' END RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'User access: ' + @CurrentUserAccess RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'Recovery model: ' + @CurrentRecoveryModel RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT END IF @CurrentDatabaseState = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5 BEGIN IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = DB_ID(@CurrentDatabaseName) AND database_guid IS NOT NULL) BEGIN SET @CurrentIsDatabaseAccessible = 1 END ELSE BEGIN SET @CurrentIsDatabaseAccessible = 0 END END IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1 BEGIN SELECT @CurrentAvailabilityGroup = availability_groups.name, @CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc, @CurrentAvailabilityGroupBackupPreference = UPPER(availability_groups.automated_backup_preference_desc) FROM sys.databases databases INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON databases.replica_id = dm_hadr_availability_replica_states.replica_id INNER JOIN sys.availability_groups availability_groups ON dm_hadr_availability_replica_states.group_id = availability_groups.group_id WHERE databases.name = @CurrentDatabaseName END IF @Version >= 11 AND SERVERPROPERTY('IsHadrEnabled') = 1 AND @CurrentAvailabilityGroup IS NOT NULL AND @AvailabilityGroupReplicas = 'PREFERRED_BACKUP_REPLICA' BEGIN SELECT @CurrentIsPreferredBackupReplica = sys.fn_hadr_backup_is_preferred_replica(@CurrentDatabaseName) END IF SERVERPROPERTY('EngineEdition') <> 5 BEGIN SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc) FROM sys.database_mirroring WHERE database_id = DB_ID(@CurrentDatabaseName) END IF @CurrentIsDatabaseAccessible IS NOT NULL BEGIN SET @DatabaseMessage = 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT END IF @CurrentAvailabilityGroup IS NOT NULL BEGIN SET @DatabaseMessage = 'Availability group: ' + ISNULL(@CurrentAvailabilityGroup,'N/A') RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'Availability group role: ' + ISNULL(@CurrentAvailabilityGroupRole,'N/A') RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT IF @AvailabilityGroupReplicas = 'PREFERRED_BACKUP_REPLICA' BEGIN SET @DatabaseMessage = 'Availability group backup preference: ' + ISNULL(@CurrentAvailabilityGroupBackupPreference,'N/A') RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT SET @DatabaseMessage = 'Is preferred backup replica: ' + CASE WHEN @CurrentIsPreferredBackupReplica = 1 THEN 'Yes' WHEN @CurrentIsPreferredBackupReplica = 0 THEN 'No' ELSE 'N/A' END RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT END END IF @CurrentDatabaseMirroringRole IS NOT NULL BEGIN SET @DatabaseMessage = 'Database mirroring role: ' + @CurrentDatabaseMirroringRole RAISERROR('%s',10,1,@DatabaseMessage) WITH NOWAIT END RAISERROR(@EmptyLine,10,1) WITH NOWAIT IF @CurrentDatabaseState = 'ONLINE' AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0) AND (@CurrentAvailabilityGroupRole = 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL OR SERVERPROPERTY('EngineEdition') = 3) AND ((@AvailabilityGroupReplicas = 'PRIMARY' AND @CurrentAvailabilityGroupRole = 'PRIMARY') OR (@AvailabilityGroupReplicas = 'SECONDARY' AND @CurrentAvailabilityGroupRole = 'SECONDARY') OR (@AvailabilityGroupReplicas = 'PREFERRED_BACKUP_REPLICA' AND @CurrentIsPreferredBackupReplica = 1) OR @AvailabilityGroupReplicas = 'ALL' OR @CurrentAvailabilityGroupRole IS NULL) AND NOT (@CurrentIsReadOnly = 1 AND @Updateability = 'READ_WRITE') AND NOT (@CurrentIsReadOnly = 0 AND @Updateability = 'READ_ONLY') BEGIN -- Check database IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKDB') AND (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SET @CurrentDatabaseContext = CASE WHEN SERVERPROPERTY('EngineEdition') = 5 THEN @CurrentDatabaseName ELSE 'master' END SET @CurrentCommandType = 'DBCC_CHECKDB' SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabaseName) IF @NoIndex = 'Y' SET @CurrentCommand += ', NOINDEX' SET @CurrentCommand += ') WITH NO_INFOMSGS, ALL_ERRORMSGS' IF @DataPurity = 'Y' SET @CurrentCommand += ', DATA_PURITY' IF @PhysicalOnly = 'Y' SET @CurrentCommand += ', PHYSICAL_ONLY' IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand += ', EXTENDED_LOGICAL_CHECKS' IF @TabLock = 'Y' SET @CurrentCommand += ', TABLOCK' IF @MaxDOP IS NOT NULL SET @CurrentCommand += ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar) EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute SET @Error = @@ERROR IF @Error <> 0 SET @CurrentCommandOutput = @Error IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput END -- Check filegroups IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP') AND (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SET @CurrentCommand = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT data_space_id AS FileGroupID, name AS FileGroupName, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.filegroups filegroups WHERE [type] <> ''FX'' ORDER BY CASE WHEN filegroups.name = ''PRIMARY'' THEN 1 ELSE 0 END DESC, filegroups.name ASC' INSERT INTO @tmpFileGroups (FileGroupID, FileGroupName, [Order], Selected, Completed) EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand SET @Error = @@ERROR IF @Error <> 0 SET @ReturnCode = @Error IF @FileGroups IS NULL BEGIN UPDATE tmpFileGroups SET tmpFileGroups.Selected = 1 FROM @tmpFileGroups tmpFileGroups END ELSE BEGIN UPDATE tmpFileGroups SET tmpFileGroups.Selected = SelectedFileGroups.Selected FROM @tmpFileGroups tmpFileGroups INNER JOIN @SelectedFileGroups SelectedFileGroups ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName,'_','[_]') AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName,'_','[_]') WHERE SelectedFileGroups.Selected = 1 UPDATE tmpFileGroups SET tmpFileGroups.Selected = SelectedFileGroups.Selected FROM @tmpFileGroups tmpFileGroups INNER JOIN @SelectedFileGroups SelectedFileGroups ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName,'_','[_]') AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName,'_','[_]') WHERE SelectedFileGroups.Selected = 0 UPDATE tmpFileGroups SET tmpFileGroups.StartPosition = SelectedFileGroups2.StartPosition FROM @tmpFileGroups tmpFileGroups INNER JOIN (SELECT tmpFileGroups.FileGroupName, MIN(SelectedFileGroups.StartPosition) AS StartPosition FROM @tmpFileGroups tmpFileGroups INNER JOIN @SelectedFileGroups SelectedFileGroups ON @CurrentDatabaseName LIKE REPLACE(SelectedFileGroups.DatabaseName,'_','[_]') AND tmpFileGroups.FileGroupName LIKE REPLACE(SelectedFileGroups.FileGroupName,'_','[_]') WHERE SelectedFileGroups.Selected = 1 GROUP BY tmpFileGroups.FileGroupName) SelectedFileGroups2 ON tmpFileGroups.FileGroupName = SelectedFileGroups2.FileGroupName END; WITH tmpFileGroups AS ( SELECT FileGroupName, [Order], ROW_NUMBER() OVER (ORDER BY StartPosition ASC, FileGroupName ASC) AS RowNumber FROM @tmpFileGroups tmpFileGroups WHERE Selected = 1 ) UPDATE tmpFileGroups SET [Order] = RowNumber SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + '.' + QUOTENAME(FileGroupName) + ', ' FROM @SelectedFileGroups SelectedFileGroups WHERE DatabaseName = @CurrentDatabaseName AND FileGroupName NOT LIKE '%[%]%' AND NOT EXISTS (SELECT * FROM @tmpFileGroups WHERE FileGroupName = SelectedFileGroups.FileGroupName) IF @@ROWCOUNT > 0 BEGIN SET @ErrorMessage = 'The following file groups do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT SET @Error = @@ERROR RAISERROR(@EmptyLine,10,1) WITH NOWAIT END WHILE (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SELECT TOP 1 @CurrentFGID = ID, @CurrentFileGroupID = FileGroupID, @CurrentFileGroupName = FileGroupName FROM @tmpFileGroups WHERE Selected = 1 AND Completed = 0 ORDER BY [Order] ASC IF @@ROWCOUNT = 0 BEGIN BREAK END -- Does the filegroup exist? SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'IF EXISTS(SELECT * FROM sys.filegroups filegroups WHERE [type] <> ''FX'' AND filegroups.data_space_id = @ParamFileGroupID AND filegroups.[name] = @ParamFileGroupName) BEGIN SET @ParamFileGroupExists = 1 END' BEGIN TRY EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand, @params = N'@ParamFileGroupID int, @ParamFileGroupName sysname, @ParamFileGroupExists bit OUTPUT', @ParamFileGroupID = @CurrentFileGroupID, @ParamFileGroupName = @CurrentFileGroupName, @ParamFileGroupExists = @CurrentFileGroupExists OUTPUT IF @CurrentFileGroupExists IS NULL SET @CurrentFileGroupExists = 0 END TRY BEGIN CATCH SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CASE WHEN ERROR_NUMBER() = 1222 THEN ', ' + ' The file group ' + QUOTENAME(@CurrentFileGroupName) + ' in the database ' + QUOTENAME(@CurrentDatabaseName) + ' is locked. It could not be checked if the filegroup exists.' ELSE '' END SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT RAISERROR(@EmptyLine,10,1) WITH NOWAIT IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10) BEGIN SET @ReturnCode = ERROR_NUMBER() END END CATCH IF @CurrentFileGroupExists = 1 BEGIN SET @CurrentDatabaseContext = @CurrentDatabaseName SET @CurrentCommandType = 'DBCC_CHECKFILEGROUP' SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'DBCC CHECKFILEGROUP (' + QUOTENAME(@CurrentFileGroupName) IF @NoIndex = 'Y' SET @CurrentCommand += ', NOINDEX' SET @CurrentCommand += ') WITH NO_INFOMSGS, ALL_ERRORMSGS' IF @PhysicalOnly = 'Y' SET @CurrentCommand += ', PHYSICAL_ONLY' IF @TabLock = 'Y' SET @CurrentCommand += ', TABLOCK' IF @MaxDOP IS NOT NULL SET @CurrentCommand += ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar) EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute SET @Error = @@ERROR IF @Error <> 0 SET @CurrentCommandOutput = @Error IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput END UPDATE @tmpFileGroups SET Completed = 1 WHERE Selected = 1 AND Completed = 0 AND ID = @CurrentFGID SET @CurrentFGID = NULL SET @CurrentFileGroupID = NULL SET @CurrentFileGroupName = NULL SET @CurrentFileGroupExists = NULL SET @CurrentDatabaseContext = NULL SET @CurrentCommand = NULL SET @CurrentCommandOutput = NULL SET @CurrentCommandType = NULL END END -- Check disk space allocation structures IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKALLOC') AND (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SET @CurrentDatabaseContext = CASE WHEN SERVERPROPERTY('EngineEdition') = 5 THEN @CurrentDatabaseName ELSE 'master' END SET @CurrentCommandType = 'DBCC_CHECKALLOC' SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'DBCC CHECKALLOC (' + QUOTENAME(@CurrentDatabaseName) SET @CurrentCommand += ') WITH NO_INFOMSGS, ALL_ERRORMSGS' IF @TabLock = 'Y' SET @CurrentCommand += ', TABLOCK' EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute SET @Error = @@ERROR IF @Error <> 0 SET @CurrentCommandOutput = @Error IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput END -- Check objects IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE') AND (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SET @CurrentCommand = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, 0 AS [Order], 0 AS Selected, 0 AS Completed FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.object_id = objects.object_id)' + CASE WHEN @Version >= 12 THEN ' AND (tables.is_memory_optimized = 0 OR is_memory_optimized IS NULL)' ELSE '' END + ' ORDER BY schemas.name ASC, objects.name ASC' INSERT INTO @tmpObjects (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, [Order], Selected, Completed) EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand SET @Error = @@ERROR IF @Error <> 0 SET @ReturnCode = @Error IF @Objects IS NULL BEGIN UPDATE tmpObjects SET tmpObjects.Selected = 1 FROM @tmpObjects tmpObjects END ELSE BEGIN UPDATE tmpObjects SET tmpObjects.Selected = SelectedObjects.Selected FROM @tmpObjects tmpObjects INNER JOIN @SelectedObjects SelectedObjects ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName,'_','[_]') AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName,'_','[_]') AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName,'_','[_]') WHERE SelectedObjects.Selected = 1 UPDATE tmpObjects SET tmpObjects.Selected = SelectedObjects.Selected FROM @tmpObjects tmpObjects INNER JOIN @SelectedObjects SelectedObjects ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName,'_','[_]') AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName,'_','[_]') AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName,'_','[_]') WHERE SelectedObjects.Selected = 0 UPDATE tmpObjects SET tmpObjects.StartPosition = SelectedObjects2.StartPosition FROM @tmpObjects tmpObjects INNER JOIN (SELECT tmpObjects.SchemaName, tmpObjects.ObjectName, MIN(SelectedObjects.StartPosition) AS StartPosition FROM @tmpObjects tmpObjects INNER JOIN @SelectedObjects SelectedObjects ON @CurrentDatabaseName LIKE REPLACE(SelectedObjects.DatabaseName,'_','[_]') AND tmpObjects.SchemaName LIKE REPLACE(SelectedObjects.SchemaName,'_','[_]') AND tmpObjects.ObjectName LIKE REPLACE(SelectedObjects.ObjectName,'_','[_]') WHERE SelectedObjects.Selected = 1 GROUP BY tmpObjects.SchemaName, tmpObjects.ObjectName) SelectedObjects2 ON tmpObjects.SchemaName = SelectedObjects2.SchemaName AND tmpObjects.ObjectName = SelectedObjects2.ObjectName END; WITH tmpObjects AS ( SELECT SchemaName, ObjectName, [Order], ROW_NUMBER() OVER (ORDER BY StartPosition ASC, SchemaName ASC, ObjectName ASC) AS RowNumber FROM @tmpObjects tmpObjects WHERE Selected = 1 ) UPDATE tmpObjects SET [Order] = RowNumber SET @ErrorMessage = '' SELECT @ErrorMessage = @ErrorMessage + QUOTENAME(DatabaseName) + '.' + QUOTENAME(SchemaName) + '.' + QUOTENAME(ObjectName) + ', ' FROM @SelectedObjects SelectedObjects WHERE DatabaseName = @CurrentDatabaseName AND SchemaName NOT LIKE '%[%]%' AND ObjectName NOT LIKE '%[%]%' AND NOT EXISTS (SELECT * FROM @tmpObjects WHERE SchemaName = SelectedObjects.SchemaName AND ObjectName = SelectedObjects.ObjectName) IF @@ROWCOUNT > 0 BEGIN SET @ErrorMessage = 'The following objects do not exist: ' + LEFT(@ErrorMessage,LEN(@ErrorMessage)-1) + '.' RAISERROR('%s',10,1,@ErrorMessage) WITH NOWAIT SET @Error = @@ERROR RAISERROR(@EmptyLine,10,1) WITH NOWAIT END WHILE (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SELECT TOP 1 @CurrentOID = ID, @CurrentSchemaID = SchemaID, @CurrentSchemaName = SchemaName, @CurrentObjectID = ObjectID, @CurrentObjectName = ObjectName, @CurrentObjectType = ObjectType FROM @tmpObjects WHERE Selected = 1 AND Completed = 0 ORDER BY [Order] ASC IF @@ROWCOUNT = 0 BEGIN BREAK END -- Does the object exist? SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'IF EXISTS(SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM sys.indexes indexes WHERE indexes.object_id = objects.object_id)' + CASE WHEN @Version >= 12 THEN ' AND (tables.is_memory_optimized = 0 OR is_memory_optimized IS NULL)' ELSE '' END + ' AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType) BEGIN SET @ParamObjectExists = 1 END' BEGIN TRY EXECUTE @CurrentDatabase_sp_executesql @stmt = @CurrentCommand, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamObjectExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamObjectExists = @CurrentObjectExists OUTPUT IF @CurrentObjectExists IS NULL SET @CurrentObjectExists = 0 END TRY BEGIN CATCH SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CASE WHEN ERROR_NUMBER() = 1222 THEN ', ' + 'The object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the object exists.' ELSE '' END SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT RAISERROR(@EmptyLine,10,1) WITH NOWAIT IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10) BEGIN SET @ReturnCode = ERROR_NUMBER() END END CATCH IF @CurrentObjectExists = 1 BEGIN SET @CurrentDatabaseContext = @CurrentDatabaseName SET @CurrentCommandType = 'DBCC_CHECKTABLE' SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'DBCC CHECKTABLE (''' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + '''' IF @NoIndex = 'Y' SET @CurrentCommand += ', NOINDEX' SET @CurrentCommand += ') WITH NO_INFOMSGS, ALL_ERRORMSGS' IF @DataPurity = 'Y' SET @CurrentCommand += ', DATA_PURITY' IF @PhysicalOnly = 'Y' SET @CurrentCommand += ', PHYSICAL_ONLY' IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand += ', EXTENDED_LOGICAL_CHECKS' IF @TabLock = 'Y' SET @CurrentCommand += ', TABLOCK' IF @MaxDOP IS NOT NULL SET @CurrentCommand += ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar) EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @LogToTable = @LogToTable, @Execute = @Execute SET @Error = @@ERROR IF @Error <> 0 SET @CurrentCommandOutput = @Error IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput END UPDATE @tmpObjects SET Completed = 1 WHERE Selected = 1 AND Completed = 0 AND ID = @CurrentOID SET @CurrentOID = NULL SET @CurrentSchemaID = NULL SET @CurrentSchemaName = NULL SET @CurrentObjectID = NULL SET @CurrentObjectName = NULL SET @CurrentObjectType = NULL SET @CurrentObjectExists = NULL SET @CurrentDatabaseContext = NULL SET @CurrentCommand = NULL SET @CurrentCommandOutput = NULL SET @CurrentCommandType = NULL END END -- Check catalog IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKCATALOG') AND (SYSDATETIME() < DATEADD(SECOND,@TimeLimit,@StartTime) OR @TimeLimit IS NULL) BEGIN SET @CurrentDatabaseContext = CASE WHEN SERVERPROPERTY('EngineEdition') = 5 THEN @CurrentDatabaseName ELSE 'master' END SET @CurrentCommandType = 'DBCC_CHECKCATALOG' SET @CurrentCommand = '' IF @LockTimeout IS NOT NULL SET @CurrentCommand = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; ' SET @CurrentCommand += 'DBCC CHECKCATALOG (' + QUOTENAME(@CurrentDatabaseName) SET @CurrentCommand += ') WITH NO_INFOMSGS' EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute SET @Error = @@ERROR IF @Error <> 0 SET @CurrentCommandOutput = @Error IF @CurrentCommandOutput <> 0 SET @ReturnCode = @CurrentCommandOutput END END IF @CurrentDatabaseState = 'SUSPECT' BEGIN SET @ErrorMessage = 'The database ' + QUOTENAME(@CurrentDatabaseName) + ' is in a SUSPECT state.' RAISERROR('%s',16,1,@ErrorMessage) WITH NOWAIT SET @Error = @@ERROR RAISERROR(@EmptyLine,10,1) WITH NOWAIT END -- Update that the database is completed IF @DatabasesInParallel = 'Y' BEGIN UPDATE dbo.QueueDatabase SET DatabaseEndTime = SYSDATETIME() WHERE QueueID = @QueueID AND DatabaseName = @CurrentDatabaseName END ELSE BEGIN UPDATE @tmpDatabases SET Completed = 1 WHERE Selected = 1 AND Completed = 0 AND ID = @CurrentDBID END -- Clear variables SET @CurrentDBID = NULL SET @CurrentDatabaseName = NULL SET @CurrentDatabase_sp_executesql = NULL SET @CurrentUserAccess = NULL SET @CurrentIsReadOnly = NULL SET @CurrentDatabaseState = NULL SET @CurrentInStandby = NULL SET @CurrentRecoveryModel = NULL SET @CurrentIsDatabaseAccessible = NULL SET @CurrentAvailabilityGroup = NULL SET @CurrentAvailabilityGroupRole = NULL SET @CurrentAvailabilityGroupBackupPreference = NULL SET @CurrentIsPreferredBackupReplica = NULL SET @CurrentDatabaseMirroringRole = NULL SET @CurrentDatabaseContext = NULL SET @CurrentCommand = NULL SET @CurrentCommandOutput = NULL SET @CurrentCommandType = NULL DELETE FROM @tmpFileGroups DELETE FROM @tmpObjects END ---------------------------------------------------------------------------------------------------- --// Log completing information //-- ---------------------------------------------------------------------------------------------------- Logging: SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),120) RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT RAISERROR(@EmptyLine,10,1) WITH NOWAIT IF @ReturnCode <> 0 BEGIN RETURN @ReturnCode END ---------------------------------------------------------------------------------------------------- END GO