Evidence Library DB Maintenance Plan

EvidenceLibrary-MaintenancePlan.sql

use WGEvidenceLibrary
go

/*** ACTIONS BEING PERFORMED IN ORDER
	1.  REBUILD INDEXES
	2.  TRUNCATED LOG FILE
	3.  SHRINK DATABASE
	4.  UPDATE STATISTICS
	5.  UPDATE AUTOGROWTH SETTINGS



**/



select 'SIZE BEFORE' AS ACTIONS
EXEC sp_spaceused


/********************************************************************************************************************************************/
/*** REBUILT INDEXES *****************************************************************************************************************************************/
/********************************************************************************************************************************************/
select 'REBUILT INDEXES' AS ACTIONS


declare @db nvarchar(200)
set @db = DB_NAME()

if object_id('tempdb..#table') is not null drop table #table

select
	*
into #table
from
	(
SELECT 
	OBJECT_NAME(s.OBJECT_ID) as table_name,
	x.name as index_name,
	case
		when round(s.avg_fragmentation_in_percent ,0) between 5 and 30 then 'ALTER INDEX '+quotename(x.name)+' ON '+quotename(@db)+'.'+quotename(OBJECT_SCHEMA_NAME(s.OBJECT_ID))+'.'+quotename(OBJECT_NAME(s.OBJECT_ID))+' REORGANIZE'
		when round(s.avg_fragmentation_in_percent ,0) > 30 then 'ALTER INDEX '+quotename(x.name)+' ON '+quotename(@db)+'.'+quotename(OBJECT_SCHEMA_NAME(s.OBJECT_ID))+'.'+quotename(OBJECT_NAME(s.OBJECT_ID))+' REBUILD'
	END
		AS script_action,
	s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats 
(DB_ID(@db), Null, NULL, NULL , 'limited') s
	inner join sys.indexes x on 
		s.index_id = x.index_id and
		s.object_id = x.object_id
) as d1
where
	script_action is not null
order by
	avg_fragmentation_in_percent desc

	

begin
	/*** THE CURSOR BELOW!!! **/
	declare @script nvarchar(max)
	declare @getScript cursor

	set @getScript = cursor for
	select
		script_action
	from
		#table

	open @getScript 

	fetch next from @getScript into @script

	while @@fetch_status = 0
	begin
		exec(@script)

		fetch next from @getScript into @script
	end

	close @getScript
	deallocate @getScript
end
go

/********************************************************************************************************************************************/
/*** TRUNCATED LOG FILE *****************************************************************************************************************************************/
/********************************************************************************************************************************************/
select 'TRUNCATED LOG FILE' AS ACTIONS
ALTER DATABASE [WGEvidenceLibrary] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(WGEvidenceLibrary_log, 1)
ALTER DATABASE [WGEvidenceLibrary] SET RECOVERY FULL WITH NO_WAIT
GO


/********************************************************************************************************************************************/
/*** SHRINK DATABASE *****************************************************************************************************************************************/
/********************************************************************************************************************************************/
select 'SHRINK DATABASE' AS ACTIONS
DBCC SHRINKDATABASE (WGEvidenceLibrary);
GO


/********************************************************************************************************************************************/
/*** UPDATE STATISTICS *****************************************************************************************************************************************/
/********************************************************************************************************************************************/
select 'UPDATE STATISTICS' AS ACTIONS
	EXEC sp_updatestats
GO
	
select 'UPDATE AUTOGROWTH SETTINGS' AS ACTIONS
GO

ALTER DATABASE [WGEvidenceLibrary] MODIFY FILE (NAME='WGEvidenceLibrary', FILEGROWTH = 256MB);
ALTER DATABASE [WGEvidenceLibrary] MODIFY FILE (NAME='WGEvidenceLibrary_log', FILEGROWTH = 128MB);
GO

select 'SIZE BEFORE' AS ACTIONS
EXEC sp_spaceused