December 10, 2018 at 6:32 am
I am using Ola Hallengren's script for DB integrity so I tested the script 1st on our dev environment and the script ran successfully however, it failed on production server when I executed the same script. Both server are running on 2008 R2 (SP2). Below is the script which I have used.USE [DBMAINT]
GO
/****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 12/10/2018 7:27:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DatabaseIntegrityCheck]
@databases nvarchar(max) = NULL,
@CheckCommands nvarchar(max) = 'CHECKDB',
@PhysicalOnly 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',
@LockTimeout int = NULL,
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'
AS
BEGIN
----------------------------------------------------------------------------------------------------
--// Source: https://ola.hallengren.com //--
----------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @DatabaseMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @Version numeric(18,10)
DECLARE @AmazonRDS bit
DECLARE @Cluster nvarchar(max)
DECLARE @CurrentDBID int
DECLARE @CurrentDatabaseID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentIsDatabaseAccessible bit
DECLARE @CurrentAvailabilityGroup nvarchar(max)
DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
DECLARE @CurrentDatabaseMirroringRole nvarchar(max)
DECLARE @CurrentIsReadOnly bit
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 @CurrentCommand01 nvarchar(max)
DECLARE @CurrentCommand02 nvarchar(max)
DECLARE @CurrentCommand03 nvarchar(max)
DECLARE @CurrentCommand04 nvarchar(max)
DECLARE @CurrentCommand05 nvarchar(max)
DECLARE @CurrentCommand06 nvarchar(max)
DECLARE @CurrentCommand07 nvarchar(max)
DECLARE @CurrentCommand08 nvarchar(max)
DECLARE @CurrentCommand09 nvarchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput04 int
DECLARE @CurrentCommandOutput05 int
DECLARE @CurrentCommandOutput08 int
DECLARE @CurrentCommandOutput09 int
DECLARE @CurrentCommandType01 nvarchar(max)
DECLARE @CurrentCommandType04 nvarchar(max)
DECLARE @CurrentCommandType05 nvarchar(max)
DECLARE @CurrentCommandType08 nvarchar(max)
DECLARE @CurrentCommandType09 nvarchar(max)
DECLARE @tmpDatabases TABLE (ID int IDENTITY,
DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
AvailabilityGroup bit,
[Snapshot] bit,
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))
DECLARE @tmpAvailabilityGroups TABLE (ID int IDENTITY PRIMARY KEY,
AvailabilityGroupName nvarchar(max),
Selected bit)
DECLARE @tmpDatabasesAvailabilityGroups TABLE (DatabaseName nvarchar(max), AvailabilityGroupName nvarchar(max))
DECLARE @tmpFileGroups TABLE (ID int IDENTITY,
FileGroupID int,
FileGroupName nvarchar(max),
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))
DECLARE @tmpObjects TABLE (ID int IDENTITY,
SchemaID int,
SchemaName nvarchar(max),
ObjectID int,
ObjectName nvarchar(max),
ObjectType nvarchar(max),
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))
DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
AvailabilityGroup nvarchar(max),
Selected bit)
DECLARE @SelectedAvailabilityGroups TABLE (AvailabilityGroupName nvarchar(max),
Selected bit)
DECLARE @SelectedFileGroups TABLE (DatabaseName nvarchar(max),
FileGroupName nvarchar(max),
Selected bit)
DECLARE @SelectedObjects TABLE (DatabaseName nvarchar(max),
SchemaName nvarchar(max),
ObjectName nvarchar(max),
Selected bit)
DECLARE @SelectedCheckCommands TABLE (CheckCommand nvarchar(max))
DECLARE @Error int
DECLARE @ReturnCode int
SET @Error = 0
SET @ReturnCode = 0
SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @CheckCommands = ' + ISNULL('''' + REPLACE(@CheckCommands,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FileGroups = ' + ISNULL('''' + REPLACE(@FileGroups,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Objects = ' + ISNULL('''' + REPLACE(@Objects,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @AvailabilityGroupReplicas = ' + ISNULL('''' + REPLACE(@AvailabilityGroupReplicas,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Updateability = ' + ISNULL('''' + REPLACE(@Updateability,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10)
SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Check core requirements //--
----------------------------------------------------------------------------------------------------
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
SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
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 '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
BEGIN
SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
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
SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF SERVERPROPERTY('EngineEdition') = 5 AND @Version < 12
BEGIN
SET @ErrorMessage = 'The stored procedure DatabaseIntegrityCheck is not supported on this version of Azure SQL Database.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO Logging
END
----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------
SET @databases = REPLACE(@Databases, CHAR(10), '')
SET @databases = REPLACE(@Databases, CHAR(13), '')
WHILE CHARINDEX(', ',@Databases) > 0 SET @databases = REPLACE(@Databases,', ',',')
WHILE CHARINDEX(' ,',@Databases) > 0 SET @databases = REPLACE(@Databases,' ,',',')
SET @databases = LTRIM(RTRIM(@Databases));
WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
FROM Databases1
WHERE EndPosition < LEN(@Databases) + 1
),
Databases2 (DatabaseItem, Selected) AS
(
SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Databases1
),
Databases3 (DatabaseItem, DatabaseType, AvailabilityGroup, 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,
Selected
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, AvailabilityGroup, Selected) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
DatabaseType,
AvailabilityGroup,
Selected
FROM Databases3
)
INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, AvailabilityGroup, Selected)
SELECT DatabaseName,
DatabaseType,
AvailabilityGroup,
Selected
FROM Databases4
OPTION (MAXRECURSION 0)
IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5
BEGIN
INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Snapshot], Selected, Completed)
SELECT [name] AS DatabaseName,
CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
CASE WHEN name IN (SELECT availability_databases_cluster.database_name FROM sys.availability_databases_cluster availability_databases_cluster) THEN 1 ELSE 0 END AS AvailabilityGroup,
CASE WHEN source_database_id IS NOT NULL THEN 1 ELSE 0 END AS [Snapshot],
0 AS Selected,
0 AS Completed
FROM sys.databases
ORDER BY [name] ASC
END
ELSE
BEGIN
INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Snapshot], Selected, Completed)
SELECT [name] AS DatabaseName,
CASE WHEN name IN('master','msdb','model') 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 Selected,
0 AS Completed
FROM sys.databases
ORDER BY [name] ASC
END
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
IF @databases IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = ''))
BEGIN
SET @ErrorMessage = 'The value for the parameter @databases is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Select availability groups //--
----------------------------------------------------------------------------------------------------
IF @AvailabilityGroups IS NOT NULL AND @Version >= 11
BEGIN
SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(10), '')
SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(13), '')
WHILE CHARINDEX(', ',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,', ',',')
WHILE CHARINDEX(' ,',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,' ,',',')
SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups));
WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) - EndPosition - 1) AS AvailabilityGroupItem
FROM AvailabilityGroups1
WHERE EndPosition < LEN(@AvailabilityGroups) + 1
),
AvailabilityGroups2 (AvailabilityGroupItem, Selected) AS
(
SELECT CASE WHEN AvailabilityGroupItem LIKE '-%' THEN RIGHT(AvailabilityGroupItem,LEN(AvailabilityGroupItem) - 1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
CASE WHEN AvailabilityGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM AvailabilityGroups1
),
AvailabilityGroups3 (AvailabilityGroupItem, Selected) AS
(
SELECT CASE WHEN AvailabilityGroupItem = 'ALL_AVAILABILITY_GROUPS' THEN '%' ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
Selected
FROM AvailabilityGroups2
),
AvailabilityGroups4 (AvailabilityGroupName, Selected) AS
(
SELECT CASE WHEN LEFT(AvailabilityGroupItem,1) = '[' AND RIGHT(AvailabilityGroupItem,1) = ']' THEN PARSENAME(AvailabilityGroupItem,1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
Selected
FROM AvailabilityGroups3
)
INSERT INTO @SelectedAvailabilityGroups (AvailabilityGroupName, Selected)
SELECT AvailabilityGroupName, Selected
FROM AvailabilityGroups4
OPTION (MAXRECURSION 0)
INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected)
SELECT name AS AvailabilityGroupName,
0 AS Selected
FROM sys.availability_groups
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
INSERT INTO @tmpDatabasesAvailabilityGroups (DatabaseName, AvailabilityGroupName)
SELECT availability_databases_cluster.database_name, availability_groups.name
FROM sys.availability_databases_cluster availability_databases_cluster
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
UPDATE tmpDatabases
SET 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)
BEGIN
SET @ErrorMessage = 'The value for the parameter @AvailabilityGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF (@Databases IS NULL AND @AvailabilityGroups IS NULL)
BEGIN
SET @ErrorMessage = 'You need to specify one of the parameters @databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL)
BEGIN
SET @ErrorMessage = 'You can only specify one of the parameters @databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Select filegroups //--
----------------------------------------------------------------------------------------------------
SET @FileGroups = REPLACE(@FileGroups, CHAR(10), '')
SET @FileGroups = REPLACE(@FileGroups, CHAR(13), '')
WHILE CHARINDEX(', ',@FileGroups) > 0 SET @FileGroups = REPLACE(@FileGroups,', ',',')
WHILE CHARINDEX(' ,',@FileGroups) > 0 SET @FileGroups = REPLACE(@FileGroups,' ,',',')
SET @FileGroups = LTRIM(RTRIM(@FileGroups));
WITH FileGroups1 (StartPosition, EndPosition, FileGroupItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FileGroups, 1), 0), LEN(@FileGroups) + 1) AS EndPosition,
SUBSTRING(@FileGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) AS EndPosition,
SUBSTRING(@FileGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) - EndPosition - 1) AS FileGroupItem
FROM FileGroups1
WHERE EndPosition < LEN(@FileGroups) + 1
),
FileGroups2 (FileGroupItem, Selected) AS
(
SELECT CASE WHEN FileGroupItem LIKE '-%' THEN RIGHT(FileGroupItem,LEN(FileGroupItem) - 1) ELSE FileGroupItem END AS FileGroupItem,
CASE WHEN FileGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM FileGroups1
),
FileGroups3 (FileGroupItem, Selected) AS
(
SELECT CASE WHEN FileGroupItem = 'ALL_FILEGROUPS' THEN '%.%' ELSE FileGroupItem END AS FileGroupItem,
Selected
FROM FileGroups2
),
FileGroups4 (DatabaseName, FileGroupName, 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,
Selected
FROM FileGroups3
)
INSERT INTO @SelectedFileGroups (DatabaseName, FileGroupName, Selected)
SELECT DatabaseName, FileGroupName, Selected
FROM FileGroups4
OPTION (MAXRECURSION 0)
----------------------------------------------------------------------------------------------------
--// Select objects //--
----------------------------------------------------------------------------------------------------
SET @Objects = REPLACE(@Objects, CHAR(10), '')
SET @Objects = REPLACE(@Objects, CHAR(13), '')
WHILE CHARINDEX(', ',@Objects) > 0 SET @Objects = REPLACE(@Objects,', ',',')
WHILE CHARINDEX(' ,',@Objects) > 0 SET @Objects = REPLACE(@Objects,' ,',',')
SET @Objects = LTRIM(RTRIM(@Objects));
WITH Objects1 (StartPosition, EndPosition, ObjectItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Objects, 1), 0), LEN(@Objects) + 1) AS EndPosition,
SUBSTRING(@Objects, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) AS EndPosition,
SUBSTRING(@Objects, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) - EndPosition - 1) AS ObjectItem
FROM Objects1
WHERE EndPosition < LEN(@Objects) + 1
),
Objects2 (ObjectItem, Selected) AS
(
SELECT CASE WHEN ObjectItem LIKE '-%' THEN RIGHT(ObjectItem,LEN(ObjectItem) - 1) ELSE ObjectItem END AS ObjectItem,
CASE WHEN ObjectItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Objects1
),
Objects3 (ObjectItem, Selected) AS
(
SELECT CASE WHEN ObjectItem = 'ALL_OBJECTS' THEN '%.%.%' ELSE ObjectItem END AS ObjectItem,
Selected
FROM Objects2
),
Objects4 (DatabaseName, SchemaName, ObjectName, 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,
Selected
FROM Objects3
)
INSERT INTO @SelectedObjects (DatabaseName, SchemaName, ObjectName, Selected)
SELECT DatabaseName, SchemaName, ObjectName, Selected
FROM Objects4
OPTION (MAXRECURSION 0);
----------------------------------------------------------------------------------------------------
--// Select check commands //--
----------------------------------------------------------------------------------------------------
WITH CheckCommands (StartPosition, EndPosition, CheckCommand) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) AS EndPosition,
SUBSTRING(@CheckCommands, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @CheckCommands, EndPosition + 1), 0), LEN(@CheckCommands) + 1) AS EndPosition,
SUBSTRING(@CheckCommands, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @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')) OR EXISTS (SELECT * FROM @SelectedCheckCommands GROUP BY CheckCommand HAVING COUNT(*) > 1) OR NOT EXISTS (SELECT * FROM @SelectedCheckCommands) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKDB')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG'))) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKALLOC','CHECKTABLE')))
BEGIN
SET @ErrorMessage = 'The value for the parameter @CheckCommands is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @PhysicalOnly NOT IN ('Y','N') OR @PhysicalOnly IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @PhysicalOnly is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @NoIndex NOT IN ('Y','N') OR @NoIndex IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @NoIndex is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL OR (@ExtendedLogicalChecks = 'Y' AND NOT @Version >= 10) OR (@PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y')
BEGIN
SET @ErrorMessage = 'The value for the parameter @ExtendedLogicalChecks is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @TabLock NOT IN ('Y','N') OR @TabLock IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @TabLock is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF EXISTS(SELECT * FROM @SelectedFileGroups WHERE DatabaseName IS NULL OR FileGroupName IS NULL) OR (@FileGroups IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedFileGroups)) OR (@FileGroups IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP'))
BEGIN
SET @ErrorMessage = 'The value for the parameter @FileGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF EXISTS(SELECT * FROM @SelectedObjects WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL) OR (@Objects IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedObjects)) OR (@Objects IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE'))
BEGIN
SET @ErrorMessage = 'The value for the parameter @Objects is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @MaxDOP < 0 OR @MaxDOP > 64 OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5)) OR (@MaxDOP IS NOT NULL AND @Version < 12.050000)
BEGIN
SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @AvailabilityGroupReplicas NOT IN('ALL','PRIMARY','SECONDARY') OR @AvailabilityGroupReplicas IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @AvailabilityGroupReplicas is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Updateability NOT IN('READ_ONLY','READ_WRITE','ALL') OR @Updateability IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @Updateability is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @LockTimeout < 0
BEGIN
SET @ErrorMessage = 'The value for the parameter @LockTimeout is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Execute NOT IN('Y','N') OR @Execute IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Error <> 0
BEGIN
SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-integrity-check.html.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @ReturnCode = @Error
GOTO Logging
END
----------------------------------------------------------------------------------------------------
--// Check Availability Group cluster name //--
----------------------------------------------------------------------------------------------------
IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5
BEGIN
SELECT @Cluster = cluster_name
FROM sys.dm_hadr_cluster
END
----------------------------------------------------------------------------------------------------
--// Execute commands //--
----------------------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
BEGIN
SELECT TOP 1 @CurrentDBID = ID,
@CurrentDatabaseName = DatabaseName
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC
SET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)
IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5
BEGIN
IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)
BEGIN
SET @CurrentIsDatabaseAccessible = 1
END
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0
END
END
IF @Version >= 11 AND @Cluster IS NOT NULL
BEGIN
SELECT @CurrentAvailabilityGroup = availability_groups.name,
@CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc
FROM sys.databases databases
INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = @CurrentDatabaseName
END
IF SERVERPROPERTY('EngineEdition') <> 5
BEGIN
SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
FROM sys.database_mirroring
WHERE database_id = @CurrentDatabaseID
END
SELECT @CurrentIsReadOnly = is_read_only
FROM sys.databases
WHERE name = @CurrentDatabaseName
-- Set database message
SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CASE WHEN @CurrentIsReadOnly = 1 THEN 'READ_ONLY' WHEN @CurrentIsReadOnly = 0 THEN 'READ_WRITE' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
IF @CurrentIsDatabaseAccessible IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)
IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)
IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)
SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
AND (@CurrentAvailabilityGroupRole = @AvailabilityGroupReplicas 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')
BEGIN
SET @CurrentCommandType01 = 'DBCC_CHECKDB'
SET @CurrentCommand01 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand01 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand01 = @CurrentCommand01 + 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabaseName)
IF @NoIndex = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', NOINDEX'
SET @CurrentCommand01 = @CurrentCommand01 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', DATA_PURITY'
IF @PhysicalOnly = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PHYSICAL_ONLY'
IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', EXTENDED_LOGICAL_CHECKS'
IF @TabLock = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', TABLOCK'
IF @MaxDOP IS NOT NULL SET @CurrentCommand01 = @CurrentCommand01 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput01 = @Error
IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01
END
-- Check filegroups
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP')
BEGIN
SET @CurrentCommand02 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT data_space_id AS FileGroupID, name AS FileGroupName, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.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, Selected, Completed)
EXECUTE sp_executesql @statement = @CurrentCommand02
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
END
WHILE EXISTS (SELECT * FROM @tmpFileGroups WHERE Selected = 1 AND Completed = 0)
BEGIN
SELECT TOP 1 @CurrentFGID = ID,
@CurrentFileGroupID = FileGroupID,
@CurrentFileGroupName = FileGroupName
FROM @tmpFileGroups
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC
-- Does the filegroup exist?
SET @CurrentCommand03 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand03 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand03 = @CurrentCommand03 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups WHERE [type] <> ''FX'' AND filegroups.data_space_id = @ParamFileGroupID AND filegroups.[name] = @ParamFileGroupName) BEGIN SET @ParamFileGroupExists = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamFileGroupID int, @ParamFileGroupName sysname, @ParamFileGroupExists bit OUTPUT', @ParamFileGroupID = @CurrentFileGroupID, @ParamFileGroupName = @CurrentFileGroupName, @ParamFileGroupExists = @CurrentFileGroupExists OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentFileGroupExists IS NULL SET @CurrentFileGroupExists = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The file group ' + QUOTENAME(@CurrentFileGroupName) + ' in the database ' + QUOTENAME(@CurrentDatabaseName) + ' is locked. It could not be checked if the filegroup exists.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
END
IF @CurrentFileGroupExists = 1
BEGIN
SET @CurrentCommandType04 = 'DBCC_CHECKFILEGROUP'
SET @CurrentCommand04 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand04 = @CurrentCommand04 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; DBCC CHECKFILEGROUP (' + QUOTENAME(@CurrentFileGroupName)
IF @NoIndex = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', NOINDEX'
SET @CurrentCommand04 = @CurrentCommand04 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', PHYSICAL_ONLY'
IF @TabLock = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', TABLOCK'
IF @MaxDOP IS NOT NULL SET @CurrentCommand04 = @CurrentCommand04 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)
EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput04 = @Error
IF @CurrentCommandOutput04 <> 0 SET @ReturnCode = @CurrentCommandOutput04
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 @CurrentCommand03 = NULL
SET @CurrentCommand04 = NULL
SET @CurrentCommandOutput04 = NULL
SET @CurrentCommandType04 = NULL
END
END
-- Check disk space allocation structures
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKALLOC')
BEGIN
SET @CurrentCommandType05 = 'DBCC_CHECKALLOC'
SET @CurrentCommand05 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand05 = @CurrentCommand05 + 'DBCC CHECKALLOC (' + QUOTENAME(@CurrentDatabaseName)
SET @CurrentCommand05 = @CurrentCommand05 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @TabLock = 'Y' SET @CurrentCommand05 = @CurrentCommand05 + ', TABLOCK'
EXECUTE @CurrentCommandOutput05 = [dbo].[CommandExecute] @Command = @CurrentCommand05, @CommandType = @CurrentCommandType05, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput05 = @Error
IF @CurrentCommandOutput05 <> 0 SET @ReturnCode = @CurrentCommandOutput05
END
-- Check objects
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE')
BEGIN
SET @CurrentCommand06 = '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 Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.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, Selected, Completed)
EXECUTE sp_executesql @statement = @CurrentCommand06
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
END
WHILE EXISTS (SELECT * FROM @tmpObjects WHERE Selected = 1 AND Completed = 0)
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 ID ASC
-- Does the object exist?
SET @CurrentCommand07 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand07 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand07 = @CurrentCommand07 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.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'
EXECUTE sp_executesql @statement = @CurrentCommand07, @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
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentObjectExists IS NULL SET @CurrentObjectExists = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the object exists.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
END
IF @CurrentObjectExists = 1
BEGIN
SET @CurrentCommandType08 = 'DBCC_CHECKTABLE'
SET @CurrentCommand08 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand08 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand08 = @CurrentCommand08 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; DBCC CHECKTABLE (''' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ''''
IF @NoIndex = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', NOINDEX'
SET @CurrentCommand08 = @CurrentCommand08 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'N' SET @CurrentCommand08 = @CurrentCommand08 + ', DATA_PURITY'
IF @PhysicalOnly = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', PHYSICAL_ONLY'
IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', EXTENDED_LOGICAL_CHECKS'
IF @TabLock = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', TABLOCK'
IF @MaxDOP IS NOT NULL SET @CurrentCommand08 = @CurrentCommand08 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)
EXECUTE @CurrentCommandOutput08 = [dbo].[CommandExecute] @Command = @CurrentCommand08, @CommandType = @CurrentCommandType08, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput08 = @Error
IF @CurrentCommandOutput08 <> 0 SET @ReturnCode = @CurrentCommandOutput08
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 @CurrentCommand07 = NULL
SET @CurrentCommand08 = NULL
SET @CurrentCommandOutput08 = NULL
SET @CurrentCommandType08 = NULL
END
END
-- Check catalog
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKCATALOG')
BEGIN
SET @CurrentCommandType09 = 'DBCC_CHECKCATALOG'
SET @CurrentCommand09 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand09 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand09 = @CurrentCommand09 + 'DBCC CHECKCATALOG (' + QUOTENAME(@CurrentDatabaseName)
SET @CurrentCommand09 = @CurrentCommand09 + ') WITH NO_INFOMSGS'
EXECUTE @CurrentCommandOutput09 = [dbo].[CommandExecute] @Command = @CurrentCommand09, @CommandType = @CurrentCommandType09, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput09 = @Error
IF @CurrentCommandOutput09 <> 0 SET @ReturnCode = @CurrentCommandOutput09
END
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentDBID
-- Clear variables
SET @CurrentDBID = NULL
SET @CurrentDatabaseID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentAvailabilityGroup = NULL
SET @CurrentAvailabilityGroupRole = NULL
SET @CurrentDatabaseMirroringRole = NULL
SET @CurrentIsReadOnly = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommand05 = NULL
SET @CurrentCommand06 = NULL
SET @CurrentCommand09 = NULL
SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput05 = NULL
SET @CurrentCommandOutput09 = NULL
SET @CurrentCommandType01 = NULL
SET @CurrentCommandType05 = NULL
SET @CurrentCommandType09 = NULL
DELETE FROM @tmpFileGroups
DELETE FROM @tmpObjects
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END
----------------------------------------------------------------------------------------------------
END
This is the error I am getting.
Unable to start execution of step 1 (reason: Variable SQLLOGDIR not found). The step failed.
Can someone guide me on how to fix this issue?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
December 12, 2018 at 7:39 am
These are never fun to troubleshoot... it could be permissions, configuration of SQL, configuration of Ola's scripts.... Here's how I'd attack this:
First, check the server settings to make sure they are the same
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
EXEC sp_configure;
Run that on both machines and compare. Fix any differences (If you are not sure, assume Production is right and take one change at a time to the Dev environment and test, test, test). And it should go without saying, document any changes.
Next, I'd check Server services for SQL - are the services run by the same users? Do those users have the same permissions on each server? Make changes one by one and test. Since the variable name is SQLLOGDIR, I'd check the Log directories for matching permissions.
Once I'm reasonably sure the 2 environments are more or less similar, I'd fully uninstall Ola's scripts, download the latest versions, reinstall then try again.
December 12, 2018 at 7:46 am
Administering Databases - Monday, December 10, 2018 6:32 AMI am using Ola Hallengren's script for DB integrity so I tested the script 1st on our dev environment and the script ran successfully however, it failed on production server when I executed the same script. Both server are running on 2008 R2 (SP2). Below is the script which I have used.
USE [DBMAINT]
GO
/****** Object: StoredProcedure [dbo].[DatabaseIntegrityCheck] Script Date: 12/10/2018 7:27:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[DatabaseIntegrityCheck]
@databases nvarchar(max) = NULL,
@CheckCommands nvarchar(max) = 'CHECKDB',
@PhysicalOnly 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',
@LockTimeout int = NULL,
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'AS
BEGIN
----------------------------------------------------------------------------------------------------
--// Source: https://ola.hallengren.com //--
----------------------------------------------------------------------------------------------------SET NOCOUNT ON
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @DatabaseMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)DECLARE @Version numeric(18,10)
DECLARE @AmazonRDS bitDECLARE @Cluster nvarchar(max)
DECLARE @CurrentDBID int
DECLARE @CurrentDatabaseID int
DECLARE @CurrentDatabaseName nvarchar(max)
DECLARE @CurrentIsDatabaseAccessible bit
DECLARE @CurrentAvailabilityGroup nvarchar(max)
DECLARE @CurrentAvailabilityGroupRole nvarchar(max)
DECLARE @CurrentDatabaseMirroringRole nvarchar(max)
DECLARE @CurrentIsReadOnly bitDECLARE @CurrentFGID int
DECLARE @CurrentFileGroupID int
DECLARE @CurrentFileGroupName nvarchar(max)
DECLARE @CurrentFileGroupExists bitDECLARE @CurrentOID int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName nvarchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName nvarchar(max)
DECLARE @CurrentObjectType nvarchar(max)
DECLARE @CurrentObjectExists bitDECLARE @CurrentCommand01 nvarchar(max)
DECLARE @CurrentCommand02 nvarchar(max)
DECLARE @CurrentCommand03 nvarchar(max)
DECLARE @CurrentCommand04 nvarchar(max)
DECLARE @CurrentCommand05 nvarchar(max)
DECLARE @CurrentCommand06 nvarchar(max)
DECLARE @CurrentCommand07 nvarchar(max)
DECLARE @CurrentCommand08 nvarchar(max)
DECLARE @CurrentCommand09 nvarchar(max)DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput04 int
DECLARE @CurrentCommandOutput05 int
DECLARE @CurrentCommandOutput08 int
DECLARE @CurrentCommandOutput09 intDECLARE @CurrentCommandType01 nvarchar(max)
DECLARE @CurrentCommandType04 nvarchar(max)
DECLARE @CurrentCommandType05 nvarchar(max)
DECLARE @CurrentCommandType08 nvarchar(max)
DECLARE @CurrentCommandType09 nvarchar(max)DECLARE @tmpDatabases TABLE (ID int IDENTITY,
DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
AvailabilityGroup bit,
[Snapshot] bit,
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))DECLARE @tmpAvailabilityGroups TABLE (ID int IDENTITY PRIMARY KEY,
AvailabilityGroupName nvarchar(max),
Selected bit)DECLARE @tmpDatabasesAvailabilityGroups TABLE (DatabaseName nvarchar(max), AvailabilityGroupName nvarchar(max))
DECLARE @tmpFileGroups TABLE (ID int IDENTITY,
FileGroupID int,
FileGroupName nvarchar(max),
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))DECLARE @tmpObjects TABLE (ID int IDENTITY,
SchemaID int,
SchemaName nvarchar(max),
ObjectID int,
ObjectName nvarchar(max),
ObjectType nvarchar(max),
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
AvailabilityGroup nvarchar(max),
Selected bit)DECLARE @SelectedAvailabilityGroups TABLE (AvailabilityGroupName nvarchar(max),
Selected bit)DECLARE @SelectedFileGroups TABLE (DatabaseName nvarchar(max),
FileGroupName nvarchar(max),
Selected bit)DECLARE @SelectedObjects TABLE (DatabaseName nvarchar(max),
SchemaName nvarchar(max),
ObjectName nvarchar(max),
Selected bit)DECLARE @SelectedCheckCommands TABLE (CheckCommand nvarchar(max))
DECLARE @Error int
DECLARE @ReturnCode intSET @Error = 0
SET @ReturnCode = 0SET @Version = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1) + '.' + REPLACE(RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)), LEN(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)))),'.','') AS numeric(18,10))
SET @AmazonRDS = CASE WHEN DB_ID('rdsadmin') IS NOT NULL AND SUSER_SNAME(0x01) = 'rdsa' THEN 1 ELSE 0 END
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar(max)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @databases = ' + ISNULL('''' + REPLACE(@Databases,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @CheckCommands = ' + ISNULL('''' + REPLACE(@CheckCommands,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @PhysicalOnly = ' + ISNULL('''' + REPLACE(@PhysicalOnly,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @NoIndex = ' + ISNULL('''' + REPLACE(@NoIndex,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @ExtendedLogicalChecks = ' + ISNULL('''' + REPLACE(@ExtendedLogicalChecks,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @TabLock = ' + ISNULL('''' + REPLACE(@TabLock,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FileGroups = ' + ISNULL('''' + REPLACE(@FileGroups,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Objects = ' + ISNULL('''' + REPLACE(@Objects,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @AvailabilityGroups = ' + ISNULL('''' + REPLACE(@AvailabilityGroups,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @AvailabilityGroupReplicas = ' + ISNULL('''' + REPLACE(@AvailabilityGroupReplicas,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Updateability = ' + ISNULL('''' + REPLACE(@Updateability,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @LockTimeout = ' + ISNULL(CAST(@LockTimeout AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @LogToTable = ' + ISNULL('''' + REPLACE(@LogToTable,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Execute = ' + ISNULL('''' + REPLACE(@Execute,'''','''''') + '''','NULL') + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Source: https://ola.hallengren.com' + CHAR(13) + CHAR(10)
SET @StartMessage = REPLACE(@StartMessage,'%','%%') + ' '
RAISERROR(@StartMessage,10,1) WITH NOWAIT----------------------------------------------------------------------------------------------------
--// Check core requirements //--
----------------------------------------------------------------------------------------------------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
SET @ErrorMessage = 'The stored procedure CommandExecute is missing. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF 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 '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
BEGIN
SET @ErrorMessage = 'The stored procedure CommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/CommandExecute.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @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
SET @ErrorMessage = 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF SERVERPROPERTY('EngineEdition') = 5 AND @Version < 12
BEGIN
SET @ErrorMessage = 'The stored procedure DatabaseIntegrityCheck is not supported on this version of Azure SQL Database.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO Logging
END----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------SET @databases = REPLACE(@Databases, CHAR(10), '')
SET @databases = REPLACE(@Databases, CHAR(13), '')WHILE CHARINDEX(', ',@Databases) > 0 SET @databases = REPLACE(@Databases,', ',',')
WHILE CHARINDEX(' ,',@Databases) > 0 SET @databases = REPLACE(@Databases,' ,',',')SET @databases = LTRIM(RTRIM(@Databases));
WITH Databases1 (StartPosition, EndPosition, DatabaseItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @databases, 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @databases, EndPosition + 1), 0), LEN(@Databases) + 1) AS EndPosition,
SUBSTRING(@Databases, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @databases, EndPosition + 1), 0), LEN(@Databases) + 1) - EndPosition - 1) AS DatabaseItem
FROM Databases1
WHERE EndPosition < LEN(@Databases) + 1
),
Databases2 (DatabaseItem, Selected) AS
(
SELECT CASE WHEN DatabaseItem LIKE '-%' THEN RIGHT(DatabaseItem,LEN(DatabaseItem) - 1) ELSE DatabaseItem END AS DatabaseItem,
CASE WHEN DatabaseItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Databases1
),
Databases3 (DatabaseItem, DatabaseType, AvailabilityGroup, 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,
Selected
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, AvailabilityGroup, Selected) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
DatabaseType,
AvailabilityGroup,
Selected
FROM Databases3
)
INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, AvailabilityGroup, Selected)
SELECT DatabaseName,
DatabaseType,
AvailabilityGroup,
Selected
FROM Databases4
OPTION (MAXRECURSION 0)IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5
BEGIN
INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Snapshot], Selected, Completed)
SELECT [name] AS DatabaseName,
CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
CASE WHEN name IN (SELECT availability_databases_cluster.database_name FROM sys.availability_databases_cluster availability_databases_cluster) THEN 1 ELSE 0 END AS AvailabilityGroup,
CASE WHEN source_database_id IS NOT NULL THEN 1 ELSE 0 END AS [Snapshot],
0 AS Selected,
0 AS Completed
FROM sys.databases
ORDER BY [name] ASC
END
ELSE
BEGIN
INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, AvailabilityGroup, [Snapshot], Selected, Completed)
SELECT [name] AS DatabaseName,
CASE WHEN name IN('master','msdb','model') 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 Selected,
0 AS Completed
FROM sys.databases
ORDER BY [name] ASC
ENDUPDATE 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 = 1UPDATE 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 = 0IF @databases IS NOT NULL AND (NOT EXISTS(SELECT * FROM @SelectedDatabases) OR EXISTS(SELECT * FROM @SelectedDatabases WHERE DatabaseName IS NULL OR DatabaseName = ''))
BEGIN
SET @ErrorMessage = 'The value for the parameter @databases is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END----------------------------------------------------------------------------------------------------
--// Select availability groups //--
----------------------------------------------------------------------------------------------------IF @AvailabilityGroups IS NOT NULL AND @Version >= 11
BEGINSET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(10), '')
SET @AvailabilityGroups = REPLACE(@AvailabilityGroups, CHAR(13), '')WHILE CHARINDEX(', ',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,', ',',')
WHILE CHARINDEX(' ,',@AvailabilityGroups) > 0 SET @AvailabilityGroups = REPLACE(@AvailabilityGroups,' ,',',')SET @AvailabilityGroups = LTRIM(RTRIM(@AvailabilityGroups));
WITH AvailabilityGroups1 (StartPosition, EndPosition, AvailabilityGroupItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) AS EndPosition,
SUBSTRING(@AvailabilityGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @AvailabilityGroups, EndPosition + 1), 0), LEN(@AvailabilityGroups) + 1) - EndPosition - 1) AS AvailabilityGroupItem
FROM AvailabilityGroups1
WHERE EndPosition < LEN(@AvailabilityGroups) + 1
),
AvailabilityGroups2 (AvailabilityGroupItem, Selected) AS
(
SELECT CASE WHEN AvailabilityGroupItem LIKE '-%' THEN RIGHT(AvailabilityGroupItem,LEN(AvailabilityGroupItem) - 1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
CASE WHEN AvailabilityGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM AvailabilityGroups1
),
AvailabilityGroups3 (AvailabilityGroupItem, Selected) AS
(
SELECT CASE WHEN AvailabilityGroupItem = 'ALL_AVAILABILITY_GROUPS' THEN '%' ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
Selected
FROM AvailabilityGroups2
),
AvailabilityGroups4 (AvailabilityGroupName, Selected) AS
(
SELECT CASE WHEN LEFT(AvailabilityGroupItem,1) = '[' AND RIGHT(AvailabilityGroupItem,1) = ']' THEN PARSENAME(AvailabilityGroupItem,1) ELSE AvailabilityGroupItem END AS AvailabilityGroupItem,
Selected
FROM AvailabilityGroups3
)
INSERT INTO @SelectedAvailabilityGroups (AvailabilityGroupName, Selected)
SELECT AvailabilityGroupName, Selected
FROM AvailabilityGroups4
OPTION (MAXRECURSION 0)INSERT INTO @tmpAvailabilityGroups (AvailabilityGroupName, Selected)
SELECT name AS AvailabilityGroupName,
0 AS Selected
FROM sys.availability_groupsUPDATE tmpAvailabilityGroups
SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected
FROM @tmpAvailabilityGroups tmpAvailabilityGroups
INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups
ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')
WHERE SelectedAvailabilityGroups.Selected = 1UPDATE tmpAvailabilityGroups
SET tmpAvailabilityGroups.Selected = SelectedAvailabilityGroups.Selected
FROM @tmpAvailabilityGroups tmpAvailabilityGroups
INNER JOIN @SelectedAvailabilityGroups SelectedAvailabilityGroups
ON tmpAvailabilityGroups.AvailabilityGroupName LIKE REPLACE(SelectedAvailabilityGroups.AvailabilityGroupName,'_','[_]')
WHERE SelectedAvailabilityGroups.Selected = 0INSERT INTO @tmpDatabasesAvailabilityGroups (DatabaseName, AvailabilityGroupName)
SELECT availability_databases_cluster.database_name, availability_groups.name
FROM sys.availability_databases_cluster availability_databases_cluster
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_idUPDATE tmpDatabases
SET 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 = 1END
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)
BEGIN
SET @ErrorMessage = 'The value for the parameter @AvailabilityGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF (@Databases IS NULL AND @AvailabilityGroups IS NULL)
BEGIN
SET @ErrorMessage = 'You need to specify one of the parameters @databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF (@Databases IS NOT NULL AND @AvailabilityGroups IS NOT NULL)
BEGIN
SET @ErrorMessage = 'You can only specify one of the parameters @databases and @AvailabilityGroups.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END----------------------------------------------------------------------------------------------------
--// Select filegroups //--
----------------------------------------------------------------------------------------------------SET @FileGroups = REPLACE(@FileGroups, CHAR(10), '')
SET @FileGroups = REPLACE(@FileGroups, CHAR(13), '')WHILE CHARINDEX(', ',@FileGroups) > 0 SET @FileGroups = REPLACE(@FileGroups,', ',',')
WHILE CHARINDEX(' ,',@FileGroups) > 0 SET @FileGroups = REPLACE(@FileGroups,' ,',',')SET @FileGroups = LTRIM(RTRIM(@FileGroups));
WITH FileGroups1 (StartPosition, EndPosition, FileGroupItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FileGroups, 1), 0), LEN(@FileGroups) + 1) AS EndPosition,
SUBSTRING(@FileGroups, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) AS EndPosition,
SUBSTRING(@FileGroups, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FileGroups, EndPosition + 1), 0), LEN(@FileGroups) + 1) - EndPosition - 1) AS FileGroupItem
FROM FileGroups1
WHERE EndPosition < LEN(@FileGroups) + 1
),
FileGroups2 (FileGroupItem, Selected) AS
(
SELECT CASE WHEN FileGroupItem LIKE '-%' THEN RIGHT(FileGroupItem,LEN(FileGroupItem) - 1) ELSE FileGroupItem END AS FileGroupItem,
CASE WHEN FileGroupItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM FileGroups1
),
FileGroups3 (FileGroupItem, Selected) AS
(
SELECT CASE WHEN FileGroupItem = 'ALL_FILEGROUPS' THEN '%.%' ELSE FileGroupItem END AS FileGroupItem,
Selected
FROM FileGroups2
),
FileGroups4 (DatabaseName, FileGroupName, 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,
Selected
FROM FileGroups3
)
INSERT INTO @SelectedFileGroups (DatabaseName, FileGroupName, Selected)
SELECT DatabaseName, FileGroupName, Selected
FROM FileGroups4
OPTION (MAXRECURSION 0)----------------------------------------------------------------------------------------------------
--// Select objects //--
----------------------------------------------------------------------------------------------------SET @Objects = REPLACE(@Objects, CHAR(10), '')
SET @Objects = REPLACE(@Objects, CHAR(13), '')WHILE CHARINDEX(', ',@Objects) > 0 SET @Objects = REPLACE(@Objects,', ',',')
WHILE CHARINDEX(' ,',@Objects) > 0 SET @Objects = REPLACE(@Objects,' ,',',')SET @Objects = LTRIM(RTRIM(@Objects));
WITH Objects1 (StartPosition, EndPosition, ObjectItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Objects, 1), 0), LEN(@Objects) + 1) AS EndPosition,
SUBSTRING(@Objects, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) AS EndPosition,
SUBSTRING(@Objects, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Objects, EndPosition + 1), 0), LEN(@Objects) + 1) - EndPosition - 1) AS ObjectItem
FROM Objects1
WHERE EndPosition < LEN(@Objects) + 1
),
Objects2 (ObjectItem, Selected) AS
(
SELECT CASE WHEN ObjectItem LIKE '-%' THEN RIGHT(ObjectItem,LEN(ObjectItem) - 1) ELSE ObjectItem END AS ObjectItem,
CASE WHEN ObjectItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Objects1
),
Objects3 (ObjectItem, Selected) AS
(
SELECT CASE WHEN ObjectItem = 'ALL_OBJECTS' THEN '%.%.%' ELSE ObjectItem END AS ObjectItem,
Selected
FROM Objects2
),
Objects4 (DatabaseName, SchemaName, ObjectName, 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,
Selected
FROM Objects3
)
INSERT INTO @SelectedObjects (DatabaseName, SchemaName, ObjectName, Selected)
SELECT DatabaseName, SchemaName, ObjectName, Selected
FROM Objects4
OPTION (MAXRECURSION 0);----------------------------------------------------------------------------------------------------
--// Select check commands //--
----------------------------------------------------------------------------------------------------WITH CheckCommands (StartPosition, EndPosition, CheckCommand) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @CheckCommands, 1), 0), LEN(@CheckCommands) + 1) AS EndPosition,
SUBSTRING(@CheckCommands, 1, ISNULL(NULLIF(CHARINDEX(',', @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(',', @CheckCommands, EndPosition + 1), 0), LEN(@CheckCommands) + 1) AS EndPosition,
SUBSTRING(@CheckCommands, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @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')) OR EXISTS (SELECT * FROM @SelectedCheckCommands GROUP BY CheckCommand HAVING COUNT(*) > 1) OR NOT EXISTS (SELECT * FROM @SelectedCheckCommands) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKDB')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP','CHECKALLOC','CHECKTABLE','CHECKCATALOG'))) OR (EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKFILEGROUP')) AND EXISTS (SELECT CheckCommand FROM @SelectedCheckCommands WHERE CheckCommand IN('CHECKALLOC','CHECKTABLE')))
BEGIN
SET @ErrorMessage = 'The value for the parameter @CheckCommands is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @PhysicalOnly NOT IN ('Y','N') OR @PhysicalOnly IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @PhysicalOnly is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @NoIndex NOT IN ('Y','N') OR @NoIndex IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @NoIndex is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @ExtendedLogicalChecks NOT IN ('Y','N') OR @ExtendedLogicalChecks IS NULL OR (@ExtendedLogicalChecks = 'Y' AND NOT @Version >= 10) OR (@PhysicalOnly = 'Y' AND @ExtendedLogicalChecks = 'Y')
BEGIN
SET @ErrorMessage = 'The value for the parameter @ExtendedLogicalChecks is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @TabLock NOT IN ('Y','N') OR @TabLock IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @TabLock is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF EXISTS(SELECT * FROM @SelectedFileGroups WHERE DatabaseName IS NULL OR FileGroupName IS NULL) OR (@FileGroups IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedFileGroups)) OR (@FileGroups IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP'))
BEGIN
SET @ErrorMessage = 'The value for the parameter @FileGroups is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF EXISTS(SELECT * FROM @SelectedObjects WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL) OR (@Objects IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedObjects)) OR (@Objects IS NOT NULL AND NOT EXISTS (SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE'))
BEGIN
SET @ErrorMessage = 'The value for the parameter @Objects is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @MaxDOP < 0 OR @MaxDOP > 64 OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5)) OR (@MaxDOP IS NOT NULL AND @Version < 12.050000)
BEGIN
SET @ErrorMessage = 'The value for the parameter @MaxDOP is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @AvailabilityGroupReplicas NOT IN('ALL','PRIMARY','SECONDARY') OR @AvailabilityGroupReplicas IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @AvailabilityGroupReplicas is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @Updateability NOT IN('READ_ONLY','READ_WRITE','ALL') OR @Updateability IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @Updateability is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @LockTimeout < 0
BEGIN
SET @ErrorMessage = 'The value for the parameter @LockTimeout is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @LogToTable is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @Execute NOT IN('Y','N') OR @Execute IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @Execute is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
ENDIF @Error <> 0
BEGIN
SET @ErrorMessage = 'The documentation is available at https://ola.hallengren.com/sql-server-integrity-check.html.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @ReturnCode = @Error
GOTO Logging
END----------------------------------------------------------------------------------------------------
--// Check Availability Group cluster name //--
----------------------------------------------------------------------------------------------------IF @Version >= 11 AND SERVERPROPERTY('EngineEdition') <> 5
BEGIN
SELECT @Cluster = cluster_name
FROM sys.dm_hadr_cluster
END----------------------------------------------------------------------------------------------------
--// Execute commands //--
----------------------------------------------------------------------------------------------------WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Selected = 1 AND Completed = 0)
BEGINSELECT TOP 1 @CurrentDBID = ID,
@CurrentDatabaseName = DatabaseName
FROM @tmpDatabases
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASCSET @CurrentDatabaseID = DB_ID(@CurrentDatabaseName)
IF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE' AND SERVERPROPERTY('EngineEdition') <> 5
BEGIN
IF EXISTS (SELECT * FROM sys.database_recovery_status WHERE database_id = @CurrentDatabaseID AND database_guid IS NOT NULL)
BEGIN
SET @CurrentIsDatabaseAccessible = 1
END
ELSE
BEGIN
SET @CurrentIsDatabaseAccessible = 0
END
ENDIF @Version >= 11 AND @Cluster IS NOT NULL
BEGIN
SELECT @CurrentAvailabilityGroup = availability_groups.name,
@CurrentAvailabilityGroupRole = dm_hadr_availability_replica_states.role_desc
FROM sys.databases databases
INNER JOIN sys.availability_databases_cluster availability_databases_cluster ON databases.group_database_id = availability_databases_cluster.group_database_id
INNER JOIN sys.availability_groups availability_groups ON availability_databases_cluster.group_id = availability_groups.group_id
INNER JOIN sys.dm_hadr_availability_replica_states dm_hadr_availability_replica_states ON availability_groups.group_id = dm_hadr_availability_replica_states.group_id AND databases.replica_id = dm_hadr_availability_replica_states.replica_id
WHERE databases.name = @CurrentDatabaseName
ENDIF SERVERPROPERTY('EngineEdition') <> 5
BEGIN
SELECT @CurrentDatabaseMirroringRole = UPPER(mirroring_role_desc)
FROM sys.database_mirroring
WHERE database_id = @CurrentDatabaseID
ENDSELECT @CurrentIsReadOnly = is_read_only
FROM sys.databases
WHERE name = @CurrentDatabaseName-- Set database message
SET @DatabaseMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabaseName) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') AS nvarchar) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Standby: ' + CASE WHEN DATABASEPROPERTYEX(@CurrentDatabaseName,'IsInStandBy') = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Updateability: ' + CASE WHEN @CurrentIsReadOnly = 1 THEN 'READ_ONLY' WHEN @CurrentIsReadOnly = 0 THEN 'READ_WRITE' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'User access: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'UserAccess') AS nvarchar) + CHAR(13) + CHAR(10)
IF @CurrentIsDatabaseAccessible IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Is accessible: ' + CASE WHEN @CurrentIsDatabaseAccessible = 1 THEN 'Yes' ELSE 'No' END + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Recovery model: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Recovery') AS nvarchar) + CHAR(13) + CHAR(10)
IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group: ' + @CurrentAvailabilityGroup + CHAR(13) + CHAR(10)
IF @CurrentAvailabilityGroup IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Availability group role: ' + @CurrentAvailabilityGroupRole + CHAR(13) + CHAR(10)
IF @CurrentDatabaseMirroringRole IS NOT NULL SET @DatabaseMessage = @DatabaseMessage + 'Database mirroring role: ' + @CurrentDatabaseMirroringRole + CHAR(13) + CHAR(10)
SET @DatabaseMessage = REPLACE(@DatabaseMessage,'%','%%') + ' '
RAISERROR(@DatabaseMessage,10,1) WITH NOWAITIF DATABASEPROPERTYEX(@CurrentDatabaseName,'Status') = 'ONLINE'
AND (@CurrentIsDatabaseAccessible = 1 OR @CurrentIsDatabaseAccessible IS NULL)
AND (@CurrentAvailabilityGroupRole = @AvailabilityGroupReplicas 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')
BEGIN
SET @CurrentCommandType01 = 'DBCC_CHECKDB'SET @CurrentCommand01 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand01 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand01 = @CurrentCommand01 + 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabaseName)
IF @NoIndex = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', NOINDEX'
SET @CurrentCommand01 = @CurrentCommand01 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', DATA_PURITY'
IF @PhysicalOnly = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PHYSICAL_ONLY'
IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', EXTENDED_LOGICAL_CHECKS'
IF @TabLock = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', TABLOCK'
IF @MaxDOP IS NOT NULL SET @CurrentCommand01 = @CurrentCommand01 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @Command = @CurrentCommand01, @CommandType = @CurrentCommandType01, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput01 = @Error
IF @CurrentCommandOutput01 <> 0 SET @ReturnCode = @CurrentCommandOutput01
END-- Check filegroups
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKFILEGROUP')
BEGIN
SET @CurrentCommand02 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT data_space_id AS FileGroupID, name AS FileGroupName, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.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, Selected, Completed)
EXECUTE sp_executesql @statement = @CurrentCommand02
SET @Error = @@ERROR
IF @Error <> 0 SET @ReturnCode = @ErrorIF @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 = 1UPDATE 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
ENDWHILE EXISTS (SELECT * FROM @tmpFileGroups WHERE Selected = 1 AND Completed = 0)
BEGIN
SELECT TOP 1 @CurrentFGID = ID,
@CurrentFileGroupID = FileGroupID,
@CurrentFileGroupName = FileGroupName
FROM @tmpFileGroups
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC-- Does the filegroup exist?
SET @CurrentCommand03 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand03 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand03 = @CurrentCommand03 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups WHERE [type] <> ''FX'' AND filegroups.data_space_id = @ParamFileGroupID AND filegroups.[name] = @ParamFileGroupName) BEGIN SET @ParamFileGroupExists = 1 END'EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamFileGroupID int, @ParamFileGroupName sysname, @ParamFileGroupExists bit OUTPUT', @ParamFileGroupID = @CurrentFileGroupID, @ParamFileGroupName = @CurrentFileGroupName, @ParamFileGroupExists = @CurrentFileGroupExists OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentFileGroupExists IS NULL SET @CurrentFileGroupExists = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The file group ' + QUOTENAME(@CurrentFileGroupName) + ' in the database ' + QUOTENAME(@CurrentDatabaseName) + ' is locked. It could not be checked if the filegroup exists.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
ENDIF @CurrentFileGroupExists = 1
BEGIN
SET @CurrentCommandType04 = 'DBCC_CHECKFILEGROUP'SET @CurrentCommand04 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand04 = @CurrentCommand04 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; DBCC CHECKFILEGROUP (' + QUOTENAME(@CurrentFileGroupName)
IF @NoIndex = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', NOINDEX'
SET @CurrentCommand04 = @CurrentCommand04 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', PHYSICAL_ONLY'
IF @TabLock = 'Y' SET @CurrentCommand04 = @CurrentCommand04 + ', TABLOCK'
IF @MaxDOP IS NOT NULL SET @CurrentCommand04 = @CurrentCommand04 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @Command = @CurrentCommand04, @CommandType = @CurrentCommandType04, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput04 = @Error
IF @CurrentCommandOutput04 <> 0 SET @ReturnCode = @CurrentCommandOutput04
ENDUPDATE @tmpFileGroups
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentFGIDSET @CurrentFGID = NULL
SET @CurrentFileGroupID = NULL
SET @CurrentFileGroupName = NULL
SET @CurrentFileGroupExists = NULLSET @CurrentCommand03 = NULL
SET @CurrentCommand04 = NULLSET @CurrentCommandOutput04 = NULL
SET @CurrentCommandType04 = NULL
END
END-- Check disk space allocation structures
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKALLOC')
BEGIN
SET @CurrentCommandType05 = 'DBCC_CHECKALLOC'SET @CurrentCommand05 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand05 = @CurrentCommand05 + 'DBCC CHECKALLOC (' + QUOTENAME(@CurrentDatabaseName)
SET @CurrentCommand05 = @CurrentCommand05 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @TabLock = 'Y' SET @CurrentCommand05 = @CurrentCommand05 + ', TABLOCK'EXECUTE @CurrentCommandOutput05 = [dbo].[CommandExecute] @Command = @CurrentCommand05, @CommandType = @CurrentCommandType05, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput05 = @Error
IF @CurrentCommandOutput05 <> 0 SET @ReturnCode = @CurrentCommandOutput05
END-- Check objects
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKTABLE')
BEGIN
SET @CurrentCommand06 = '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 Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.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, Selected, Completed)
EXECUTE sp_executesql @statement = @CurrentCommand06
SET @Error = @@ERROR
IF @Error <> 0 SET @ReturnCode = @ErrorIF @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 = 1UPDATE 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
ENDWHILE EXISTS (SELECT * FROM @tmpObjects WHERE Selected = 1 AND Completed = 0)
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 ID ASC-- Does the object exist?
SET @CurrentCommand07 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand07 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand07 = @CurrentCommand07 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.schema_id = schemas.schema_id LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables ON objects.object_id = tables.object_id WHERE objects.[type] IN(''U'',''V'') AND EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.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'EXECUTE sp_executesql @statement = @CurrentCommand07, @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
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentObjectExists IS NULL SET @CurrentObjectExists = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the object exists.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
ENDIF @CurrentObjectExists = 1
BEGIN
SET @CurrentCommandType08 = 'DBCC_CHECKTABLE'SET @CurrentCommand08 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand08 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand08 = @CurrentCommand08 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; DBCC CHECKTABLE (''' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ''''
IF @NoIndex = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', NOINDEX'
SET @CurrentCommand08 = @CurrentCommand08 + ') WITH NO_INFOMSGS, ALL_ERRORMSGS'
IF @PhysicalOnly = 'N' SET @CurrentCommand08 = @CurrentCommand08 + ', DATA_PURITY'
IF @PhysicalOnly = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', PHYSICAL_ONLY'
IF @ExtendedLogicalChecks = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', EXTENDED_LOGICAL_CHECKS'
IF @TabLock = 'Y' SET @CurrentCommand08 = @CurrentCommand08 + ', TABLOCK'
IF @MaxDOP IS NOT NULL SET @CurrentCommand08 = @CurrentCommand08 + ', MAXDOP = ' + CAST(@MaxDOP AS nvarchar)EXECUTE @CurrentCommandOutput08 = [dbo].[CommandExecute] @Command = @CurrentCommand08, @CommandType = @CurrentCommandType08, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput08 = @Error
IF @CurrentCommandOutput08 <> 0 SET @ReturnCode = @CurrentCommandOutput08
ENDUPDATE @tmpObjects
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentOIDSET @CurrentOID = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentObjectType = NULL
SET @CurrentObjectExists = NULLSET @CurrentCommand07 = NULL
SET @CurrentCommand08 = NULLSET @CurrentCommandOutput08 = NULL
SET @CurrentCommandType08 = NULL
END
END-- Check catalog
IF EXISTS(SELECT * FROM @SelectedCheckCommands WHERE CheckCommand = 'CHECKCATALOG')
BEGIN
SET @CurrentCommandType09 = 'DBCC_CHECKCATALOG'SET @CurrentCommand09 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand09 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand09 = @CurrentCommand09 + 'DBCC CHECKCATALOG (' + QUOTENAME(@CurrentDatabaseName)
SET @CurrentCommand09 = @CurrentCommand09 + ') WITH NO_INFOMSGS'EXECUTE @CurrentCommandOutput09 = [dbo].[CommandExecute] @Command = @CurrentCommand09, @CommandType = @CurrentCommandType09, @Mode = 1, @DatabaseName = @CurrentDatabaseName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput09 = @Error
IF @CurrentCommandOutput09 <> 0 SET @ReturnCode = @CurrentCommandOutput09
ENDEND
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentDBID-- Clear variables
SET @CurrentDBID = NULL
SET @CurrentDatabaseID = NULL
SET @CurrentDatabaseName = NULL
SET @CurrentIsDatabaseAccessible = NULL
SET @CurrentAvailabilityGroup = NULL
SET @CurrentAvailabilityGroupRole = NULL
SET @CurrentDatabaseMirroringRole = NULL
SET @CurrentIsReadOnly = NULLSET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommand05 = NULL
SET @CurrentCommand06 = NULL
SET @CurrentCommand09 = NULLSET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput05 = NULL
SET @CurrentCommandOutput09 = NULLSET @CurrentCommandType01 = NULL
SET @CurrentCommandType05 = NULL
SET @CurrentCommandType09 = NULLDELETE FROM @tmpFileGroups
DELETE FROM @tmpObjectsEND
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------Logging:
SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAITIF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END----------------------------------------------------------------------------------------------------
END
This is the error I am getting.
Unable to start execution of step 1 (reason: Variable SQLLOGDIR not found). The step failed.
Can someone guide me on how to fix this issue?
Have you checked the log file for the job. His script will create a log file and write on every run. Check the error log location and get some input.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 12, 2018 at 7:55 am
https://stackoverflow.com/questions/52281868/variable-for-sqllogdir-not-found
Check this as well.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply