На разработку данного кода повлияло несколько факторов:
1. При работе с ежедневным обслуживанием баз мы используем широко известные скрипты Ola (https://ola.hallengren.com/sql-server-integrity-check.html), так как при их использовании существует ряд важных преимуществ: возможность параллельного запуска команд (бэкапа баз, логов, проверки баз, работы с индексами) и поддержка в качестве аргумента метамаски имен баз данных, благодаря чему на сервере, где находятся не только 1с пользовательские базы данные, легко создать план обслуживания, указав ненужные исключения в качестве параметра (у нас это суффиксы информационных баз вида _DEV разработка, _TEST, _COPY и т.д. для остальных нужд в базах, содержимое которых не требует регулярной с ними работы/бэкапа и т.д.
2. Рано или поздно, каждый может столкнуться с ситуацией, когда базы данных повреждаются, как об этом написано в заметке //infostart.ru/public/1234180/ . О необходимости их детекта и ежедневного отслеживания сказано там же, но в нашем случае этот прискорбный случай произошел после очередного обновления Windows на сервере, приведшего к неудачному выключению сервера (процесс шатдауна завис, ничего не сделав, никакие методы не помогли, рубить пришлось "железно"). Как выяснилось в дальнейшем, за промежуток времени до регламентных работ в индексах базы данных, с которой постоянно идет работа, произошли ошибки, приведшие к постоянным крахам процессов и переполнению каталога с логами и дампамиSQL сервера. Утром вся работа на предприятии встала. При ручной проверке индексы, к сожалению, нормально ребилдиться без предварительного их отключения не захотели, эта ситуация уже описывалась и ранее по поиску в интернете.
3. Для того, чтобы все-таки избежать по возможности подобных случаев и не проходить вручную по неисправным индексам или отключать базу и ребилдить ее без пользователей, искалось решение, которое обработабывало бы результаты проверки базы данных и исправляло только поврежденные таблицы (экономя время в автоматическом режиме в еженочных планах обслуживания в случае обнаружения таких ошибок, либо помогало при обработке вручную).
В процессе из наиболее близкого здесь же была найдена уже упомянутая статья Анатолия Симакова, за что отдельное спасибо, всем рекомендую ознакомиться с ней, там более подробно описано первоначальное назначение модуля, послужившего прообразом - повторяться не буду.
К этому алгоритму был добавлен блок с исправлением таблиц методом отключения индексов и их перестроения с последующим включением и интегрирован код с учетом использования нами ставшими стандартами скриптами Ola. Как показано на скриншотах, в итоге в ежедневном плане обслуживания можно проходить цикл проверки всех баз (с запуском в параллельном режиме по желанию, если ресурсы сервера и режим работы предприятия это позволяют) с автоматической попыткой исправления и отправкой репорта по окончании всех работ.
В качестве доработки вместе с оригинальными упомянутыми выше скриптами, используется процедура [dbo].[DatabaseIntegrityCheck_addjk] с параметром @ModeRepair, в которой в качестве строки необходимо указать комбинацию параметров (по желанию) 'REPORT REPAIR TABLE'. При указании слова REPAIR будет проведена попытка исправления каждой таблицы, в которой были обнаружены ошибки (при желании текст в модуле так же можно поправить под свои нужды - здесь это только отключение и дальнейшее перестроение индексов). Если имеется слово REPORT, при завершении работы (и, опционально, исправления базы данных) будет передан отчет на электронную почту (используется профиль почтового ящика по умолчанию и e-mail оператора sql agent сервера, в тексте в конце модуля можно переопределить) - если исправления не помогли, в отчете будут присутствовать сообщения об ошибках с префиксом 'FIX:' и именем базы данных. Отдельно упомяну параметр TABLE - при указании этой опции, результат работы проверки и исправления (если была указана данная опция) не очищается при выходе из процедуры и остается в таблице, данные из нее показаны на скриншоте и их можно выбрать командой select * from ##DBCC_DataReport для каких-либо дальнейших действий.
скрипт
USE [master]
GO
/****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 18.06.2020 10:51:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DatabaseIntegrityCheck_addjk]
@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',
@ModeRepair nvarchar(max) = NULL --'REPORT REPAIR TABLE' --+jk: REPORT-report on errors to e-mail, REPAIR - try repair, TABLE - not remove log table at exit (for ext script)
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')
--+jk:
SET @Parameters += ', @ModeRepair = ' + ISNULL('''' + REPLACE(@ModeRepair,'''','''''') + '''','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
--{+jk
IF (@ModeRepair IS NOT NULL)
BEGIN
IF OBJECT_iD('tempdb..##DBCC_DataReport') is not null
DROP TABLE ##DBCC_DataReport
-- table structure for SQL Server 2012, 2014, 2016 and 2017
CREATE TABLE ##DBCC_DataReport(
[DatabaseName][VARCHAR](100) NULL
,[Error] [int] NULL
,[Level] [int] NULL
,[State] [int] NULL
,[MessageText] [VARCHAR](7000) NULL
,[RepairLevel] [VARCHAR](300) NULL
,[Status] [int] NULL
,[DbId] [int] NULL
,[DbFragId] [int] NULL
,[ObjectId] [int] NULL
,[IndexId] [int] NULL
,[PartitionID] [bigint] NULL
,[AllocUnitID] [bigint] NULL
,[RidDbId] [int] NULL
,[RidPruId] [int] NULL
,[File] [int] NULL
,[Page] [int] NULL
,[Slot] [int] NULL
,[RefDbId] [int] NULL
,[RefPruId] [int] NULL
,[RefFile] [int] NULL
,[RefPage] [int] NULL
,[RefSlot] [int] NULL
,[Allocation] [int] NULL
)
END
--}jk
----------------------------------------------------------------------------------------------------
--// 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)
--{+jk
IF @ModeRepair is not NULL
BEGIN
SET @CurrentCommand+=', TABLERESULTS, DATA_PURITY'
-- SET @CurrentCommand = REPLACE (@CurrentCommand,'WITH ','WITH TABLERESULTS, DATA_PURITY, ')
Print @CurrentCommand
INSERT INTO ##DBCC_DataReport ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], DbFragId, ObjectId, IndexId, PartitionId, AllocUnitId, RidDbId, RidPruId, [File], Page, Slot,
RefDbId, RefPruId, RefFile, RefPage, RefSlot,Allocation)
EXEC (@CurrentCommand)
UPDATE ##DBCC_DataReport SET [DatabaseName] = @CurrentDatabaseName WHERE [DatabaseName] IS NULL
END
ELSE
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseContext, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
--}jk
-- 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 --while 1=1
--{+jk
IF @ModeRepair like '%REPAIR%'
BEGIN
DECLARE @database_fixname NVARCHAR(100),@database_fixtbl nvarchar(200),@tblpath nvarchar(max),@cmdforfix nvarchar(max)
DECLARE databasefix_CURSOR CURSOR FOR
SELECT
DatabaseName, object_name(cast(replace(substring(MessageText,23+patindex('%(идентификатор объекта %)%',MessageText),90),')','') as decimal),DB_ID(DatabaseName)) as Tbl
FROM ##DBCC_DataReport
WHERE
SUBSTRING(MessageText, 1, 195) like 'CHECKDB обнаружил [1-9]%таблице%'
or SUBSTRING(MessageText, 1, 195) like 'CHECKDB обнаружил [0-9]%[1-9]%таблице%'
OPEN databasefix_CURSOR
FETCH NEXT FROM databasefix_CURSOR INTO @database_fixname,@database_fixtbl
WHILE @@FETCH_STATUS=0
BEGIN
SET @tblpath='['+@database_fixname+'].[dbo].['+@database_fixtbl+']'
SET @cmdforfix='ALTER INDEX ALL ON '+@tblpath+' DISABLE;ALTER INDEX ALL ON '+@tblpath+' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) '
PRINT @cmdforfix
EXEC(@cmdforfix)
declare @cmd nvarchar(max)
set @cmd='dbcc checktable ('''+@tblpath+''') with TABLERESULTS, ALL_ERRORMSGS, DATA_PURITY,NO_INFOMSGS'
INSERT INTO ##DBCC_DataReport ([Error], [Level], [State], MessageText, RepairLevel, [Status],
[DbId], DbFragId, ObjectId, IndexId, PartitionId, AllocUnitId, RidDbId, RidPruId, [File], Page, Slot,
RefDbId, RefPruId, RefFile, RefPage, RefSlot,Allocation)
exec(@cmd)
UPDATE ##DBCC_DataReport SET [DatabaseName] = 'FIX: '+@database_fixname WHERE [DatabaseName] IS NULL
FETCH NEXT FROM databasefix_CURSOR INTO @database_fixname,@database_fixtbl
END --while
CLOSE databasefix_CURSOR
DEALLOCATE databasefix_CURSOR
END --REPAIR
IF @ModeRepair like '%REPORT%'
BEGIN
DECLARE @MSG NVARCHAR(MAX)
SET @MSG = (
SELECT
TextData + CHAR(10) AS [text()]
FROM (
SELECT
Concat(DatabaseName, ': ', MessageText) as TextData
FROM ##DBCC_DataReport
WHERE
SUBSTRING(MessageText, 1, 45) like 'CHECKDB обнаружил [1-9]%'
or SUBSTRING(MessageText, 1, 45) like 'CHECKDB обнаружил [0-9]%[1-9]%'
) AS ReportData
FOR XML PATH (''))
IF @MSG IS NOT NULL
BEGIN
DECLARE @Profilename as nvarchar(100) = NULL --профиль по умолчанию -- Имя почтового профиля, для отправки электонной почты
DECLARE @Recipients as nvarchar(100) = (SELECT TOP 1 [email_address] FROM [msdb].[dbo].[sysoperators]) -- по умолчанию берем e-mail оператора (напр.sql agentа). Получатели сообщений электронной почты, разделенные знаком ";" (для нас 'alert@o*74.ru' )
DECLARE @Msubject nvarchar(200)='SQL SERVER '+@@SERVERNAME+'. '+@CheckCommands+' '+@ModeRepair -- Тема сообщения SET @Msubject=
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @Profilename,
@recipients = @Recipients,
@body = @MSG,
@subject = @Msubject;
END
END --report
IF @ModeRepair is not null and @ModeRepair not like '%TABLE%'
DROP TABLE ##DBCC_DataReport
--}jk
----------------------------------------------------------------------------------------------------
--// 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
в приложенном необязательном архиве этот же скрипт в комплекте с связкой с остальными скриптами, с которыми сейчас все это дело работает (скрипты Ola изредка обновляются - при появлении ошибок проверьте логи - если присутствуют записи вида "The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.", нужно выполнить необходимые действия).
