USE [msdb]
GO

/****** Object:  Job [RCSCareSysLive Reindex]    Script Date: 04/24/2017 16:04:18 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 04/24/2017 16:04:18 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'RCSCareSysLive Reindex',
		@enabled=1,
		@notify_level_eventlog=0,
		@notify_level_email=0,
		@notify_level_netsend=0,
		@notify_level_page=0,
		@delete_level=0,
		@description=N'Reindex RCSCAreSysLive database, Created By: David Fowler, Advanced on 13 Oct 2016',
		@category_name=N'Database Maintenance',
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Reindex]    Script Date: 04/24/2017 16:04:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Reindex',
		@step_id=1,
		@cmdexec_success_code=0,
		@on_success_action=3,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'--Database Reindex
--Written By: David Fowler
--Date: 10/02/2016
--Updated 13/03/2017 - Reorganise indexes that can''t be rebuilt online
DECLARE @MaxRunTime INT = 50
DECLARE @AllowOnlineRebuild BIT = 0
DECLARE @AllowReorg BIT = 1
DECLARE @ReorgOnFail BIT = 0  --reorganise an index if online rebuild not possible

--size of index and fragmentation.  Set ReindexGroupOrder = to the SizeRating
IF OBJECT_ID(''tempdb..#indexstats'') IS NOT NULL DROP TABLE #indexstats
IF OBJECT_ID(''tempdb..#indexpriority'') IS NOT NULL DROP TABLE #indexpriority
IF OBJECT_ID(''tempdb..#TotalUsage'') IS NOT NULL DROP TABLE #TotalUsage
TRUNCATE TABLE ADVANCED_utility..ReindexWork

DECLARE @RetryFlag BIT = 0
DECLARE @StartTime AS DateTime
SET @StartTime = GETDATE()


SELECT SUM(user_seeks + user_scans + user_lookups) AS TotalUsage
INTO #TotalUsage
FROM sys.dm_db_index_usage_stats

--Get frgamentation statistics, this is running a SIMPLE scan.  We''re only worried about the leaf nodes for the priority calculations
SELECT	objects.name AS TableName,
		objects.object_id AS object_id,
		indexes.index_id AS index_id,
		indexstats.page_count AS page_count,
		indexstats.index_level AS index_level,
		COUNT(indexes.name) AS IndexCount,
		AVG(indexstats.avg_fragmentation_in_percent) AS avg_fragmentation_in_percent,
		SUM(page_count) AS TotalPageCount,
		usage.user_seeks + usage.user_scans + usage.user_lookups AS usage,
		TotalUsage.TotalUsage,
		CAST((usage.user_seeks + usage.user_scans + usage.user_lookups) AS NUMERIC) / CAST(TotalUsage.TotalUsage AS NUMERIC) * 100 as usage_percentage

INTO #indexstats
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL,NULL,''sampled'') indexstats
JOIN sys.objects objects ON objects.object_id = indexstats.object_id
JOIN sys.indexes indexes ON indexes.index_id = indexstats.index_id
							AND indexes.object_id = indexstats.object_id
JOIN sys.dm_db_index_usage_stats usage ON usage.object_id = objects.object_id
							AND usage.index_id = indexes.index_id,

#TotalUsage TotalUsage
WHERE indexes.index_id != 0 --exclude any heaps
AND indexes.is_disabled = 0 --exclude disabled indexes
AND usage.database_id = DB_ID()
GROUP BY objects.name, objects.object_id, indexes.index_id, indexstats.page_count, indexstats.index_level,usage.user_seeks,usage.user_scans,usage.user_lookups, TotalUsage
ORDER BY avg_fragmentation_in_percent DESC

--Calculate priority ranking based on index size, we''re only interested in indexes with fragmentation over 20%
SELECT
CASE
	WHEN page_count < 1000 THEN 1
	WHEN page_count < 10000 THEN 2
	WHEN page_count < 50000 THEN 3
	WHEN page_count < 100000 THEN 4
	WHEN page_count >= 100000 THEN 5
END AS SizeRating,
CASE
	WHEN avg_fragmentation_in_percent < 10 THEN 1
	WHEN avg_fragmentation_in_percent < 30 THEN 2
	WHEN avg_fragmentation_in_percent < 60 THEN 3
	WHEN avg_fragmentation_in_percent < 80 THEN 4
	WHEN avg_fragmentation_in_percent >= 80 THEN 5
END AS FragRating,
NTILE(5) OVER (ORDER BY usage) AS UsageRating,
page_count,
(CAST(page_count AS NUMERIC) * 8)/1024/1024 AS sizeGB,
avg_fragmentation_in_percent,
usage,
tables.name AS TableName,
tables.object_id,
indexes.index_id,
indexes.name AS IndexName
INTO #indexpriority
FROM #indexstats
JOIN sys.tables tables ON #indexstats.object_id = tables.object_id
JOIN sys.indexes indexes ON indexes.index_id = #indexstats.index_id AND indexes.object_id = #indexstats.object_id
WHERE avg_fragmentation_in_percent > 10
AND #indexstats.index_level = 0


--load into index work table

INSERT INTO ADVANCED_utility..ReindexWork
SELECT *, SizeRating + FragRating + UsageRating AS OverallRating,
CASE
	WHEN FragRating = 2 THEN  ''ALTER INDEX ['' + indexname + ''] ON ['' + TableName + ''] REORGANIZE;'' --reorg any indexes with fragmentation < 30%
	ELSE ''ALTER INDEX ['' + indexname + ''] ON ['' + TableName + ''] REBUILD WITH (ONLINE = ON);''
END AS ReIndexStatement,
0 AS complete,
NULL,NULL,NULL
FROM #indexpriority
WHERE SizeRating != 1
	AND FragRating != 1
	AND UsageRating != 1


--loop through each reindex in order of overallrating

DECLARE @object_id INT
DECLARE @index_id INT
DECLARE @IndexStmt VARCHAR(500)
DECLARE @SizeGB INT

DECLARE reindex_cursor CURSOR FOR
SELECT [object_id], index_id, ReindexStatement, SizeGB
FROM ADVANCED_utility..ReindexWork
ORDER BY OverallRating DESC

OPEN reindex_cursor
FETCH NEXT FROM reindex_cursor INTO  @object_id, @index_id, @IndexStmt, @SizeGB

WHILE	@@FETCH_STATUS = 0
		AND DATEDIFF(MINUTE, @StartTime, GETDATE()) < @MaxRunTime  --stop if job has run for longer than 4 hours
BEGIN

	--check available disk space

	IF ((SELECT DISTINCT (volumestats.available_bytes/1024/1024/1024) + ((size*8/1024/1024) - (FILEPROPERTY(masterfiles.name, ''SpaceUsed'')*8/1024/1024))
		FROM sys.master_files masterfiles
		CROSS APPLY sys.dm_os_volume_stats(masterfiles.database_id, file_id) volumestats
		WHERE masterfiles.type = 0
		AND masterfiles.database_id = DB_ID()) > @SizeGB * 2)

	BEGIN --if disk space ok, run reindex and log O code in work table

		BEGIN TRY
			--set start time
			UPDATE ADVANCED_utility..ReindexWork
			SET starttime = GETDATE()
			WHERE @object_id = OBJECT_ID
			AND @index_id = index_id

			--execute statement
			EXEC (@IndexStmt)

			--PRINT @IndexStmt
			UPDATE ADVANCED_utility..ReindexWork
			SET complete = 1,
			stoptime = GETDATE()
			WHERE @object_id = OBJECT_ID
			AND @index_id = index_id
		END TRY
		BEGIN CATCH --log error in work table

			--if error becase it can''t run an online index, run a reord instead
			IF ERROR_MESSAGE() LIKE ''An online operation cannot be performed for index%''
			BEGIN
				UPDATE ADVANCED_utility..ReindexWork
				SET complete = 4,
				ReIndexStatement = REPLACE(ReIndexStatement, ''REBUILD WITH (ONLINE = ON)'',''REORGANIZE'')
				WHERE @object_id = OBJECT_ID
				AND @index_id = index_id

				SET @RetryFlag = 1
			END
			ELSE --for other errors, log error code 3 and error message
			BEGIN
				UPDATE ADVANCED_utility..ReindexWork
				SET complete = 3,
					error = ERROR_MESSAGE(),
					stoptime = GETDATE()
				WHERE @object_id = OBJECT_ID
				AND @index_id = index_id
			END
		END CATCH

		--for indexes that couldn''t be rebuilt online, run a reorganise

		IF @RetryFlag = 1  --if retry flag set, rerun reindex as reorg
		BEGIN
			SET @RetryFlag = 0

			BEGIN TRY
				SELECT @IndexStmt = ReIndexStatement
				FROM ADVANCED_utility..ReindexWork
				WHERE @object_id = OBJECT_ID
				AND @index_id = index_id

				--execute statement
				EXEC (@IndexStmt)

				--PRINT @IndexStmt
				UPDATE ADVANCED_utility..ReindexWork
				SET complete = 1,
				stoptime = GETDATE()
				WHERE @object_id = OBJECT_ID
				AND @index_id = index_id

			END TRY

			BEGIN CATCH
				UPDATE ADVANCED_utility..ReindexWork
				SET complete = 3,
					error = ERROR_MESSAGE(),
					stoptime = GETDATE()
				WHERE @object_id = OBJECT_ID
				AND @index_id = index_id
			END CATCH
		END
	END
	ELSE BEGIN --if disk space low, log skip code in work table

		UPDATE ADVANCED_utility..ReindexWork
		SET complete = 2,
			error = ''Skipped Due To Low Disk Space'',
			stoptime = GETDATE()
		WHERE @object_id = OBJECT_ID
		AND @index_id = index_id

	END

	FETCH NEXT FROM reindex_cursor INTO  @object_id, @index_id, @IndexStmt,@SizeGB

END

CLOSE reindex_cursor
DEALLOCATE reindex_cursor



----retry indexes that couldn''t be rebuilt offline
--IF (@AllowOnlineRebuild = 1)
--BEGIN

--	UPDATE ADVANCED_utility..ReindexWork
--	SET ReIndexStatement = REPLACE(ReIndexStatement, ''REBUILD WITH (ONLINE = ON)'',''REBUILD WITH (ONLINE = OFF)'')
--	WHERE error LIKE ''%The operation must be performed offline.''

--	DECLARE reindex_cursor CURSOR FOR
--	SELECT [object_id], index_id, ReindexStatement, SizeGB
--	FROM ADVANCED_utility..ReindexWork
--	WHERE error LIKE ''%The operation must be performed offline.''
--	ORDER BY OverallRating DESC

--	OPEN reindex_cursor
--	FETCH NEXT FROM reindex_cursor INTO  @object_id, @index_id, @IndexStmt, @SizeGB

--	WHILE	@@FETCH_STATUS = 0
--			AND DATEDIFF(hh, @StartTime, GETDATE()) < @MaxRunTime  --stop if job has run for longer than 4 hours
--	BEGIN

--		--check available disk space

--		IF ((SELECT DISTINCT (volumestats.available_bytes/1024/1024/1024) + ((size*8/1024/1024) - (FILEPROPERTY(masterfiles.name, ''SpaceUsed'')*8/1024/1024))
--			FROM sys.master_files masterfiles
--			CROSS APPLY sys.dm_os_volume_stats(masterfiles.database_id, file_id) volumestats
--			WHERE masterfiles.type = 0
--			AND masterfiles.database_id = DB_ID()) > @SizeGB * 2)

--		BEGIN --if disk space ok, run reindex and log O code in work table

--			BEGIN TRY
--				--set start time
--				UPDATE ADVANCED_utility..ReindexWork
--				SET starttime = GETDATE()
--				WHERE @object_id = OBJECT_ID
--				AND @index_id = index_id

--				--execute statement
--				EXEC (@IndexStmt)

--				--PRINT @IndexStmt
--				UPDATE ADVANCED_utility..ReindexWork
--				SET complete = 1,
--				stoptime = GETDATE()
--				WHERE @object_id = OBJECT_ID
--				AND @index_id = index_id
--			END TRY
--			BEGIN CATCH --log error in work table
--				UPDATE ADVANCED_utility..ReindexWork
--				SET complete = 3,
--					error = ERROR_MESSAGE(),
--					stoptime = GETDATE()
--				WHERE @object_id = OBJECT_ID
--				AND @index_id = index_id
--			END CATCH
--		END
--		ELSE BEGIN --if disk space low, log skip code in work table

--			UPDATE ADVANCED_utility..ReindexWork
--			SET complete = 2,
--				error = ''Skipped Due To Low Disk Space'',
--				stoptime = GETDATE()
--			WHERE @object_id = OBJECT_ID
--			AND @index_id = index_id

--		END

--		FETCH NEXT FROM reindex_cursor INTO  @object_id, @index_id, @IndexStmt,@SizeGB

--	END

--	CLOSE reindex_cursor
--	DEALLOCATE reindex_cursor
--END


--update history table

INSERT INTO ADVANCED_utility..ReindexHistory
SELECT page_count, avg_fragmentation_in_percent, tableName, indexName, OverallRating, ReindexStatement, complete, error, starttime, stoptime
FROM ADVANCED_utility..[ReindexWork]
WHERE complete != 0',
		@database_name=N'RCSCareSysLive',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Update Stats]    Script Date: 04/24/2017 16:04:18 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update Stats',
		@step_id=2,
		@cmdexec_success_code=0,
		@on_success_action=1,
		@on_success_step_id=0,
		@on_fail_action=2,
		@on_fail_step_id=0,
		@retry_attempts=0,
		@retry_interval=0,
		@os_run_priority=0, @subsystem=N'TSQL',
		@command=N'DECLARE @stmt VARCHAR(256)
DECLARE @rowmod INT

DECLARE UpdateStatsCurr CURSOR FOR
	SELECT DISTINCT ''UPDATE STATISTICS '' + QUOTENAME(OBJECT_NAME(sysindexes.id)) AS STMT
	FROM sys.sysindexes sysindexes
	JOIN sys.indexes indexes ON	sysindexes.id = indexes.object_id AND
								sysindexes.indid = indexes.index_id
	JOIN sys.tables tables ON indexes.object_id = tables.object_id
	WHERE indexes.index_id > 0
	AND rowmodctr > 10000

OPEN UpdateStatsCurr
FETCH NEXT FROM UpdateStatsCurr INTO @stmt

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC(@stmt)
	PRINT @stmt
	FETCH NEXT FROM UpdateStatsCurr INTO @stmt
END

CLOSE UpdateStatsCurr
DEALLOCATE UpdateStatsCurr
',
		@database_name=N'RCSCareSysLive',
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'nightly @ 2am',
		@enabled=1,
		@freq_type=4,
		@freq_interval=1,
		@freq_subday_type=1,
		@freq_subday_interval=0,
		@freq_relative_interval=0,
		@freq_recurrence_factor=0,
		@active_start_date=20161013,
		@active_end_date=99991231,
		@active_start_time=20000,
		@active_end_time=235959,
		@schedule_uid=N'5a95157d-1cb0-468c-9e72-a94318e07b1a'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO