SQL Server 2005 - Backup, Integrity Check and Index Optimization - SQL Server Central
I have made a solution for backup, integrity check and index
optimization in SQL Server 2005. The solution is based on stored
procedures, functions, sqlcmd and SQL Server Agent jobs.
- Dynamic selection of databases, e.g. USER_DATABASES.
- Database state check. If a database is not online the procedure logs the database state and continues to the next database.
- Robust
error handling and logging. If an error occurs the procedure logs the
error and continues to the next database or index. In the end the job
reports failure. Information about all commands are logged with start
time, command text, command output and end time.
- Database
backup features. Full, differential and transaction log backups.
Automatic creation of backup directories. Backup file names with the
name of the instance, the name of the database, backup type, date and
time. Verification of backups. Deletion of old backup files.
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24
- Database integrity check features.
EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES'
- Dynamic
index optimization. Rebuild indexes online or offline, reorganize
indexes, update statistics, reorganize indexes and update statistics or
do nothing based on fragmentation level and lob existence.
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000
Please see the
code and the
documentation.
Ola Hallengren
http://ola.hallengren.com
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DatabaseSelect] (@DatabaseList varchar(max))
RETURNS @Database TABLE(DatabaseName varchar(max) NOT NULL)
AS
BEGIN
DECLARE @Database01 TABLE( DatabaseName varchar(max),
DatabaseStatus bit)
DECLARE @Database02 TABLE( DatabaseName varchar(max),
DatabaseStatus bit)
DECLARE @DatabaseItem varchar(max)
DECLARE @Position int
SET @DatabaseList = LTRIM(RTRIM(@DatabaseList))
SET @DatabaseList = REPLACE(@DatabaseList,' ','')
SET @DatabaseList = REPLACE(@DatabaseList,'[','')
SET @DatabaseList = REPLACE(@DatabaseList,']','')
SET @DatabaseList = REPLACE(@DatabaseList,'''','')
SET @DatabaseList = REPLACE(@DatabaseList,'"','')
WHILE CHARINDEX(',,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,',,',',')
IF RIGHT(@DatabaseList,1) = ',' SET @DatabaseList = LEFT(@DatabaseList,LEN(@DatabaseList) - 1)
IF LEFT(@DatabaseList,1) = ',' SET @DatabaseList = RIGHT(@DatabaseList,LEN(@DatabaseList) - 1)
WHILE LEN(@DatabaseList) > 0
BEGIN
SET @Position = CHARINDEX(',', @DatabaseList)
IF @Position = 0
BEGIN
SET @DatabaseItem = @DatabaseList
SET @DatabaseList = ''
END
ELSE
BEGIN
SET @DatabaseItem = LEFT(@DatabaseList, @Position - 1)
SET @DatabaseList = RIGHT(@DatabaseList, LEN(@DatabaseList) - @Position)
END
INSERT INTO @Database01 (DatabaseName) VALUES(@DatabaseItem)
END
UPDATE @Database01
SET DatabaseStatus = 1
WHERE DatabaseName NOT LIKE '-%'
UPDATE @Database01
SET DatabaseName = RIGHT(DatabaseName,LEN(DatabaseName) - 1), DatabaseStatus = 0
WHERE DatabaseName LIKE '-%'
INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT DISTINCT DatabaseName, DatabaseStatus
FROM @Database01
WHERE DatabaseName NOT IN('SYSTEM_DATABASES','USER_DATABASES')
IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'SYSTEM_DATABASES' AND DatabaseStatus = 0)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('master', 0)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('model', 0)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('msdb', 0)
END
IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'SYSTEM_DATABASES' AND DatabaseStatus = 1)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('master', 1)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('model', 1)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('msdb', 1)
END
IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'USER_DATABASES' AND DatabaseStatus = 0)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT [name], 0
FROM sys.databases
WHERE database_id > 4
END
IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'USER_DATABASES' AND DatabaseStatus = 1)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT [name], 1
FROM sys.databases
WHERE database_id > 4
END
INSERT INTO @Database (DatabaseName)
SELECT [name]
FROM sys.databases
WHERE [name] <> 'tempdb'
INTERSECT
SELECT DatabaseName
FROM @Database02
WHERE DatabaseStatus = 1
EXCEPT
SELECT DatabaseName
FROM @Database02
WHERE DatabaseStatus = 0
RETURN
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CommandExecute]
@Command varchar(max),
@Comment varchar(max),
@Mode int
AS
SET NOCOUNT ON
SET LOCK_TIMEOUT 3600000
----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------
DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @ErrorMessage varchar(max)
DECLARE @Error int
SET @Error = 0
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @Command IS NULL OR @Command = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Command is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Comment IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Comment is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Mode NOT IN(1,2) OR @Mode IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Mode is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------
IF @Error <> 0 GOTO ReturnCode
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Command: ' + @Command + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Comment: ' + @Comment
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Execute command //--
----------------------------------------------------------------------------------------------------
IF @Mode = 1
BEGIN
EXECUTE(@Command)
SET @Error = @@ERROR
END
IF @Mode = 2
BEGIN
BEGIN TRY
EXECUTE(@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS varchar) + ', ' + ISNULL(ERROR_MESSAGE(),'')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END CATCH
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
RAISERROR(@EndMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Return code //--
----------------------------------------------------------------------------------------------------
ReturnCode:
RETURN @Error
----------------------------------------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DatabaseBackup]
@Databases varchar(max),
@Directory varchar(max),
@BackupType varchar(max),
@Verify varchar(max),
@CleanupTime int
AS
SET NOCOUNT ON
----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------
DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)
DECLARE @InstanceName varchar(max)
DECLARE @FileExtension varchar(max)
DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentDirectory varchar(max)
DECLARE @CurrentDate varchar(max)
DECLARE @CurrentFileName varchar(max)
DECLARE @CurrentFilePath varchar(max)
DECLARE @CurrentCleanupTime varchar(max)
DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommand02 varchar(max)
DECLARE @CurrentCommand03 varchar(max)
DECLARE @CurrentCommand04 varchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput02 int
DECLARE @CurrentCommandOutput03 int
DECLARE @CurrentCommandOutput04 int
DECLARE @DirectoryInfo TABLE ( FileExists bit,
FileIsADirectory bit,
ParentDirectoryExists bit)
DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY,
DatabaseName varchar(max),
Completed bit)
DECLARE @Error int
SET @Error = 0
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + ', @Directory = ' + ISNULL('''' + @Directory + '''','NULL')
SET @StartMessage = @StartMessage + ', @BackupType = ' + ISNULL('''' + @BackupType + '''','NULL')
SET @StartMessage = @StartMessage + ', @Verify = ' + ISNULL('''' + @Verify + '''','NULL')
SET @StartMessage = @StartMessage + ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS varchar),'NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------
IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0 AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check directory //--
----------------------------------------------------------------------------------------------------
IF NOT (@Directory LIKE '_:' OR @Directory LIKE '_:\%') OR @Directory LIKE '%\' OR @Directory IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Directory is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)
EXECUTE('EXECUTE xp_FileExist ''' + @Directory + '''')
IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)
BEGIN
SET @ErrorMessage = 'The directory does not exist.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check backup type //--
----------------------------------------------------------------------------------------------------
SET @BackupType = UPPER(@BackupType)
IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @BackupType is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check Verify input //--
----------------------------------------------------------------------------------------------------
IF @Verify NOT IN ('Y','N') OR @Verify IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Verify is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check CleanupTime input //--
----------------------------------------------------------------------------------------------------
IF @CleanupTime < 0 OR @CleanupTime IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @CleanupTime is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------
IF @Error <> 0 GOTO Logging
----------------------------------------------------------------------------------------------------
--// Set global variables //--
----------------------------------------------------------------------------------------------------
SET @InstanceName = REPLACE(CAST(SERVERPROPERTY('servername') AS varchar),'\','$')
SELECT @FileExtension = CASE
WHEN @BackupType = 'FULL' THEN 'bak'
WHEN @BackupType = 'DIFF' THEN 'bak'
WHEN @BackupType = 'LOG' THEN 'trn'
END
----------------------------------------------------------------------------------------------------
--// Execute backup commands //--
----------------------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC
-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN
SET @CurrentDirectory = @Directory + '\' + @InstanceName + '\' + @CurrentDatabase + '\' + @BackupType
SET @CurrentDate = REPLACE(REPLACE(REPLACE((CONVERT(varchar,GETDATE(),120)),'-',''),' ','_'),':','')
SET @CurrentFileName = @InstanceName + '_' + @CurrentDatabase + '_' + @BackupType + '_' + @CurrentDate + '.' + @FileExtension
SET @CurrentFilePath = @CurrentDirectory + '\' + @CurrentFileName
SET @CurrentCleanupTime = CONVERT(varchar(19),(DATEADD(hh,-(@CleanupTime),GETDATE())),126)
-- Create directory
SET @CurrentCommand01 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_create_subdir ''' + @CurrentDirectory + ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput01 = @ERROR
-- Perform a backup
IF @CurrentCommandOutput01 = 0
BEGIN
SELECT @CurrentCommand02 = CASE
WHEN @BackupType = 'FULL' THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabase) + ' TO DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM'
WHEN @BackupType = 'DIFF' THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabase) + ' TO DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM, DIFFERENTIAL'
WHEN @BackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabase) + ' TO DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM'
END
EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput02 = @ERROR
END
-- Verify the backup
IF @CurrentCommandOutput02 = 0 AND @Verify = 'Y'
BEGIN
SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM'
EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @CurrentCommand03, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput03 = @ERROR
END
-- Delete old backup files
IF (@CurrentCommandOutput02 = 0 AND @Verify = 'N')
OR (@CurrentCommandOutput02 = 0 AND @Verify = 'Y' AND @CurrentCommandOutput03 = 0)
BEGIN
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_delete_file 0, ''' + @CurrentDirectory + ''', ''' + @FileExtension + ''', ''' + @CurrentCleanupTime + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @CurrentCommand04, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput04 = @ERROR
END
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID
-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentDirectory = NULL
SET @CurrentDate = NULL
SET @CurrentFileName = NULL
SET @CurrentFilePath = NULL
SET @CurrentCleanupTime = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommand03 = NULL
SET @CurrentCommand04 = NULL
SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL
SET @CurrentCommandOutput03 = NULL
SET @CurrentCommandOutput04 = NULL
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)
RAISERROR(@EndMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]
@Databases varchar(max)
AS
SET NOCOUNT ON
----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------
DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)
DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY,
DatabaseName varchar(max),
Completed bit)
DECLARE @Error int
SET @Error = 0
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------
IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0 AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------
IF @Error <> 0 GOTO Logging
----------------------------------------------------------------------------------------------------
--// Execute commands //--
----------------------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC
-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(13) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN
SET @CurrentCommand01 = 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabase) + ') WITH DATA_PURITY, NO_INFOMSGS'
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput01 = @ERROR
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID
-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommandOutput01 = NULL
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)
RAISERROR(@EndMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IndexOptimize]
@Databases varchar(max),
@FragmentationHigh_LOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationLow_LOB varchar(max) = 'NOTHING',
@FragmentationLow_NonLOB varchar(max) = 'NOTHING',
@FragmentationLevel1 tinyint = 5,
@FragmentationLevel2 tinyint = 30,
@PageCountLevel int = 1000
AS
SET NOCOUNT ON
----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------
DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)
DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentCommandSelect01 varchar(max)
DECLARE @CurrentCommandSelect02 varchar(max)
DECLARE @CurrentCommandSelect03 varchar(max)
DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommand02 varchar(max)
DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput02 int
DECLARE @CurrentIxID int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName varchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName varchar(max)
DECLARE @CurrentIndexID int
DECLARE @CurrentIndexName varchar(max)
DECLARE @CurrentIndexType int
DECLARE @CurrentIndexExists bit
DECLARE @CurrentIsLOB bit
DECLARE @CurrentFragmentationLevel float
DECLARE @CurrentPageCount bigint
DECLARE @CurrentAction varchar(max)
DECLARE @CurrentComment varchar(max)
DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY,
DatabaseName varchar(max),
Completed bit)
DECLARE @tmpIndexes TABLE ( IxID int IDENTITY PRIMARY KEY,
SchemaID int,
SchemaName varchar(max),
ObjectID int,
ObjectName varchar(max),
IndexID int,
IndexName varchar(max),
IndexType int,
Completed bit)
DECLARE @tmpIndexExists TABLE ([Count] int)
DECLARE @tmpIsLOB TABLE ([Count] int)
DECLARE @Actions TABLE ([Action] varchar(max))
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
INSERT INTO @Actions([Action]) VALUES('STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE_STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('NOTHING')
DECLARE @Error int
SET @Error = 0
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_LOB = ' + ISNULL('''' + @FragmentationHigh_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_NonLOB = ' + ISNULL('''' + @FragmentationHigh_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_LOB = ' + ISNULL('''' + @FragmentationMedium_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_NonLOB = ' + ISNULL('''' + @FragmentationMedium_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_LOB = ' + ISNULL('''' + @FragmentationLow_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_NonLOB = ' + ISNULL('''' + @FragmentationLow_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS varchar),'NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)
RAISERROR(@StartMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------
IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0 AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @FragmentationHigh_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationHigh_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationHigh_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationHigh_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationMedium_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationMedium_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationMedium_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationMedium_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLow_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLow_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLow_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLow_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @PageCountLevel < 0 OR @PageCountLevel IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------
IF @Error <> 0 GOTO Logging
----------------------------------------------------------------------------------------------------
--// Execute commands //--
----------------------------------------------------------------------------------------------------
WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC
-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(13) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT
IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN
-- Select indexes in the current database
SET @CurrentCommandSelect01 = 'SELECT sys.schemas.[schema_id], sys.schemas.[name], sys.objects.[object_id], sys.objects.[name], sys.indexes.index_id, sys.indexes.[name], sys.indexes.[type], 0 FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.objects.is_ms_shipped = 0 AND sys.indexes.[type] IN(1,2) ORDER BY sys.schemas.[schema_id] ASC, sys.objects.[object_id] ASC, sys.indexes.index_id ASC'
INSERT INTO @tmpIndexes (SchemaID, SchemaName, ObjectID, ObjectName, IndexID, IndexName, IndexType, Completed)
EXECUTE(@CurrentCommandSelect01)
WHILE EXISTS (SELECT * FROM @tmpIndexes WHERE Completed = 0)
BEGIN
SELECT TOP 1 @CurrentIxID = IxID,
@CurrentSchemaID = SchemaID,
@CurrentSchemaName = SchemaName,
@CurrentObjectID = ObjectID,
@CurrentObjectName = ObjectName,
@CurrentIndexID = IndexID,
@CurrentIndexName = IndexName,
@CurrentIndexType = IndexType
FROM @tmpIndexes
WHERE Completed = 0
ORDER BY IxID ASC
-- Does the index exist?
SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.indexes.index_id > 0 AND sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS varchar) + ' AND sys.schemas.[name] = ''' + @CurrentSchemaName + ''' AND sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.objects.[name] = ''' + @CurrentObjectName + ''' AND sys.indexes.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND sys.indexes.[name] = ''' + @CurrentIndexName + ''' AND sys.indexes.[type] = ' + CAST(@CurrentIndexType AS varchar)
INSERT INTO @tmpIndexExists ([Count])
EXECUTE(@CurrentCommandSelect02)
IF (SELECT [Count] FROM @tmpIndexExists) > 0 BEGIN SET @CurrentIndexExists = 1 END ELSE BEGIN SET @CurrentIndexExists = 0 END
IF @CurrentIndexExists = 0 GOTO NoAction
-- Does the index contain a LOB?
IF @CurrentIndexType = 1 SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND (sys.types.name IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.name IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.columns.max_length = -1))'
IF @CurrentIndexType = 2 SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.columns ON sys.index_columns.[object_id] = sys.columns.[object_id] AND sys.index_columns.column_id = sys.columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.index_columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.index_columns.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND (sys.types.[name] IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.[name] IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.types.max_length = -1))'
INSERT INTO @tmpIsLOB ([Count])
EXECUTE(@CurrentCommandSelect03)
IF (SELECT [Count] FROM @tmpIsLOB) > 0 BEGIN SET @CurrentIsLOB = 1 END ELSE BEGIN SET @CurrentIsLOB = 0 END
-- Is the index fragmented?
SELECT @CurrentFragmentationLevel = avg_fragmentation_in_percent,
@CurrentPageCount = page_count
FROM sys.dm_db_index_physical_stats(DB_ID(@CurrentDatabase), @CurrentObjectID, @CurrentIndexID, NULL, 'LIMITED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
AND index_level = 0
-- Decide action
SELECT @CurrentAction = CASE
WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_LOB
WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_NonLOB
WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_LOB
WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_NonLOB
WHEN @CurrentIsLOB = 1 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_LOB
WHEN @CurrentIsLOB = 0 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_NonLOB
END
-- Create comment
SET @CurrentComment = 'IndexType: ' + CAST(@CurrentIndexType AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'LOB: ' + CAST(@CurrentIsLOB AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'PageCount: ' + CAST(@CurrentPageCount AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + CAST(@CurrentFragmentationLevel AS varchar)
IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE','INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE')
BEGIN
SELECT @CurrentCommand01 = CASE
WHEN @CurrentAction = 'INDEX_REBUILD_ONLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'
WHEN @CurrentAction = 'INDEX_REBUILD_OFFLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF)'
WHEN @CurrentAction IN('INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE') THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REORGANIZE'
END
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, @CurrentComment, 2
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput01 = @ERROR
END
IF @CurrentAction IN('INDEX_REORGANIZE_STATISTICS_UPDATE','STATISTICS_UPDATE')
BEGIN
SET @CurrentCommand02 = 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentIndexName)
EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, @CurrentComment, 2
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput02 = @ERROR
END
NoAction:
-- Update that the index is completed
UPDATE @tmpIndexes
SET Completed = 1
WHERE IxID = @CurrentIxID
-- Clear variables
SET @CurrentCommandSelect02 = NULL
SET @CurrentCommandSelect03 = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL
SET @CurrentIxID = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentIndexID = NULL
SET @CurrentIndexName = NULL
SET @CurrentIndexType = NULL
SET @CurrentIndexExists = NULL
SET @CurrentIsLOB = NULL
SET @CurrentFragmentationLevel = NULL
SET @CurrentPageCount = NULL
SET @CurrentAction = NULL
SET @CurrentComment = NULL
DELETE FROM @tmpIndexExists
DELETE FROM @tmpIsLOB
END
END
-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID
-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentCommandSelect01 = NULL
DELETE FROM @tmpIndexes
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
Logging:
SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)
RAISERROR(@EndMessage,10,1) WITH NOWAIT
----------------------------------------------------------------------------------------------------
GO
Scheduling Jobs in SQL Server Express - SQLTeam.com
