--- Protect Server log removal job

DECLARE
	-- Set this to the name of your Protect Server database
	@database VARCHAR(255) = 'ProtectServerData',
	
	-- Set this to the time each day to trigger the log removal job (format: HHMMSS)
	@start_time INT = 231000,
	
	-- Set this to the maximum number of days to retain logs
	@days_to_keep_log_entries nvarchar(max) = '180',
	
	-- Set this to the maximum number of log entries to remove per attempt. This script can 
	-- consume 100% of a CPU core for a long amount of time. (40000x5 records 
	-- took about 40 minutes to delete). 
	@max_number_of_events_to_delete nvarchar(max) = '20000',
	
	-- Set this to the user to run the job
	@job_user VARCHAR(255) = 'sa'
	
USE [msdb]

DECLARE @JobId	UNIQUEIDENTIFIER

-- Create a Job Catagory
IF NOT EXISTS ( SELECT 1 FROM [msdb].[dbo].[syscategories] WHERE name = 'ProtectServer_DataPolicy' )
BEGIN
	EXECUTE sp_add_category @class = 'JOB', @type = 'LOCAL', @name = 'ProtectServer_DataPolicy'
END	

IF EXISTS ( SELECT 1 FROm [msdb].[dbo].[sysjobs] WHERE name = 'ProtectServer_DeleteLogs' )
BEGIN
	EXECUTE sp_delete_job @job_name = 'ProtectServer_DeleteLogs'
END


-- Create a Job
EXECUTE sp_add_job @job_name = 'ProtectServer_DeleteLogs'
                   , @enabled = 1
                   , @description = 'Removal of old logs'
                   , @category_name = 'ProtectServer_DataPolicy'
                   , @owner_login_name = @job_user
                   , @notify_level_eventlog = 2
                   , @job_id = @JobId OUTPUT
          
declare @removeCommand nvarchar(max) =
'
USE '+@database+'

SET NOCOUNT ON

DECLARE @EventsToRemove TABLE
(
	Id			INT IDENTITY(1,1),
	EventId INT NOT NULL,
	DayAdded	DATETIME,
	Processed	BIT DEFAULT 0
)

-- Set date 180 days ago (6 months)
DECLARE @StartDate DATETIME = DATEADD(day, -'+@days_to_keep_log_entries+', GETDATE())

PRINT ''Removing records from before :''
PRINT @StartDate

INSERT INTO @EventsToRemove(EventId, DayAdded) SELECT DISTINCT TOP('+ @max_number_of_events_to_delete +') EventId, DayAdded FROM [Events].[Logs] WHERE DayAdded < @StartDate ORDER BY DayAdded

DECLARE @TotalRemovableRecords INT = (SELECT COUNT(*) FROM [Events].[Logs] WHERE DayAdded < @StartDate)
PRINT ''Number of Records older than ' + @days_to_keep_log_entries + ' days :'' 
PRINT @TotalRemovableRecords

DECLARE @NumRecordsToRemove INT = (SELECT COUNT(*) FROM @EventsToRemove)
PRINT ''Number of Records to remove :'' 
PRINT @NumRecordsToRemove

DECLARE @EarliestDateAdded DATETIME = (SELECT MIN(DayAdded) FROM @EventsToRemove)
PRINT ''Earliest date to remove:''
PRINT @EarliestDateAdded

DECLARE @LatestDateAdded DATETIME = (SELECT MAX(DayAdded) FROM @EventsToRemove)
PRINT ''Latest date to remove:''
PRINT @LatestDateAdded

DECLARE @EventId		INT
WHILE EXISTS ( SELECT 1 FROM @EventsToRemove WHERE Processed = 0 )
BEGIN
	SELECT	@EventId = MIN(EventId)
	FROM	@EventsToRemove
	WHERE	Processed = 0
	
	BEGIN TRY	
		BEGIN TRANSACTION
			-- Remove Metadata Results
			DELETE	[Events].[MetadataResults]
			WHERE	EventId = @EventId			
					
			-- Remove Attachemnts			
			DELETE	[Events].[Attachments]
			WHERE	EventId = @EventId			
			
			-- Remove Recipients			
			DELETE	[Events].[Recipients]
			WHERE	EventId = @EventId			
			
			-- Remove Metadata Info			
			DELETE	[Events].[MetadataInfo]
			WHERE	EventId = @EventId			
			
			-- Remove Recipients		
			DELETE	[Events].[Logs]
			WHERE	EventId = @EventId			
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		-- Most common problem will deadlocks so let it continue to the next day
		-- to be processed.  When this script is ran again the deadlock for that
		-- day should have cleared.
	END CATCH	
	
	UPDATE	@EventsToRemove
	SET		Processed = 1
	WHERE	EventId = @EventId			
END

PRINT ''Number of log entries removed:''
DECLARE @RemoveEventsProcessed INT = (SELECT COUNT(*) FROM @EventsToRemove WHERE Processed = 1) 
PRINT @RemoveEventsProcessed

PRINT ''Log entries unable to be removed (these are probably locked and shall be removed on next run):''
DECLARE @RemoveEventsUnprocessed INT = (SELECT COUNT(*) FROM @EventsToRemove WHERE Processed = 0) 
PRINT @RemoveEventsUnprocessed
SET NOCOUNT OFF
'

-- Add the steps                   
EXECUTE sp_add_jobstep	@job_name = 'ProtectServer_DeleteLogs'
						, @step_id = 1
						, @step_name = 'Remove Data'
						, @subsystem = 'TSQL' 
						, @database_name = @database
						, @retry_attempts = 2
						, @retry_interval = 5
						, @command = @removeCommand

-- Add the schedule
EXECUTE sp_add_jobschedule @job_name = 'ProtectServer_DeleteLogs'
							, @name = 'ProtectServer_DeleteSchedule'
							, @freq_type = 4 -- Daily
							, @freq_interval=1 -- Once per day
							, @freq_subday_type=1
							, @freq_subday_interval=0
							, @freq_recurrence_factor=1 -- recurs every day
							, @active_start_date=20101201 -- Starts on 1st Dec 2010 
							, @active_start_time=@start_time
                          
EXECUTE sp_add_jobserver @job_name = 'ProtectServer_DeleteLogs'
							, @server_name = '(LOCAL)'