Set Up the Backup routine using the Windows Schedule Task - SQL Express
1º - Prepare the database objects on Master DB
-------------------------------------------------------------------
USE [master]
GO
/****** Object: StoredProcedure [DBO].[spMaintenance] Script Date: 13/02/2017 11:16:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('[DBO].[spMaintenance]') > 0
drop proc [DBO].[spMaintenance]
go
Create proc [DBO].[spMaintenance]
@Operation varchar(50) -- BackupDB, BackupLog, CycleSQLLog, ReIndex
as
/******************************************************************************
Name: DBO.spMaintenance
Run manual maintenance tasks. Uusally needed for SQL Express Edition bacause of
the lack of the SQL Agent. Must be called externally from a scheduler using SQLCMD.
******************************************************************************
Change History
******************************************************************************
Date: Author: Description:
----- ------- -------------------
13 Jan 2017 Leandro Buffone - Turners Created
*****************************************************************************
This is run from a Windows Task Scheduler with the following config
User: SYSTEM (or a user with a non expiring password)
Program: "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
Arguments: -Usa -PPW -Q "exec DBO.spMaintenance @Operation='ReIndex'"
Usage:
exec DBO.spMaintenance @Operation = 'BackupDB'
exec DBO.spMaintenance @Operation = 'BackupLog'
exec DBO.spMaintenance @Operation = 'ReIndex'
exec DBO.spMaintenance @Operation = 'CycleLog'
*/
if @Operation = 'BackupDB'
exec DBO.spExpressMaint
@Database = 'ALL_SYSTEM',
@OpType = 'DB',
@BackupFldr = 'D:\SQL\Backup',
@ReportFldr = 'D:\SQL\Maintenance',
@Verify = 1,
@DBRetainUnit = 'days',
@DBRetainVal = 4,
@RptRetainUnit = 'weeks',
@RptRetainVal = 1,
@Report = 1
if @Operation = 'BackupDB'
exec DBO.spExpressMaint
@Database = 'ALL_USER',
@OpType = 'DB',
@BackupFldr = 'D:\SQL\Backup',
@ReportFldr = 'D:\SQL\Maintenance',
@Verify = 1,
@DBRetainUnit = 'days',
@DBRetainVal = 4,
@RptRetainUnit = 'weeks',
@RptRetainVal = 1,
@Report = 1
if @Operation = 'BackupLog'
exec DBO.spExpressMaint
@Database = 'ALL_USER',
@OpType = 'LOG',
@BackupFldr = 'D:\SQL\Backup',
@ReportFldr = 'D:\SQL\Maintenance',
@Verify = 1,
@DBRetainUnit = 'days',
@DBRetainVal = 4,
@RptRetainUnit = 'weeks',
@RptRetainVal = 1,
@Report = 1
if @Operation = 'ReIndex'
exec DBO.spIndexOptimize @Databases = 'ALL_DATABASES'
if @Operation = 'CycleLog'
exec sp_cycle_errorlog
Go
USE [master]
GO
/****** Object: StoredProcedure [DBO].[spExpressMaint] Script Date: 13/02/2017 11:26:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('[DBO].[spExpressMaint]') > 0
drop proc [DBO].[spExpressMaint]
go
CREATE proc [DBO].[spExpressMaint]
@database sysname, -- database name | ALL_USER | ALL_SYSTEM
@optype varchar(7), -- LOG | DB | DIFF | REINDEX | REORG | CHECKDB
@backupwith varchar(500) = NULL, -- additional backup options
@backupfldr varchar(200) = NULL, -- folder to write backup to
@reportfldr varchar(200) = NULL, -- folder to write text report
@verify bit = 1, -- verify backup
@verifywith varchar(500) = NULL, -- additional verify options
@dbretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
@dbretainval int = 1, -- specifies how many retainunits to keep backup
@report bit = 1, -- flag to indicate whether to generate report
@rptretainunit varchar(10) = NULL, -- minutes | hours | days | weeks | months | copies
@rptretainval int = 1, -- specifies how many retainunits to keep reports
@checkattrib bit = 0, -- check if archive bit is cleared before deleting
@delfirst bit = 0, -- delete before backup (handy if space issues)
@debug bit = 0 -- print commands to be executed
as
/******************************************************************************
Name: DBO.spExpressMaint
see https://www.sqlDBOtips.com/showarticle.asp?ID=27 for documentation
Date Author Notes
24/07/2004 Jasper Smith Initial release
Usage:
exec DBO.spExpressMaint
@Database = 'ALL_USER',
@OpType = 'LOG',
@BackupFldr = 'D:\SQL\Backup',
@ReportFldr = 'D:\SQL\Maintenance',
@Verify = 1,
@DBRetainUnit = 'days',
@DBRetainVal = 4,
@RptRetainUnit = 'weeks',
@RptRetainVal = 1,
@Report = 1
*/
SET NOCOUNT ON
SET DATEFORMAT YMD
/************************
VARIABLE DECLARATION
************************/
DECLARE @fso int
DECLARE @file int
DECLARE @reportfilename varchar(500)
DECLARE @backupfilename varchar(500)
DECLARE @delfilename varchar(500)
DECLARE @cmd varchar(650)
DECLARE @backupfldrorig varchar(200)
DECLARE @databaseorig sysname
DECLARE @table nvarchar(600)
DECLARE @exists varchar(5)
DECLARE @err int
DECLARE @start datetime
DECLARE @finish datetime
DECLARE @runtime datetime
DECLARE @output varchar(200)
DECLARE @errormsg varchar(210)
DECLARE @datepart nchar(2)
DECLARE @execmd nvarchar(1000)
DECLARE @delcmd nvarchar(1000)
DECLARE @exemsg varchar(8000)
DECLARE @filecount int ; SET @filecount = 0
DECLARE @delcount int ; SET @delcount = 0
DECLARE @hr int ; SET @hr = 0
DECLARE @ret int ; SET @ret = 0
DECLARE @cmdret int ; SET @cmdret = 0
DECLARE @delbkflag int ; SET @delbkflag = 0
DECLARE @delrptflag int ; SET @delrptflag = 0
DECLARE @filecrt int ; SET @filecrt = 0
DECLARE @user sysname ; SET @user = SUSER_SNAME()
DECLARE @jobdt datetime ; SET @jobdt = GETDATE()
DECLARE @jobstart char(12) ;
DECLARE @stage int ; SET @stage = 1
SET @jobstart = CONVERT(char(8),@jobdt,112)+LEFT(REPLACE(CONVERT(char(8),@jobdt,108),':',''),4)
IF RIGHT(@reportfldr,1)<>'\' SET @reportfldr = @reportfldr + '\'
IF RIGHT(@backupfldr,1)<>'\' SET @backupfldr = @backupfldr + '\'
SET @backupfldrorig = @backupfldr
SET @databaseorig = @database
CREATE TABLE #files(filename varchar(255))
CREATE TABLE #exists(exist int,isdir int,parent int)
CREATE TABLE #databases(dbname sysname)
/**********************************
INITIALIZE FSO IF @report = 1
***********************************/
IF @report = 1
BEGIN
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR('Error creating File System Object',16,1)
SET @ret = 1
GOTO CLEANUP
END
END
/************************
CHECK INPUT
************************/
-- check sysadmin
IF IS_SRVROLEMEMBER('sysadmin') = 0
BEGIN
RAISERROR('The current user %s is not a member of the sysadmin role',16,1,@user)
SET @ret = 1
GOTO CLEANUP
END
-- check database exists and is online
IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF (DB_ID(@database) IS NULL) OR ((select state from sys.databases where name = @database) <>0)
BEGIN
RAISERROR('Database %s is invalid or database status is not ONLINE',16,1,@database)
SET @ret = 1
GOTO CLEANUP
END
END
-- check @optype is valid
IF UPPER(@optype) NOT IN ('LOG','DB','DIFF','REINDEX','REORG','CHECKDB')
BEGIN
RAISERROR('%s is not a valid option for @optype',16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END
-- check recovery mode is correct if trying log backup
IF @database NOT IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF (@optype = 'LOG' and ((select recovery_model from sys.databases where name = @database) = 3))
BEGIN
RAISERROR('%s is not a valid option for database %s because it is in SIMPLE recovery mode',16,1,@optype,@database)
SET @ret = 1
GOTO CLEANUP
END
END
-- no log backups for system databases
IF @database = 'ALL_SYSTEM'
BEGIN
IF @optype = 'LOG'
BEGIN
RAISERROR('%s is not a valid option for the option ALL_SYSTEM',16,1,@optype)
SET @ret = 1
GOTO CLEANUP
END
END
-- check that @backupfldr exists on the server
IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@backupfldr
IF @exists <> 'True'
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
INSERT #exists
EXEC master.dbo.xp_fileexist @backupfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@backupfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END
-- check that @reportfldr exists on the server
IF @reportfldr IS NOT NULL or @report = 1
BEGIN
IF @report = 1
BEGIN
EXEC sp_OAMethod @fso,'FolderExists',@exists OUT,@reportfldr
IF @exists <> 'True'
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
ELSE
BEGIN
DELETE #exists
INSERT #exists
EXEC master.dbo.xp_fileexist @reportfldr
IF (SELECT MAX(isdir) FROM #exists)<>1
BEGIN
RAISERROR('The folder %s does not exist on this server',16,1,@reportfldr)
SET @ret = 1
GOTO CLEANUP
END
END
END
-- check @dbretainunit is a vaild value
IF @optype NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
IF UPPER(@dbretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES')
BEGIN
RAISERROR('%s is not a valid value for @dbretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@dbretainunit)
SET @ret = 1
GOTO CLEANUP
END
END
--check @dbretainval is a vaild value
IF @dbretainval<1
BEGIN
RAISERROR('%i is not a valid value for @dbretainval (must be >0)',16,1,@dbretainval)
SET @ret = 1
GOTO CLEANUP
END
-- check @rptretainunit is a vaild value if present
IF UPPER(@rptretainunit) NOT IN ('MINUTES','HOURS','DAYS','WEEKS','MONTHS','COPIES') and @rptretainunit IS NOT NULL
BEGIN
RAISERROR('%s is not a valid value for @rptretainunit (''minutes | hours | days | weeks | months | copies'')',16,1,@rptretainunit)
SET @ret = 1
GOTO CLEANUP
END
--check @rptretainval is a vaild value
IF @rptretainval<1
BEGIN
RAISERROR('%i is not a valid value for @rptretainval (must be >0)',16,1,@rptretainval)
SET @ret = 1
GOTO CLEANUP
END
/***********************************
list of databases to process
************************************/
IF @database IN ('ALL_USER','ALL_SYSTEM')
BEGIN
IF @database = 'ALL_USER'
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id > 4
AND (@optype <> 'LOG' OR recovery_model <> '3')
ELSE
INSERT #databases(dbname)
SELECT [name] from sys.databases where database_id in (1,3,4)
END
ELSE
INSERT #databases(dbname) SELECT @database
/***********************************
INITIALIZE REPORT IF @report = 1
************************************/
-- generate report filename
SELECT @reportfilename = @reportfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_'
WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
END + @jobstart + '.txt'
-- if no report just set @reportfilename to NULL
IF @report = 0 SET @reportfilename = NULL
IF @debug = 1
BEGIN
PRINT '@reportfilename = ' + ISNULL(@reportfilename,'NULL')
END
IF @report = 1
BEGIN
-- create report file
EXEC @hr=sp_OAMethod @fso, 'CreateTextFile',@file OUT, @reportfilename
IF (@hr <> 0)
BEGIN
EXEC sp_OAGetErrorInfo @fso
RAISERROR('Error creating log file',16,1)
SET @ret = 1
GOTO CLEANUP
END
ELSE
-- set global flag to indicate we have created a report file
SET @filecrt = 1
-- write header
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = 'Expressmaint utility, Logged on to SQL Server [' + @@SERVERNAME + '] as ' + '[' + @user + ']'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
IF UPPER(@optype) NOT IN ('REINDEX','CHECKDB','REORG')
BEGIN
SET @output = 'Starting backup on ' + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) = 'CHECKDB'
BEGIN
SET @output = 'Starting CheckDB on ' + convert(varchar(25),getdate(),100)
END
IF UPPER(@optype) IN ('REINDEX','REORG')
BEGIN
SET @output = 'Starting Reindex on ' + convert(varchar(25),getdate(),100)
END
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
/************************
BACKUP ACTIONS
************************/
IF UPPER(@optype) = 'CHECKDB' GOTO CHECK_DB
IF UPPER(@optype) IN ('REINDEX','REORG') GOTO REINDEX
-- if @delfirst = 1 we need to delete prior backups that qualify
IF @delfirst = 1 GOTO DELFIRST
-- this label is so that we can return here after deleting files if @delfirst = 1
DOBACKUP:
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
-- set backup start time
SET @start = GETDATE()
-- write to text report
IF @report = 1
BEGIN
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': ' +
CASE WHEN UPPER(@optype) = 'DB' THEN 'Full Backup '
WHEN UPPER(@optype) = 'DIFF' THEN 'Differential Backup '
WHEN UPPER(@optype) = 'LOG' THEN 'Log Backup '
END + 'starting at ' + CONVERT(varchar(25),@start,100)
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
-- backup subfolder
SET @execmd = 'IF NOT EXIST "' + @backupfldrorig + @database + '\" MKDIR "' + @backupfldrorig + @database + '\"'
EXEC master.dbo.xp_cmdshell @execmd,no_output
SET @backupfldr = @backupfldrorig + @database + '\'
SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
/************************
FULL BACKUP
************************/
IF UPPER(@optype) = 'DB'
BEGIN
SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH -- backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Full backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Full database backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
END
/************************
DIFFERENTIAL BACKUP
************************/
IF UPPER(@optype) = 'DIFF'
BEGIN
SET @execmd = N'BACKUP DATABASE [' + @database + '] TO DISK = ''' +
@backupfilename + ''' WITH DIFFERENTIAL' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' , ' + @backupwith) END
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH -- backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Differential backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Database backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Differential database backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
END
/************************
LOG BACKUP
************************/
IF UPPER(@optype) = 'LOG'
BEGIN
SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH -- backup failure
SELECT @err = @@ERROR,@ret = @err
SELECT @errormsg = 'Log backup of database ' + @database + ' failed with error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
SET @output = SPACE(4) + 'Refer to SQL Error Log and NT Event Log for further details'
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
-- backup success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Log backed up to ' + @backupfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
--calculate backup runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Log backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
/************************
VERIFY BACKUP
************************/
IF @verify = 1
BEGIN
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @backupfilename = @backupfldrorig + @database + '\' + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
SET @start = GETDATE()
-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Verify Backup File...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SET @execmd = N'RESTORE VERIFYONLY FROM DISK = ''' + @backupfilename + '''' +
CASE WHEN @verifywith IS NULL THEN '' ELSE (' WITH ' + @verifywith) END
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH
SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'Verify of ' + @backupfilename + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
-- verify success
SET @finish = GETDATE()
SET @output = SPACE(4) + 'Backup file ' + @backupfilename + ' verified'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
--calculate verify runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Verify backup completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
END
/************************
DELETE OLD FILES
************************/
-- we have already deleted files so skip to the end
IF @delfirst = 1 GOTO CLEANUP
-- this label is so that we can delete files prior to backup if @delfirst = 1
DELFIRST:
/************************
DELETE OLD BACKUPS
************************/
SET @datepart = CASE
WHEN UPPER(@dbretainunit) = 'MINUTES' THEN N'mi'
WHEN UPPER(@dbretainunit) = 'HOURS' THEN N'hh'
WHEN UPPER(@dbretainunit) = 'DAYS' THEN N'dd'
WHEN UPPER(@dbretainunit) = 'WEEKS' THEN N'ww'
WHEN UPPER(@dbretainunit) = 'MONTHS' THEN N'yy'
END
IF @debug = 1 PRINT '@datepart for backups = ' + @datepart
-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Backup Files...'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
SET @backupfldr = + @backupfldrorig + @database + '\'
SELECT @backupfilename = @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_'
END + @jobstart +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END
-- load files in @backupfldr
IF @checkattrib = 1
SET @cmd = 'dir /B /A-D-A /OD "' + @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
ELSE
SET @cmd = 'dir /B /A-D /OD "' + @backupfldr + REPLACE(@database,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_' END + '*' +
CASE WHEN UPPER(@optype) = 'LOG' THEN '.TRN' ELSE '.BAK' END + '"'
IF @debug = 1 PRINT '@cmd = ' + @cmd
DELETE #files
INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL or filename = ISNULL(REPLACE(@backupfilename,@backupfldr,''),'nothing')
IF @debug = 1 SELECT * FROM #files
-- get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX('%File Not Found%',filename) = 0
AND PATINDEX('%The system cannot find%',filename) = 0
-- remove files that don't meet retention criteria if there are any files that match pattern
IF UPPER(@dbretainunit) <> 'COPIES'
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@dbretainval as nvarchar(10)) + N',' +
'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''
IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE -- number of copies not date based (include current backup that's not in #files)
BEGIN
IF @filecount>0
BEGIN
IF @dbretainval>1
BEGIN
SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST((@dbretainval-1) as nvarchar(10)) +
N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'
IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd
END
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
IF @debug = 1 PRINT '@delcount = ' + STR(@delcount)
-- if there are any matching files
IF @filecount>0
BEGIN
-- are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = 'DEL /Q "' + @backupfldr + @delfilename + '"'
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
-- log failure to delete but don't abort procedure
IF @cmdret<>0
BEGIN
SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @backupfldr + @delfilename + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SELECT @delbkflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
ELSE
BEGIN
SET @output = SPACE(4) + 'Deleted file ' + @backupfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
END
FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END
-- write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
-- clear temporary table and variables
DELETE #files
SET @cmd = ''
SET @delcmd = ''
SET @delfilename = ''
SET @datepart = ''
SET @filecount = 0
SET @delcount = 0
SET @cmdret = 0
SET @stage = @stage + 1
/************************
DELETE OLD REPORTS
************************/
DELREPORTS:
IF @rptretainunit IS NOT NULL
BEGIN
SET @datepart = CASE
WHEN UPPER(@rptretainunit) = 'MINUTES' THEN N'mi'
WHEN UPPER(@rptretainunit) = 'HOURS' THEN N'hh'
WHEN UPPER(@rptretainunit) = 'DAYS' THEN N'dd'
WHEN UPPER(@rptretainunit) = 'WEEKS' THEN N'ww'
WHEN UPPER(@rptretainunit) = 'MONTHS' THEN N'yy'
END
IF @debug = 1 PRINT '@datepart for reports = ' + @datepart
-- write to text report
SET @output = '[' + CAST(@stage as varchar(10)) + '] Delete Old Report Files...'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
-- load files in @reportfldr
SET @cmd = 'dir /B /A-D /OD "' + @reportfldr + REPLACE(@databaseorig,' ','_') +
CASE WHEN UPPER(@optype) = 'DB' THEN '_FullDBBackup_report_'
WHEN UPPER(@optype) = 'DIFF' THEN '_DiffDBBackup_report_'
WHEN UPPER(@optype) = 'REINDEX' THEN '_Reindex_report_'
WHEN UPPER(@optype) = 'CHECKDB' THEN '_CheckDB_report_'
WHEN UPPER(@optype) = 'REORG' THEN '_Reorg_report_'
WHEN UPPER(@optype) = 'LOG' THEN '_LogBackup_report_' END + '*.txt"'
IF @debug = 1 PRINT '@cmd = ' + @cmd
INSERT #files EXEC master.dbo.xp_cmdshell @cmd
DELETE #files WHERE filename IS NULL
IF @debug = 1 SELECT * FROM #files
-- get count of files that match pattern
SELECT @filecount = COUNT(*) from #files
WHERE PATINDEX('%File Not Found%',filename) = 0
AND PATINDEX('%The system cannot find%',filename) = 0
-- remove files that don't meet retention criteria if there are any files that match pattern
IF UPPER(@rptretainunit) <> 'COPIES'
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE DATEADD(' + @datepart + N',' + CAST(@rptretainval as nvarchar(10)) + N',' +
'CONVERT(datetime,(SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),7,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),5,2) +''/''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),1,4) +'' ''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),9,2) +'':''
+ SUBSTRING(SUBSTRING(filename,((LEN(filename)-CHARINDEX(''_'',REVERSE(filename)))+2),12),11,2)),103)) > ''' + CAST(@jobdt as nvarchar(25)) + N''''
IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
ELSE -- number of copies not date based
BEGIN
IF @filecount>0
BEGIN
SET @delcmd = N'DELETE #files WHERE filename IN(SELECT TOP ' + CAST(@rptretainval as nvarchar(10)) +
N' filename FROM #files ORDER BY substring(filename,((len(filename)+2)-charindex(''_'',reverse(filename))),12) DESC)'
IF @debug = 1 PRINT '@delcmd=' + @delcmd
EXEC master.dbo.sp_executesql @delcmd
SELECT @delcount = COUNT(*) from #files
END
ELSE
BEGIN
SELECT @delcount = 0
END
END
IF @debug = 1 PRINT STR(@delcount)
-- if there are any matching files
IF @filecount>0
BEGIN
-- are there any files that need deleting
IF @delcount>0
BEGIN
DECLARE FCUR CURSOR FORWARD_ONLY FOR
SELECT * FROM #files
OPEN FCUR
FETCH NEXT FROM FCUR INTO @delfilename
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd = 'DEL /Q "' + @reportfldr + @delfilename + '"'
EXEC @cmdret = master.dbo.xp_cmdshell @cmd,no_output
-- log failure to delete but don't abort procedure
IF @cmdret<>0
BEGIN
SET @output = SPACE(4) + '*** Error: Failed to delete file ' + @reportfldr + @delfilename + ' ***'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
SELECT @delrptflag = 1 , @cmdret = 0, @delcount = (@delcount-1)
END
BEGIN
SET @output = SPACE(4) + 'Deleted file ' + @reportfldr + @delfilename
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
END
FETCH NEXT FROM FCUR INTO @delfilename
END
CLOSE FCUR
DEALLOCATE FCUR
END
END
-- write to text report
SET @output = SPACE(4) + CAST(@delcount as varchar(10)) + ' file(s) deleted.'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
-- update stage
SET @stage = @stage + 1
END
-- if we got here due to @delfirst = 1 go back and do the backups
IF @delfirst = 1
GOTO DOBACKUP
ELSE
GOTO CLEANUP
/************************
CHECKDB
************************/
CHECK_DB:
IF @optype = 'CHECKDB'
BEGIN
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Check Data and Index Linkage...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
-- set backup start time
SET @start = GETDATE()
SET @execmd = N'DBCC CHECKDB([' + @database + N']) WITH NO_INFOMSGS'
IF @debug = 1 PRINT 'DBCC Command : ' + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH
SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'CheckDB of ' + @database + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE dcur
DEALLOCATE dcur
GOTO CLEANUP
END CATCH
SET @finish = GETDATE()
--calculate checkdb runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'CheckDB completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
-- delete reports
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
GOTO DELREPORTS
END
/************************
REINDEX/REORG
************************/
REINDEX:
IF @optype in ('REINDEX','REORG')
BEGIN
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT dbname FROM #databases ORDER BY dbname
OPEN dcur
FETCH NEXT FROM dcur into @database
WHILE @@FETCH_STATUS=0
BEGIN
-- write to text report
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
IF @optype = 'REINDEX'
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Rebuild (using original fillfactor)...'
ELSE
SET @output = '[' + CAST(@stage as varchar(10)) + '] Database ' + @database + ': Index Reorganize...'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
-- set start time
SET @start = GETDATE()
-- all user tables
CREATE TABLE #tables(tablename sysname)
EXEC(N'INSERT #tables(tablename) SELECT DISTINCT(''['' + s.[name] + ''].['' + t.[name] + '']'') FROM [' + @database + N'].sys.tables t ' +
N'JOIN [' + @database + N'].sys.schemas s on t.schema_id=s.schema_id ' +
N'JOIN [' + @database + N'].sys.indexes i on t.object_id=i.object_id ' +
N'WHERE t.is_ms_shipped = 0 AND i.type>0')
DECLARE tcur CURSOR LOCAL FAST_FORWARD
FOR SELECT tablename FROM #tables ORDER BY tablename
OPEN tcur
FETCH NEXT FROM tcur INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN
IF @report = 1
BEGIN
IF @optype = 'REINDEX'
SET @output = SPACE(4) + N'Rebuilding indexes for table ' + @table
ELSE
SET @output = SPACE(4) + N'Reorganizing indexes for table ' + @table
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
IF @optype = 'REINDEX'
SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REBUILD'
ELSE
SET @execmd = N'ALTER INDEX ALL ON [' + @database + N'].' + @table + N' REORGANIZE'
IF @debug = 1 PRINT 'Reindex Command : ' + @execmd
BEGIN TRY
EXEC(@execmd)
END TRY
BEGIN CATCH
SELECT @err = @@ERROR,@ret = @err
SET @errormsg = 'Rebuild of indexes on [' + @database + N'].' + @table + ' failed with Native Error : ' + CAST(@err as varchar(10))
SET @output = SPACE(4) + '*** ' + @errormsg + ' ***'
PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
END
CLOSE tcur
DEALLOCATE tcur
DROP TABLE #tables
GOTO CLEANUP
END CATCH
FETCH NEXT FROM tcur INTO @table
END
CLOSE tcur
DEALLOCATE tcur
SET @finish = GETDATE()
--calculate runtime
SET @runtime = (@finish - @start)
SET @output = SPACE(4) + 'Index maintenance completed in '
+ CAST(DATEPART(hh,@runtime) as varchar(2)) + ' hour(s) '
+ CAST(DATEPART(mi,@runtime) as varchar(2)) + ' min(s) '
+ CAST(DATEPART(ss,@runtime) as varchar(2)) + ' second(s)'
IF @debug = 1 PRINT @output
IF @report = 1
BEGIN
EXEC sp_OAMethod @file,'WriteLine',NULL,''
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
END
DROP TABLE #tables
SET @stage = (@stage + 1)
FETCH NEXT FROM dcur into @database
END
CLOSE dcur
DEALLOCATE dcur
-- delete reports
GOTO DELREPORTS
END
/************************
CLEAN UP
************************/
CLEANUP:
DROP TABLE #files
DROP TABLE #exists
DROP TABLE #databases
-- if we encountered errors deleting old backups return failure
IF @delbkflag<>0
BEGIN
SET @errormsg = 'Expressmaint encountered errors deleting old backup files' + CHAR(13)
+ CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END
-- if we encountered errors deleting old reports return failure
IF (@delrptflag<>0 AND @delbkflag = 0)
BEGIN
SET @errormsg = 'Expressmaint encountered errors deleting old report files' + CHAR(13)
+ CASE WHEN @report = 1 THEN ('Please see ' + @reportfilename + CHAR(13) + ' for further details') ELSE '' END
RAISERROR(@errormsg,16,1)
SET @ret = 1
END
-- if we created a file make sure we write trailer and destroy object
IF @filecrt = 1
BEGIN
-- write final part of report
EXEC sp_OAMethod @file,'WriteLine',NULL,''
SET @output = 'Expressmaint processing finished at ' + CONVERT(varchar(25),GETDATE(),100)
+ ' (Return Code : ' + CAST(@ret as varchar(10)) + ')'
IF @debug = 1 PRINT @output
EXEC sp_OAMethod @file,'WriteLine',NULL,@output
EXEC sp_OAMethod @file,'WriteLine',NULL,''
-- destroy file object
EXEC @hr=sp_OADestroy @file
IF @hr <> 0 EXEC sp_OAGetErrorInfo @file
END
IF @report = 1
BEGIN
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
END
RETURN @ret
Go
USE [master]
GO
/****** Object: StoredProcedure [DBO].[spIndexOptimize] Script Date: 13/02/2017 11:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('[DBO].[spIndexOptimize]') > 0
drop proc [DBO].[spIndexOptimize]
go
CREATE PROCEDURE [DBO].[spIndexOptimize]
@Databases nvarchar(max),
@FragmentationLow nvarchar(max) = NULL,
@FragmentationMedium nvarchar(max) = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh nvarchar(max) = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 int = 5,
@FragmentationLevel2 int = 30,
@PageCountLevel int = 1000,
@SortInTempdb nvarchar(max) = 'N',
@MaxDOP int = NULL,
@FillFactor int = NULL,
@PadIndex nvarchar(max) = NULL,
@LOBCompaction nvarchar(max) = 'Y',
@UpdateStatistics nvarchar(max) = NULL,
@OnlyModifiedStatistics nvarchar(max) = 'N',
@StatisticsSample int = NULL,
@StatisticsResample nvarchar(max) = 'N',
@PartitionLevel nvarchar(max) = 'Y',
@MSShippedObjects nvarchar(max) = 'N',
@Indexes nvarchar(max) = NULL,
@TimeLimit int = NULL,
@Delay int = NULL,
@WaitAtLowPriorityMaxDuration int = NULL,
@WaitAtLowPriorityAbortAfterWait nvarchar(max) = NULL,
@LockTimeout int = NULL,
@LogToTable nvarchar(max) = 'N',
@Execute nvarchar(max) = 'Y'
AS
BEGIN
/**********************************************************
Source: https://ola.hallengren.com
Usage:
exec DBO.spIndexOptimize @Databases = 'ALL_DATABASES'
exec DBO.spIndexOptimize @Databases = 'TDW'
************************************************************/
SET NOCOUNT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
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 @StartTime datetime
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 @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 @CurrentCommand10 nvarchar(max)
DECLARE @CurrentCommand11 nvarchar(max)
DECLARE @CurrentCommand12 nvarchar(max)
DECLARE @CurrentCommand13 nvarchar(max)
DECLARE @CurrentCommand14 nvarchar(max)
DECLARE @CurrentCommandOutput13 int
DECLARE @CurrentCommandOutput14 int
DECLARE @CurrentCommandType13 nvarchar(max)
DECLARE @CurrentCommandType14 nvarchar(max)
DECLARE @CurrentIxID int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName nvarchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName nvarchar(max)
DECLARE @CurrentObjectType nvarchar(max)
DECLARE @CurrentIsMemoryOptimized bit
DECLARE @CurrentIndexID int
DECLARE @CurrentIndexName nvarchar(max)
DECLARE @CurrentIndexType int
DECLARE @CurrentStatisticsID int
DECLARE @CurrentStatisticsName nvarchar(max)
DECLARE @CurrentPartitionID bigint
DECLARE @CurrentPartitionNumber int
DECLARE @CurrentPartitionCount int
DECLARE @CurrentIsPartition bit
DECLARE @CurrentIndexExists bit
DECLARE @CurrentStatisticsExists bit
DECLARE @CurrentIsImageText bit
DECLARE @CurrentIsNewLOB bit
DECLARE @CurrentIsFileStream bit
DECLARE @CurrentIsColumnStore bit
DECLARE @CurrentAllowPageLocks bit
DECLARE @CurrentNoRecompute bit
DECLARE @CurrentStatisticsModified bit
DECLARE @CurrentOnReadOnlyFileGroup bit
DECLARE @CurrentFragmentationLevel float
DECLARE @CurrentPageCount bigint
DECLARE @CurrentFragmentationGroup nvarchar(max)
DECLARE @CurrentAction nvarchar(max)
DECLARE @CurrentMaxDOP int
DECLARE @CurrentUpdateStatistics nvarchar(max)
DECLARE @CurrentComment nvarchar(max)
DECLARE @CurrentExtendedInfo xml
DECLARE @CurrentDelay datetime
DECLARE @tmpDatabases TABLE (ID int IDENTITY,
DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))
DECLARE @tmpIndexesStatistics TABLE (ID int IDENTITY,
SchemaID int,
SchemaName nvarchar(max),
ObjectID int,
ObjectName nvarchar(max),
ObjectType nvarchar(max),
IsMemoryOptimized bit,
IndexID int,
IndexName nvarchar(max),
IndexType int,
StatisticsID int,
StatisticsName nvarchar(max),
PartitionID bigint,
PartitionNumber int,
PartitionCount int,
Selected bit,
Completed bit,
PRIMARY KEY(Selected, Completed, ID))
DECLARE @SelectedDatabases TABLE (DatabaseName nvarchar(max),
DatabaseType nvarchar(max),
Selected bit)
DECLARE @SelectedIndexes TABLE (DatabaseName nvarchar(max),
SchemaName nvarchar(max),
ObjectName nvarchar(max),
IndexName nvarchar(max),
Selected bit)
DECLARE @Actions TABLE ([Action] nvarchar(max))
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
DECLARE @ActionsPreferred TABLE (FragmentationGroup nvarchar(max),
[Priority] int,
[Action] nvarchar(max))
DECLARE @CurrentActionsAllowed TABLE ([Action] 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 @StartTime = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),120),120)
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + 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 + ', @FragmentationLow = ' + ISNULL('''' + REPLACE(@FragmentationLow,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium = ' + ISNULL('''' + REPLACE(@FragmentationMedium,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh = ' + ISNULL('''' + REPLACE(@FragmentationHigh,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @SortInTempdb = ' + ISNULL('''' + REPLACE(@SortInTempdb,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @MaxDOP = ' + ISNULL(CAST(@MaxDOP AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @FillFactor = ' + ISNULL(CAST(@FillFactor AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @PadIndex = ' + ISNULL('''' + REPLACE(@PadIndex,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @LOBCompaction = ' + ISNULL('''' + REPLACE(@LOBCompaction,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @UpdateStatistics = ' + ISNULL('''' + REPLACE(@UpdateStatistics,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @OnlyModifiedStatistics = ' + ISNULL('''' + REPLACE(@OnlyModifiedStatistics,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @StatisticsSample = ' + ISNULL(CAST(@StatisticsSample AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @StatisticsResample = ' + ISNULL('''' + REPLACE(@StatisticsResample,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @PartitionLevel = ' + ISNULL('''' + REPLACE(@PartitionLevel,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @MSShippedObjects = ' + ISNULL('''' + REPLACE(@MSShippedObjects,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @Indexes = ' + ISNULL('''' + REPLACE(@Indexes,'''','''''') + '''','NULL')
SET @StartMessage = @StartMessage + ', @TimeLimit = ' + ISNULL(CAST(@TimeLimit AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @Delay = ' + ISNULL(CAST(@Delay AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @WaitAtLowPriorityMaxDuration = ' + ISNULL(CAST(@WaitAtLowPriorityMaxDuration AS nvarchar),'NULL')
SET @StartMessage = @StartMessage + ', @WaitAtLowPriorityAbortAfterWait = ' + ISNULL('''' + REPLACE(@WaitAtLowPriorityAbortAfterWait,'''','''''') + '''','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] = 'spCommandExecute')
BEGIN
SET @ErrorMessage = 'The stored procedure spCommandExecute is missing. Download https://ola.hallengren.com/scripts/spCommandExecute.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] = 'spCommandExecute' AND (OBJECT_DEFINITION(objects.[object_id]) NOT LIKE '%@LogToTable%' OR OBJECT_DEFINITION(objects.[object_id]) LIKE '%LOCK_TIMEOUT%'))
BEGIN
SET @ErrorMessage = 'The stored procedure spCommandExecute needs to be updated. Download https://ola.hallengren.com/scripts/spCommandExecute.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 spIndexOptimize 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, ', ', ',');
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, Selected) AS
(
SELECT CASE WHEN DatabaseItem IN('ALL_DATABASES','SYSTEM_DATABASES','USER_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,
Selected
FROM Databases2
),
Databases4 (DatabaseName, DatabaseType, Selected) AS
(
SELECT CASE WHEN LEFT(DatabaseItem,1) = '[' AND RIGHT(DatabaseItem,1) = ']' THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem,
DatabaseType,
Selected
FROM Databases3
)
INSERT INTO @SelectedDatabases (DatabaseName, DatabaseType, Selected)
SELECT DatabaseName,
DatabaseType,
Selected
FROM Databases4
OPTION (MAXRECURSION 0)
INSERT INTO @tmpDatabases (DatabaseName, DatabaseType, Selected, Completed)
SELECT [name] AS DatabaseName,
CASE WHEN name IN('master','msdb','model') THEN 'S' ELSE 'U' END AS DatabaseType,
0 AS Selected,
0 AS Completed
FROM sys.databases
WHERE [name] <> 'tempdb'
AND source_database_id IS NULL
ORDER BY [name] ASC
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)
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)
WHERE SelectedDatabases.Selected = 0
IF @Databases IS NULL OR 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 indexes //--
----------------------------------------------------------------------------------------------------
SET @Indexes = REPLACE(@Indexes, ', ', ',');
WITH Indexes1 (StartPosition, EndPosition, IndexItem) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) AS EndPosition,
SUBSTRING(@Indexes, 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, 1), 0), LEN(@Indexes) + 1) - 1) AS IndexItem
WHERE @Indexes IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) AS EndPosition,
SUBSTRING(@Indexes, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @Indexes, EndPosition + 1), 0), LEN(@Indexes) + 1) - EndPosition - 1) AS IndexItem
FROM Indexes1
WHERE EndPosition < LEN(@Indexes) + 1
),
Indexes2 (IndexItem, Selected) AS
(
SELECT CASE WHEN IndexItem LIKE '-%' THEN RIGHT(IndexItem,LEN(IndexItem) - 1) ELSE IndexItem END AS IndexItem,
CASE WHEN IndexItem LIKE '-%' THEN 0 ELSE 1 END AS Selected
FROM Indexes1
),
Indexes3 (IndexItem, Selected) AS
(
SELECT CASE WHEN IndexItem = 'ALL_INDEXES' THEN '%.%.%.%' ELSE IndexItem END AS IndexItem,
Selected
FROM Indexes2
),
Indexes4 (DatabaseName, SchemaName, ObjectName, IndexName, Selected) AS
(
SELECT CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,3) ELSE PARSENAME(IndexItem,4) END AS DatabaseName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,2) ELSE PARSENAME(IndexItem,3) END AS SchemaName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN PARSENAME(IndexItem,1) ELSE PARSENAME(IndexItem,2) END AS ObjectName,
CASE WHEN PARSENAME(IndexItem,4) IS NULL THEN '%' ELSE PARSENAME(IndexItem,1) END AS IndexName,
Selected
FROM Indexes3
)
INSERT INTO @SelectedIndexes (DatabaseName, SchemaName, ObjectName, IndexName, Selected)
SELECT DatabaseName, SchemaName, ObjectName, IndexName, Selected
FROM Indexes4
OPTION (MAXRECURSION 0);
----------------------------------------------------------------------------------------------------
--// Select actions //--
----------------------------------------------------------------------------------------------------
WITH FragmentationLow (StartPosition, EndPosition, [Action]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,
SUBSTRING(@FragmentationLow, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, 1), 0), LEN(@FragmentationLow) + 1) - 1) AS [Action]
WHERE @FragmentationLow IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) AS EndPosition,
SUBSTRING(@FragmentationLow, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationLow, EndPosition + 1), 0), LEN(@FragmentationLow) + 1) - EndPosition - 1) AS [Action]
FROM FragmentationLow
WHERE EndPosition < LEN(@FragmentationLow) + 1
)
INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
SELECT 'Low' AS FragmentationGroup,
ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
[Action]
FROM FragmentationLow
OPTION (MAXRECURSION 0);
WITH FragmentationMedium (StartPosition, EndPosition, [Action]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,
SUBSTRING(@FragmentationMedium, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, 1), 0), LEN(@FragmentationMedium) + 1) - 1) AS [Action]
WHERE @FragmentationMedium IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) AS EndPosition,
SUBSTRING(@FragmentationMedium, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationMedium, EndPosition + 1), 0), LEN(@FragmentationMedium) + 1) - EndPosition - 1) AS [Action]
FROM FragmentationMedium
WHERE EndPosition < LEN(@FragmentationMedium) + 1
)
INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
SELECT 'Medium' AS FragmentationGroup,
ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
[Action]
FROM FragmentationMedium
OPTION (MAXRECURSION 0);
WITH FragmentationHigh (StartPosition, EndPosition, [Action]) AS
(
SELECT 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,
SUBSTRING(@FragmentationHigh, 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, 1), 0), LEN(@FragmentationHigh) + 1) - 1) AS [Action]
WHERE @FragmentationHigh IS NOT NULL
UNION ALL
SELECT CAST(EndPosition AS int) + 1 AS StartPosition,
ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) AS EndPosition,
SUBSTRING(@FragmentationHigh, EndPosition + 1, ISNULL(NULLIF(CHARINDEX(',', @FragmentationHigh, EndPosition + 1), 0), LEN(@FragmentationHigh) + 1) - EndPosition - 1) AS [Action]
FROM FragmentationHigh
WHERE EndPosition < LEN(@FragmentationHigh) + 1
)
INSERT INTO @ActionsPreferred(FragmentationGroup, [Priority], [Action])
SELECT 'High' AS FragmentationGroup,
ROW_NUMBER() OVER(ORDER BY StartPosition ASC) AS [Priority],
[Action]
FROM FragmentationHigh
OPTION (MAXRECURSION 0)
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' AND [Action] NOT IN(SELECT * FROM @Actions))
OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Low' GROUP BY [Action] HAVING COUNT(*) > 1)
BEGIN
SET @ErrorMessage = 'The value for the parameter @FragmentationLow is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' AND [Action] NOT IN(SELECT * FROM @Actions))
OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'Medium' GROUP BY [Action] HAVING COUNT(*) > 1)
BEGIN
SET @ErrorMessage = 'The value for the parameter @FragmentationMedium is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF EXISTS (SELECT [Action] FROM @ActionsPreferred WHERE FragmentationGroup = 'High' AND [Action] NOT IN(SELECT * FROM @Actions))
OR EXISTS(SELECT * FROM @ActionsPreferred WHERE FragmentationGroup = 'High' GROUP BY [Action] HAVING COUNT(*) > 1)
BEGIN
SET @ErrorMessage = 'The value for the parameter @FragmentationHigh 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 the 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 the 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 the parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @SortInTempdb NOT IN('Y','N') OR @SortInTempdb IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @SortInTempdb 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 > (SELECT cpu_count FROM sys.dm_os_sys_info) OR (@MaxDOP > 1 AND SERVERPROPERTY('EngineEdition') NOT IN (3,5))
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 @FillFactor <= 0 OR @FillFactor > 100
BEGIN
SET @ErrorMessage = 'The value for the parameter @FillFactor is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @PadIndex NOT IN('Y','N')
BEGIN
SET @ErrorMessage = 'The value for the parameter @PadIndex is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @LOBCompaction NOT IN('Y','N') OR @LOBCompaction IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @LOBCompaction is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @UpdateStatistics NOT IN('ALL','COLUMNS','INDEX')
BEGIN
SET @ErrorMessage = 'The value for the parameter @UpdateStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @OnlyModifiedStatistics NOT IN('Y','N') OR @OnlyModifiedStatistics IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @OnlyModifiedStatistics is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @StatisticsSample <= 0 OR @StatisticsSample > 100
BEGIN
SET @ErrorMessage = 'The value for the parameter @StatisticsSample is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @StatisticsResample NOT IN('Y','N') OR @StatisticsResample IS NULL OR (@StatisticsResample = 'Y' AND @StatisticsSample IS NOT NULL)
BEGIN
SET @ErrorMessage = 'The value for the parameter @StatisticsResample is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @PartitionLevel NOT IN('Y','N') OR @PartitionLevel IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @PartitionLevel is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @MSShippedObjects NOT IN('Y','N') OR @MSShippedObjects IS NULL
BEGIN
SET @ErrorMessage = 'The value for the parameter @MSShippedObjects is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF EXISTS(SELECT * FROM @SelectedIndexes WHERE DatabaseName IS NULL OR SchemaName IS NULL OR ObjectName IS NULL OR IndexName IS NULL) OR (@Indexes IS NOT NULL AND NOT EXISTS(SELECT * FROM @SelectedIndexes))
BEGIN
SET @ErrorMessage = 'The value for the parameter @Indexes is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @TimeLimit < 0
BEGIN
SET @ErrorMessage = 'The value for the parameter @TimeLimit is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @Delay < 0
BEGIN
SET @ErrorMessage = 'The value for the parameter @Delay is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @WaitAtLowPriorityMaxDuration < 0 OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityMaxDuration IS NOT NULL AND @WaitAtLowPriorityAbortAfterWait IS NULL) OR (@WaitAtLowPriorityMaxDuration IS NULL AND @WaitAtLowPriorityAbortAfterWait IS NOT NULL)
BEGIN
SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityMaxDuration is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @WaitAtLowPriorityAbortAfterWait NOT IN('NONE','SELF','BLOCKERS') OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @Version < 12) OR (@WaitAtLowPriorityAbortAfterWait IS NOT NULL AND @WaitAtLowPriorityMaxDuration IS NULL) OR (@WaitAtLowPriorityAbortAfterWait IS NULL AND @WaitAtLowPriorityMaxDuration IS NOT NULL)
BEGIN
SET @ErrorMessage = 'The value for the parameter @WaitAtLowPriorityAbortAfterWait 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-index-and-statistics-maintenance.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
-- 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: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') AS nvarchar) + 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 DATABASEPROPERTYEX(@CurrentDatabaseName,'Updateability') = 'READ_WRITE'
BEGIN
-- Select indexes in the current database
IF (EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IS NOT NULL) AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
BEGIN
SET @CurrentCommand01 = 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed FROM ('
IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX')
BEGIN
SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE 'NULL' END + ' AS IsMemoryOptimized, indexes.index_id AS IndexID, indexes.[name] AS IndexName, indexes.[type] AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName'
IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', partitions.partition_id AS PartitionID, partitions.partition_number AS PartitionNumber, IndexPartitions.partition_count AS PartitionCount'
IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ', NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount'
SET @CurrentCommand01 = @CurrentCommand01 + ', 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] 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] LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats ON indexes.[object_id] = stats.[object_id] AND indexes.[index_id] = stats.[stats_id]'
IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id LEFT OUTER JOIN (SELECT partitions.[object_id], partitions.index_id, COUNT(*) AS partition_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions GROUP BY partitions.[object_id], partitions.index_id) IndexPartitions ON partitions.[object_id] = IndexPartitions.[object_id] AND partitions.[index_id] = IndexPartitions.[index_id]'
IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id] AND partitions.partition_id = dm_db_partition_stats.partition_id'
IF @PartitionLevel = 'N' SET @CurrentCommand01 = @CurrentCommand01 + ' LEFT OUTER JOIN (SELECT dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id], SUM(dm_db_partition_stats.in_row_data_page_count) AS in_row_data_page_count FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.dm_db_partition_stats dm_db_partition_stats GROUP BY dm_db_partition_stats.[object_id], dm_db_partition_stats.[index_id]) dm_db_partition_stats ON indexes.[object_id] = dm_db_partition_stats.[object_id] AND indexes.[index_id] = dm_db_partition_stats.[index_id]'
SET @CurrentCommand01 = @CurrentCommand01 + ' WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0'
IF (@UpdateStatistics NOT IN('ALL','INDEX') OR @UpdateStatistics IS NULL) AND @PageCountLevel > 0 SET @CurrentCommand01 = @CurrentCommand01 + ' AND (dm_db_partition_stats.in_row_data_page_count >= @ParamPageCountLevel OR dm_db_partition_stats.in_row_data_page_count IS NULL)'
IF NOT EXISTS(SELECT * FROM @ActionsPreferred) SET @CurrentCommand01 = @CurrentCommand01 + ' AND stats.stats_id IS NOT NULL'
END
IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ' UNION '
IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + 'SELECT schemas.[schema_id] AS SchemaID, schemas.[name] AS SchemaName, objects.[object_id] AS ObjectID, objects.[name] AS ObjectName, RTRIM(objects.[type]) AS ObjectType, ' + CASE WHEN @Version >= 12 THEN 'tables.is_memory_optimized' ELSE 'NULL' END + ' AS IsMemoryOptimized, NULL AS IndexID, NULL AS IndexName, NULL AS IndexType, stats.stats_id AS StatisticsID, stats.name AS StatisticsName, NULL AS PartitionID, NULL AS PartitionNumber, NULL AS PartitionCount, 0 AS Selected, 0 AS Completed FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] 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'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND NOT EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = stats.[object_id] AND indexes.index_id = stats.stats_id)'
SET @CurrentCommand01 = @CurrentCommand01 + ') IndexesStatistics ORDER BY SchemaName ASC, ObjectName ASC'
IF (EXISTS(SELECT * FROM @ActionsPreferred) AND @UpdateStatistics = 'COLUMNS') OR @UpdateStatistics = 'ALL' SET @CurrentCommand01 = @CurrentCommand01 + ', CASE WHEN IndexType IS NULL THEN 1 ELSE 0 END ASC'
IF EXISTS(SELECT * FROM @ActionsPreferred) OR @UpdateStatistics IN('ALL','INDEX') SET @CurrentCommand01 = @CurrentCommand01 + ', IndexType ASC, IndexName ASC'
IF @UpdateStatistics IN('ALL','COLUMNS') SET @CurrentCommand01 = @CurrentCommand01 + ', StatisticsName ASC'
IF @PartitionLevel = 'Y' SET @CurrentCommand01 = @CurrentCommand01 + ', PartitionNumber ASC'
INSERT INTO @tmpIndexesStatistics (SchemaID, SchemaName, ObjectID, ObjectName, ObjectType, IsMemoryOptimized, IndexID, IndexName, IndexType, StatisticsID, StatisticsName, PartitionID, PartitionNumber, PartitionCount, Selected, Completed)
EXECUTE sp_executesql @statement = @CurrentCommand01, @params = N'@ParamPageCountLevel int', @ParamPageCountLevel = @PageCountLevel
SET @Error = @@ERROR
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
END
END
IF @Indexes IS NULL
BEGIN
UPDATE tmpIndexesStatistics
SET tmpIndexesStatistics.Selected = 1
FROM @tmpIndexesStatistics tmpIndexesStatistics
END
ELSE
BEGIN
UPDATE tmpIndexesStatistics
SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected
FROM @tmpIndexesStatistics tmpIndexesStatistics
INNER JOIN @SelectedIndexes SelectedIndexes
ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')
WHERE SelectedIndexes.Selected = 1
UPDATE tmpIndexesStatistics
SET tmpIndexesStatistics.Selected = SelectedIndexes.Selected
FROM @tmpIndexesStatistics tmpIndexesStatistics
INNER JOIN @SelectedIndexes SelectedIndexes
ON @CurrentDatabaseName LIKE REPLACE(SelectedIndexes.DatabaseName,'_','[_]') AND tmpIndexesStatistics.SchemaName LIKE REPLACE(SelectedIndexes.SchemaName,'_','[_]') AND tmpIndexesStatistics.ObjectName LIKE REPLACE(SelectedIndexes.ObjectName,'_','[_]') AND COALESCE(tmpIndexesStatistics.IndexName,tmpIndexesStatistics.StatisticsName) LIKE REPLACE(SelectedIndexes.IndexName,'_','[_]')
WHERE SelectedIndexes.Selected = 0
END
WHILE EXISTS (SELECT * FROM @tmpIndexesStatistics WHERE Selected = 1 AND Completed = 0 AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL))
BEGIN
SELECT TOP 1 @CurrentIxID = ID,
@CurrentSchemaID = SchemaID,
@CurrentSchemaName = SchemaName,
@CurrentObjectID = ObjectID,
@CurrentObjectName = ObjectName,
@CurrentObjectType = ObjectType,
@CurrentIsMemoryOptimized = IsMemoryOptimized,
@CurrentIndexID = IndexID,
@CurrentIndexName = IndexName,
@CurrentIndexType = IndexType,
@CurrentStatisticsID = StatisticsID,
@CurrentStatisticsName = StatisticsName,
@CurrentPartitionID = PartitionID,
@CurrentPartitionNumber = PartitionNumber,
@CurrentPartitionCount = PartitionCount
FROM @tmpIndexesStatistics
WHERE Selected = 1
AND Completed = 0
ORDER BY ID ASC
-- Is the index a partition?
IF @CurrentPartitionNumber IS NULL OR @CurrentPartitionCount = 1 BEGIN SET @CurrentIsPartition = 0 END ELSE BEGIN SET @CurrentIsPartition = 1 END
-- Does the index exist?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentCommand02 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand02 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
IF @CurrentIsPartition = 0 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType) BEGIN SET @ParamIndexExists = 1 END'
IF @CurrentIsPartition = 1 SET @CurrentCommand02 = @CurrentCommand02 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON indexes.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.partitions partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.index_id = partitions.index_id WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND indexes.[type] IN(1,2,3,4,5,6,7) AND indexes.is_disabled = 0 AND indexes.is_hypothetical = 0 AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND indexes.index_id = @ParamIndexID AND indexes.[name] = @ParamIndexName AND indexes.[type] = @ParamIndexType AND partitions.partition_id = @ParamPartitionID AND partitions.partition_number = @ParamPartitionNumber) BEGIN SET @ParamIndexExists = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand02, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamIndexID int, @ParamIndexName sysname, @ParamIndexType int, @ParamPartitionID bigint, @ParamPartitionNumber int, @ParamIndexExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamIndexID = @CurrentIndexID, @ParamIndexName = @CurrentIndexName, @ParamIndexType = @CurrentIndexType, @ParamPartitionID = @CurrentPartitionID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamIndexExists = @CurrentIndexExists OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentIndexExists IS NULL SET @CurrentIndexExists = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index exists.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
IF @CurrentIndexExists = 0 GOTO NoAction
END
-- Does the statistics exist?
IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL
BEGIN
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.stats stats INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.objects objects ON stats.[object_id] = objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] IN(''U'',''V'')' + CASE WHEN @MSShippedObjects = 'N' THEN ' AND objects.is_ms_shipped = 0' ELSE '' END + ' AND schemas.[schema_id] = @ParamSchemaID AND schemas.[name] = @ParamSchemaName AND objects.[object_id] = @ParamObjectID AND objects.[name] = @ParamObjectName AND objects.[type] = @ParamObjectType AND stats.stats_id = @ParamStatisticsID AND stats.[name] = @ParamStatisticsName) BEGIN SET @ParamStatisticsExists = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand03, @params = N'@ParamSchemaID int, @ParamSchemaName sysname, @ParamObjectID int, @ParamObjectName sysname, @ParamObjectType sysname, @ParamStatisticsID int, @ParamStatisticsName sysname, @ParamStatisticsExists bit OUTPUT', @ParamSchemaID = @CurrentSchemaID, @ParamSchemaName = @CurrentSchemaName, @ParamObjectID = @CurrentObjectID, @ParamObjectName = @CurrentObjectName, @ParamObjectType = @CurrentObjectType, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsName = @CurrentStatisticsName, @ParamStatisticsExists = @CurrentStatisticsExists OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentStatisticsExists IS NULL SET @CurrentStatisticsExists = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the statistics exists.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
IF @CurrentStatisticsExists = 0 GOTO NoAction
END
-- Is one of the columns in the index an image, text or ntext data type?
IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType = 1 AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentCommand04 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand04 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand04 = @CurrentCommand04 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id WHERE columns.[object_id] = @ParamObjectID AND types.name IN(''image'',''text'',''ntext'')) BEGIN SET @ParamIsImageText = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand04, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsImageText bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsImageText = @CurrentIsImageText OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentIsImageText IS NULL SET @CurrentIsImageText = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any image, text, or ntext data types.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is one of the columns in the index an xml, varchar(max), nvarchar(max), varbinary(max) or large CLR data type?
IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN(1,2) AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentCommand05 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand05 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
IF @CurrentIndexType = 1 SET @CurrentCommand05 = @CurrentCommand05 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE columns.[object_id] = @ParamObjectID AND (types.name IN(''xml'') OR (types.name IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'
IF @CurrentIndexType = 2 SET @CurrentCommand05 = @CurrentCommand05 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.index_columns index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns ON index_columns.[object_id] = columns.[object_id] AND index_columns.column_id = columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.types types ON columns.system_type_id = types.user_type_id OR (columns.user_type_id = types.user_type_id AND types.is_assembly_type = 1) WHERE index_columns.[object_id] = @ParamObjectID AND index_columns.index_id = @ParamIndexID AND (types.[name] IN(''xml'') OR (types.[name] IN(''varchar'',''nvarchar'',''varbinary'') AND columns.max_length = -1) OR (types.is_assembly_type = 1 AND columns.max_length = -1))) BEGIN SET @ParamIsNewLOB = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand05, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsNewLOB bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsNewLOB = @CurrentIsNewLOB OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentIsNewLOB IS NULL SET @CurrentIsNewLOB = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any xml, varchar(max), nvarchar(max), varbinary(max), or large CLR data types.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is one of the columns in the index a file stream column?
IF @CurrentIndexID IS NOT NULL AND @CurrentIndexType = 1 AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentCommand06 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand06 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand06 = @CurrentCommand06 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.columns columns WHERE columns.[object_id] = @ParamObjectID AND columns.is_filestream = 1) BEGIN SET @ParamIsFileStream = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand06, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamIsFileStream bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamIsFileStream = @CurrentIsFileStream OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentIsFileStream IS NULL SET @CurrentIsFileStream = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index contains any file stream columns.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is there a columnstore index on the table?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred) AND @Version >= 11
BEGIN
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.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND [type] IN(5,6)) BEGIN SET @ParamIsColumnStore = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand07, @params = N'@ParamObjectID int, @ParamIsColumnStore bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIsColumnStore = @CurrentIsColumnStore OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentIsColumnStore IS NULL SET @CurrentIsColumnStore = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if there is a columnstore index on the table.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is Allow_Page_Locks set to On?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentCommand08 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand08 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand08 = @CurrentCommand08 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes WHERE indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID AND indexes.[allow_page_locks] = 1) BEGIN SET @ParamAllowPageLocks = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand08, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamAllowPageLocks bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamAllowPageLocks = @CurrentAllowPageLocks OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentAllowPageLocks IS NULL SET @CurrentAllowPageLocks = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if page locking is enabled on the index.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is No_Recompute set to On?
IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL
BEGIN
SET @CurrentCommand09 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand09 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand09 = @CurrentCommand09 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.stats stats WHERE stats.[object_id] = @ParamObjectID AND stats.[stats_id] = @ParamStatisticsID AND stats.[no_recompute] = 1) BEGIN SET @ParamNoRecompute = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand09, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamNoRecompute bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamNoRecompute = @CurrentNoRecompute OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentNoRecompute IS NULL SET @CurrentNoRecompute = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if automatic statistics update is enabled.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Has the data in the statistics been modified since the statistics was last updated?
IF @CurrentStatisticsID IS NOT NULL AND @UpdateStatistics IS NOT NULL AND @OnlyModifiedStatistics = 'Y'
BEGIN
SET @CurrentCommand10 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand10 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
IF (@Version >= 10.504000 AND @Version < 11) OR @Version >= 11.03000
BEGIN
SET @CurrentCommand10 = @CurrentCommand10 + 'USE ' + QUOTENAME(@CurrentDatabaseName) + '; IF EXISTS(SELECT * FROM sys.dm_db_stats_properties (@ParamObjectID, @ParamStatisticsID) WHERE modification_counter > 0) BEGIN SET @ParamStatisticsModified = 1 END'
END
ELSE
BEGIN
SET @CurrentCommand10 = @CurrentCommand10 + 'IF EXISTS(SELECT * FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.sysindexes sysindexes WHERE sysindexes.[id] = @ParamObjectID AND sysindexes.[indid] = @ParamStatisticsID AND sysindexes.[rowmodctr] <> 0) BEGIN SET @ParamStatisticsModified = 1 END'
END
EXECUTE sp_executesql @statement = @CurrentCommand10, @params = N'@ParamObjectID int, @ParamStatisticsID int, @ParamStatisticsModified bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamStatisticsID = @CurrentStatisticsID, @ParamStatisticsModified = @CurrentStatisticsModified OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentStatisticsModified IS NULL SET @CurrentStatisticsModified = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The statistics ' + QUOTENAME(@CurrentStatisticsName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if any rows has been modified since the most recent statistics update.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is the index on a read-only filegroup?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentCommand11 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand11 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand11 = @CurrentCommand11 + 'IF EXISTS(SELECT * FROM (SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.destination_data_spaces destination_data_spaces ON indexes.data_space_id = destination_data_spaces.partition_scheme_id INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON destination_data_spaces.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'
IF @CurrentIsPartition = 1 SET @CurrentCommand11 = @CurrentCommand11 + ' AND destination_data_spaces.destination_id = @ParamPartitionNumber'
SET @CurrentCommand11 = @CurrentCommand11 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.indexes indexes INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON indexes.data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND indexes.[object_id] = @ParamObjectID AND indexes.[index_id] = @ParamIndexID'
IF @CurrentIndexType = 1 SET @CurrentCommand11 = @CurrentCommand11 + ' UNION SELECT filegroups.data_space_id FROM ' + QUOTENAME(@CurrentDatabaseName) + '.sys.tables tables INNER JOIN ' + QUOTENAME(@CurrentDatabaseName) + '.sys.filegroups filegroups ON tables.lob_data_space_id = filegroups.data_space_id WHERE filegroups.is_read_only = 1 AND tables.[object_id] = @ParamObjectID'
SET @CurrentCommand11 = @CurrentCommand11 + ') ReadOnlyFileGroups) BEGIN SET @ParamOnReadOnlyFileGroup = 1 END'
EXECUTE sp_executesql @statement = @CurrentCommand11, @params = N'@ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamOnReadOnlyFileGroup bit OUTPUT', @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamOnReadOnlyFileGroup = @CurrentOnReadOnlyFileGroup OUTPUT
SET @Error = @@ERROR
IF @Error = 0 AND @CurrentOnReadOnlyFileGroup IS NULL SET @CurrentOnReadOnlyFileGroup = 0
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. It could not be checked if the index is on a read-only filegroup.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Is the index fragmented?
IF @CurrentIndexID IS NOT NULL
AND @CurrentOnReadOnlyFileGroup = 0
AND EXISTS(SELECT * FROM @ActionsPreferred)
AND (EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3) OR @PageCountLevel > 0)
BEGIN
SET @CurrentCommand12 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand12 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand12 = @CurrentCommand12 + 'SELECT @ParamFragmentationLevel = MAX(avg_fragmentation_in_percent), @ParamPageCount = SUM(page_count) FROM sys.dm_db_index_physical_stats(@ParamDatabaseID, @ParamObjectID, @ParamIndexID, @ParamPartitionNumber, ''LIMITED'') WHERE alloc_unit_type_desc = ''IN_ROW_DATA'' AND index_level = 0'
EXECUTE sp_executesql @statement = @CurrentCommand12, @params = N'@ParamDatabaseID int, @ParamObjectID int, @ParamIndexID int, @ParamPartitionNumber int, @ParamFragmentationLevel float OUTPUT, @ParamPageCount bigint OUTPUT', @ParamDatabaseID = @CurrentDatabaseID, @ParamObjectID = @CurrentObjectID, @ParamIndexID = @CurrentIndexID, @ParamPartitionNumber = @CurrentPartitionNumber, @ParamFragmentationLevel = @CurrentFragmentationLevel OUTPUT, @ParamPageCount = @CurrentPageCount OUTPUT
SET @Error = @@ERROR
IF @Error = 1222
BEGIN
SET @ErrorMessage = 'The index ' + QUOTENAME(@CurrentIndexName) + ' on the object ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' is locked. The size and fragmentation of the index could not be checked.' + CHAR(13) + CHAR(10) + ' '
SET @ErrorMessage = REPLACE(@ErrorMessage,'%','%%')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END
IF @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO NoAction
END
END
-- Select fragmentation group
IF @CurrentIndexID IS NOT NULL AND @CurrentOnReadOnlyFileGroup = 0 AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
SET @CurrentFragmentationGroup = CASE
WHEN @CurrentFragmentationLevel >= @FragmentationLevel2 THEN 'High'
WHEN @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 THEN 'Medium'
WHEN @CurrentFragmentationLevel < @FragmentationLevel1 THEN 'Low'
END
END
-- Which actions are allowed?
IF @CurrentIndexID IS NOT NULL AND EXISTS(SELECT * FROM @ActionsPreferred)
BEGIN
IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) AND (@CurrentAllowPageLocks = 1 OR @CurrentIndexType = 5)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REORGANIZE')
END
IF @CurrentOnReadOnlyFileGroup = 0 AND @CurrentIndexType IN (1,2,3,4,5) AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REBUILD_OFFLINE')
END
IF @CurrentOnReadOnlyFileGroup = 0
AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL)
AND (@CurrentIsPartition = 0 OR @Version >= 12)
AND ((@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsNewLOB = 0)
OR (@CurrentIndexType = 2 AND @CurrentIsNewLOB = 0)
OR (@CurrentIndexType = 1 AND @CurrentIsImageText = 0 AND @CurrentIsFileStream = 0 AND @Version >= 11)
OR (@CurrentIndexType = 2 AND @Version >= 11))
AND (@CurrentIsColumnStore = 0 OR @Version < 11)
AND SERVERPROPERTY('EngineEdition') IN (3,5)
BEGIN
INSERT INTO @CurrentActionsAllowed ([Action])
VALUES ('INDEX_REBUILD_ONLINE')
END
END
-- Decide action
IF @CurrentIndexID IS NOT NULL
AND EXISTS(SELECT * FROM @ActionsPreferred)
AND (@CurrentPageCount >= @PageCountLevel OR @PageCountLevel = 0)
BEGIN
IF EXISTS(SELECT [Priority], [Action], COUNT(*) FROM @ActionsPreferred GROUP BY [Priority], [Action] HAVING COUNT(*) <> 3)
BEGIN
SELECT @CurrentAction = [Action]
FROM @ActionsPreferred
WHERE FragmentationGroup = @CurrentFragmentationGroup
AND [Priority] = (SELECT MIN([Priority])
FROM @ActionsPreferred
WHERE FragmentationGroup = @CurrentFragmentationGroup
AND [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))
END
ELSE
BEGIN
SELECT @CurrentAction = [Action]
FROM @ActionsPreferred
WHERE [Priority] = (SELECT MIN([Priority])
FROM @ActionsPreferred
WHERE [Action] IN (SELECT [Action] FROM @CurrentActionsAllowed))
END
END
-- Workaround for limitation in SQL Server, https://support.microsoft.com/kb/2292737
IF @CurrentIndexID IS NOT NULL
BEGIN
SET @CurrentMaxDOP = @MaxDOP
IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @CurrentAllowPageLocks = 0
BEGIN
SET @CurrentMaxDOP = 1
END
END
-- Update statistics?
IF @CurrentStatisticsID IS NOT NULL
AND ((@UpdateStatistics = 'ALL' AND (@CurrentIndexType IN (1,2,3,4,7) OR @CurrentIndexID IS NULL)) OR (@UpdateStatistics = 'INDEX' AND @CurrentIndexID IS NOT NULL AND @CurrentIndexType IN (1,2,3,4,7)) OR (@UpdateStatistics = 'COLUMNS' AND @CurrentIndexID IS NULL))
AND (@CurrentStatisticsModified = 1 OR @OnlyModifiedStatistics = 'N' OR @CurrentIsMemoryOptimized = 1)
AND ((@CurrentIsPartition = 0 AND (@CurrentAction NOT IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE') OR @CurrentAction IS NULL)) OR (@CurrentIsPartition = 1 AND @CurrentPartitionNumber = @CurrentPartitionCount))
BEGIN
SET @CurrentUpdateStatistics = 'Y'
END
ELSE
BEGIN
SET @CurrentUpdateStatistics = 'N'
END
-- Create comment
IF @CurrentIndexID IS NOT NULL
BEGIN
SET @CurrentComment = 'ObjectType: ' + CASE WHEN @CurrentObjectType = 'U' THEN 'Table' WHEN @CurrentObjectType = 'V' THEN 'View' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'IndexType: ' + CASE WHEN @CurrentIndexType = 1 THEN 'Clustered' WHEN @CurrentIndexType = 2 THEN 'NonClustered' WHEN @CurrentIndexType = 3 THEN 'XML' WHEN @CurrentIndexType = 4 THEN 'Spatial' WHEN @CurrentIndexType = 5 THEN 'Clustered Columnstore' WHEN @CurrentIndexType = 6 THEN 'NonClustered Columnstore' WHEN @CurrentIndexType = 7 THEN 'NonClustered Hash' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'ImageText: ' + CASE WHEN @CurrentIsImageText = 1 THEN 'Yes' WHEN @CurrentIsImageText = 0 THEN 'No' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'NewLOB: ' + CASE WHEN @CurrentIsNewLOB = 1 THEN 'Yes' WHEN @CurrentIsNewLOB = 0 THEN 'No' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'FileStream: ' + CASE WHEN @CurrentIsFileStream = 1 THEN 'Yes' WHEN @CurrentIsFileStream = 0 THEN 'No' ELSE 'N/A' END + ', '
IF @Version >= 11 SET @CurrentComment = @CurrentComment + 'ColumnStore: ' + CASE WHEN @CurrentIsColumnStore = 1 THEN 'Yes' WHEN @CurrentIsColumnStore = 0 THEN 'No' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'AllowPageLocks: ' + CASE WHEN @CurrentAllowPageLocks = 1 THEN 'Yes' WHEN @CurrentAllowPageLocks = 0 THEN 'No' ELSE 'N/A' END + ', '
SET @CurrentComment = @CurrentComment + 'PageCount: ' + ISNULL(CAST(@CurrentPageCount AS nvarchar),'N/A') + ', '
SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + ISNULL(CAST(@CurrentFragmentationLevel AS nvarchar),'N/A')
END
IF @CurrentIndexID IS NOT NULL AND (@CurrentPageCount IS NOT NULL OR @CurrentFragmentationLevel IS NOT NULL)
BEGIN
SET @CurrentExtendedInfo = (SELECT *
FROM (SELECT CAST(@CurrentPageCount AS nvarchar) AS [PageCount],
CAST(@CurrentFragmentationLevel AS nvarchar) AS Fragmentation
) ExtendedInfo FOR XML AUTO, ELEMENTS)
END
IF @CurrentIndexID IS NOT NULL AND @CurrentAction IS NOT NULL AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
BEGIN
SET @CurrentCommandType13 = 'ALTER_INDEX'
SET @CurrentCommand13 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand13 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand13 = @CurrentCommand13 + 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName)
IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE')
BEGIN
SET @CurrentCommand13 = @CurrentCommand13 + ' REBUILD'
IF @CurrentIsPartition = 1 SET @CurrentCommand13 = @CurrentCommand13 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)
SET @CurrentCommand13 = @CurrentCommand13 + ' WITH ('
IF @SortInTempdb = 'Y' AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + 'SORT_IN_TEMPDB = ON'
IF @SortInTempdb = 'N' AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + 'SORT_IN_TEMPDB = OFF'
IF @CurrentIndexType IN(1,2,3,4) AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + ', '
IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + 'ONLINE = ON'
IF @CurrentAction = 'INDEX_REBUILD_ONLINE' AND @WaitAtLowPriorityMaxDuration IS NOT NULL SET @CurrentCommand13 = @CurrentCommand13 + ' (WAIT_AT_LOW_PRIORITY (MAX_DURATION = ' + CAST(@WaitAtLowPriorityMaxDuration AS nvarchar) + ', ABORT_AFTER_WAIT = ' + UPPER(@WaitAtLowPriorityAbortAfterWait) + '))'
IF @CurrentAction = 'INDEX_REBUILD_OFFLINE' AND (@CurrentIsPartition = 0 OR @Version >= 12) SET @CurrentCommand13 = @CurrentCommand13 + 'ONLINE = OFF'
IF @CurrentMaxDOP IS NOT NULL SET @CurrentCommand13 = @CurrentCommand13 + ', MAXDOP = ' + CAST(@CurrentMaxDOP AS nvarchar)
IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)
IF @PadIndex = 'Y' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', PAD_INDEX = ON'
IF @PadIndex = 'N' AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', PAD_INDEX = OFF'
SET @CurrentCommand13 = @CurrentCommand13 + ')'
END
IF @CurrentAction IN('INDEX_REORGANIZE')
BEGIN
SET @CurrentCommand13 = @CurrentCommand13 + ' REORGANIZE'
IF @CurrentIsPartition = 1 SET @CurrentCommand13 = @CurrentCommand13 + ' PARTITION = ' + CAST(@CurrentPartitionNumber AS nvarchar)
SET @CurrentCommand13 = @CurrentCommand13 + ' WITH ('
IF @LOBCompaction = 'Y' SET @CurrentCommand13 = @CurrentCommand13 + 'LOB_COMPACTION = ON'
IF @LOBCompaction = 'N' SET @CurrentCommand13 = @CurrentCommand13 + 'LOB_COMPACTION = OFF'
SET @CurrentCommand13 = @CurrentCommand13 + ')'
END
EXECUTE @CurrentCommandOutput13 = [DBO].[spCommandExecute] @Command = @CurrentCommand13, @CommandType = @CurrentCommandType13, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput13 = @Error
IF @CurrentCommandOutput13 <> 0 SET @ReturnCode = @CurrentCommandOutput13
IF @Delay > 0
BEGIN
SET @CurrentDelay = DATEADD(ss,@Delay,'1900-01-01')
WAITFOR DELAY @CurrentDelay
END
END
IF @CurrentStatisticsID IS NOT NULL AND @CurrentUpdateStatistics = 'Y' AND (GETDATE() < DATEADD(ss,@TimeLimit,@StartTime) OR @TimeLimit IS NULL)
BEGIN
SET @CurrentCommandType14 = 'UPDATE_STATISTICS'
SET @CurrentCommand14 = ''
IF @LockTimeout IS NOT NULL SET @CurrentCommand14 = 'SET LOCK_TIMEOUT ' + CAST(@LockTimeout * 1000 AS nvarchar) + '; '
SET @CurrentCommand14 = @CurrentCommand14 + 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabaseName) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentStatisticsName)
IF @StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ' WITH'
IF @StatisticsSample = 100 SET @CurrentCommand14 = @CurrentCommand14 + ' FULLSCAN'
IF @StatisticsSample IS NOT NULL AND @StatisticsSample <> 100 AND (@CurrentIsMemoryOptimized = 0 OR @CurrentIsMemoryOptimized IS NULL) SET @CurrentCommand14 = @CurrentCommand14 + ' SAMPLE ' + CAST(@StatisticsSample AS nvarchar) + ' PERCENT'
IF @StatisticsResample = 'Y' OR (@CurrentIsMemoryOptimized = 1 AND (@StatisticsSample <> 100 OR @StatisticsSample IS NULL)) SET @CurrentCommand14 = @CurrentCommand14 + ' RESAMPLE'
IF (@StatisticsSample IS NOT NULL OR @StatisticsResample = 'Y' OR @CurrentIsMemoryOptimized = 1) AND @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ','
IF @CurrentNoRecompute = 1 SET @CurrentCommand14 = @CurrentCommand14 + ' NORECOMPUTE'
EXECUTE @CurrentCommandOutput14 = [DBO].[spCommandExecute] @Command = @CurrentCommand14, @CommandType = @CurrentCommandType14, @Mode = 2, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @StatisticsName = @CurrentStatisticsName, @LogToTable = @LogToTable, @Execute = @Execute
SET @Error = @@ERROR
IF @Error <> 0 SET @CurrentCommandOutput14 = @Error
IF @CurrentCommandOutput14 <> 0 SET @ReturnCode = @CurrentCommandOutput14
END
NoAction:
-- Update that the index is completed
UPDATE @tmpIndexesStatistics
SET Completed = 1
WHERE Selected = 1
AND Completed = 0
AND ID = @CurrentIxID
-- Clear variables
SET @CurrentCommand02 = NULL
SET @CurrentCommand03 = NULL
SET @CurrentCommand04 = NULL
SET @CurrentCommand05 = NULL
SET @CurrentCommand06 = NULL
SET @CurrentCommand07 = NULL
SET @CurrentCommand08 = NULL
SET @CurrentCommand09 = NULL
SET @CurrentCommand10 = NULL
SET @CurrentCommand11 = NULL
SET @CurrentCommand12 = NULL
SET @CurrentCommand13 = NULL
SET @CurrentCommand14 = NULL
SET @CurrentCommandOutput13 = NULL
SET @CurrentCommandOutput14 = NULL
SET @CurrentCommandType13 = NULL
SET @CurrentCommandType14 = NULL
SET @CurrentIxID = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentObjectType = NULL
SET @CurrentIsMemoryOptimized = NULL
SET @CurrentIndexID = NULL
SET @CurrentIndexName = NULL
SET @CurrentIndexType = NULL
SET @CurrentStatisticsID = NULL
SET @CurrentStatisticsName = NULL
SET @CurrentPartitionID = NULL
SET @CurrentPartitionNumber = NULL
SET @CurrentPartitionCount = NULL
SET @CurrentIsPartition = NULL
SET @CurrentIndexExists = NULL
SET @CurrentStatisticsExists = NULL
SET @CurrentIsImageText = NULL
SET @CurrentIsNewLOB = NULL
SET @CurrentIsFileStream = NULL
SET @CurrentIsColumnStore = NULL
SET @CurrentAllowPageLocks = NULL
SET @CurrentNoRecompute = NULL
SET @CurrentStatisticsModified = NULL
SET @CurrentOnReadOnlyFileGroup = NULL
SET @CurrentFragmentationLevel = NULL
SET @CurrentPageCount = NULL
SET @CurrentFragmentationGroup = NULL
SET @CurrentAction = NULL
SET @CurrentMaxDOP = NULL
SET @CurrentUpdateStatistics = NULL
SET @CurrentComment = NULL
SET @CurrentExtendedInfo = NULL
DELETE FROM @CurrentActionsAllowed
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 @CurrentCommand01 = NULL
DELETE FROM @tmpIndexesStatistics
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
go
USE [master]
GO
/****** Object: StoredProcedure [DBO].[spCommandExecute] Script Date: 13/02/2017 12:41:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if OBJECT_ID('[DBO].[spCommandExecute]') > 0
drop proc [DBO].[spCommandExecute]
go
CREATE PROCEDURE [DBO].[spCommandExecute]
@Command nvarchar(max),
@CommandType nvarchar(max),
@Mode int,
@Comment nvarchar(max) = NULL,
@DatabaseName nvarchar(max) = NULL,
@SchemaName nvarchar(max) = NULL,
@ObjectName nvarchar(max) = NULL,
@ObjectType nvarchar(max) = NULL,
@IndexName nvarchar(max) = NULL,
@IndexType int = NULL,
@StatisticsName nvarchar(max) = NULL,
@PartitionNumber int = NULL,
@ExtendedInfo xml = NULL,
@LogToTable nvarchar(max),
@Execute nvarchar(max)
AS
BEGIN
-- Source: https://ola.hallengren.com
SET NOCOUNT ON
DECLARE @StartMessage nvarchar(max)
DECLARE @EndMessage nvarchar(max)
DECLARE @ErrorMessage nvarchar(max)
DECLARE @ErrorMessageOriginal nvarchar(max)
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @StartTimeSec datetime
DECLARE @EndTimeSec datetime
DECLARE @ID int
DECLARE @Error int
DECLARE @ReturnCode int
SET @Error = 0
SET @ReturnCode = 0
----------------------------------------------------------------------------------------------------
--// Check core requirements //--
----------------------------------------------------------------------------------------------------
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 @Error <> 0
BEGIN
SET @ReturnCode = @Error
GOTO ReturnCode
END
----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------
IF @Command IS NULL OR @Command = ''
BEGIN
SET @ErrorMessage = 'The value for the parameter @Command is not supported.' + CHAR(13) + CHAR(10) + ' '
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END
IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60
BEGIN
SET @ErrorMessage = 'The value for the parameter @CommandType 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 the parameter @Mode 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 @ReturnCode = @Error
GOTO ReturnCode
END
----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------
SET @StartTime = GETDATE()
SET @StartTimeSec = CONVERT(datetime,CONVERT(nvarchar,@StartTime,120),120)
SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTimeSec,120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Command: ' + @Command
IF @Comment IS NOT NULL SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10) + 'Comment: ' + @Comment
SET @StartMessage = REPLACE(@StartMessage,'%','%%')
RAISERROR(@StartMessage,10,1) WITH NOWAIT
IF @LogToTable = 'Y'
BEGIN
INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime)
VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime)
END
SET @ID = SCOPE_IDENTITY()
----------------------------------------------------------------------------------------------------
--// Execute command //--
----------------------------------------------------------------------------------------------------
IF @Mode = 1 AND @Execute = 'Y'
BEGIN
EXECUTE(@Command)
SET @Error = @@ERROR
SET @ReturnCode = @Error
END
IF @Mode = 2 AND @Execute = 'Y'
BEGIN
BEGIN TRY
EXECUTE(@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ReturnCode = @Error
SET @ErrorMessageOriginal = ERROR_MESSAGE()
SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END CATCH
END
----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------
SET @EndTime = GETDATE()
SET @EndTimeSec = CONVERT(datetime,CONVERT(varchar,@EndTime,120),120)
SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END + CHAR(13) + CHAR(10)
SET @EndMessage = @EndMessage + 'Duration: ' + CASE WHEN DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,@StartTimeSec, @EndTimeSec)/(24*3600) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,@EndTimeSec - @StartTimeSec,108) + CHAR(13) + CHAR(10)
SET @EndMessage = @EndMessage + 'Date and time: ' + CONVERT(nvarchar,@EndTimeSec,120) + CHAR(13) + CHAR(10) + ' '
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT
IF @LogToTable = 'Y'
BEGIN
UPDATE dbo.CommandLog
SET EndTime = @EndTime,
ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END,
ErrorMessage = @ErrorMessageOriginal
WHERE ID = @ID
END
ReturnCode:
IF @ReturnCode <> 0
BEGIN
RETURN @ReturnCode
END
----------------------------------------------------------------------------------------------------
END
2º - Prepare the Windows Schedule Task to execute it
-------------------------------------------------------------------
Transaction:
exec DBO.spMaintenance @Operation = 'BackupDB'
exec DBO.spMaintenance @Operation = 'ReIndex'
exec DBO.spMaintenance @Operation = 'CycleLog'
SQL Backup:
--------------------------
Command line: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
TSQL: -UusrDBO -PPassword -Q "exec DBO.spMaintenance @Operation='BackupDB'"
SQL Maintenance:
--------------------------
Command line: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
TSQL: -UusrDBO -PPassword -Q "exec DBO.spMaintenance @Operation='BackupDB'"
Command line: "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE"
TSQL: -UusrDBO -PPassword -Q "exec DBO.spMaintenance @Operation='BackupDB'"