MOM SCDWgroom Enhancement

USE [msdb]
GO
/****** Object:  Job [SCDWGroomJob]    
 ****** Created By:  Sean Gorman
 ****** Purpose: If you ever wanted to downsize the amount of data that
	you keep in your datawarehouse, good luck.  This will help you do that.
	This job is set to shrink it down to 120 days, but you can adjust it as
	you like.  Runs again in 5 day increments with each run. 

Script Date: 07/19/2007 15:36:59 ******/



BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/19/2007 15:36:59 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SCDWGroomJob', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Job that executes the datawarehouse grooming stored procedure.

The first step slowly increments the number of groom days from the current setting down to 120.  It will not increment lower than 120.  Change this step''s code to change the data retention period for the MOM Reporting Database (gormans1)', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'CLEANHARBORS\SQL_MOM_DAS', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [DWGroomingStep]    Script Date: 07/19/2007 15:37:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DWGroomingStep', 
		@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=1, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXECUTE dbo.p_GroomDatawarehouseTables', 
		@database_name=N'SystemCenterReporting', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Increment Down the Number of GROOM_DAYS]    Script Date: 07/19/2007 15:37:00 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Increment Down the Number of GROOM_DAYS', 
		@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 @current_groomdays INT
DECLARE @new_groomdays INT

SET @current_groomdays = (
										SELECT top 1 wcs.WCS_GroomDays
										FROM WarehouseClassSchema wcs
										JOIN ClassSchemas cs
										ON cs.CS_ClassID = wcs.WCS_ClassID
										WHERE cs.CS_TableName = ''SC_AlertFact_Table'' OR
										cs.CS_TableName = ''SC_AlertHistoryFact_Table'' OR
										cs.CS_TableName = ''SC_AlertToEventFact_Table'' OR
										cs.CS_TableName = ''SC_EventFact_Table'' OR
										cs.CS_TableName = ''SC_EventParameterFact_Table'' OR
										cs.CS_TableName = ''SC_SampledNumericDataFact_Table'' AND
										wcs.WCS_MustBeGroomed = 1
										)

IF @current_groomdays > 120
	BEGIN
	SET @new_groomdays = (@current_groomdays - 5)
		Exec p_updategroomdays ''SC_AlertFact_Table'', @new_groomdays
		Exec p_updategroomdays ''SC_AlertHistoryFact_Table'', @new_groomdays
		Exec p_updategroomdays ''SC_AlertToEventFact_Table'', @new_groomdays
		Exec p_updategroomdays ''SC_EventFact_Table'', @new_groomdays
		Exec p_updategroomdays ''SC_EventParameterFact_Table'', @new_groomdays
		Exec p_updategroomdays ''SC_SampledNumericDataFact_Table'', @new_groomdays
	END', 
		@database_name=N'SystemCenterReporting', 
		@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'MOMX DW Groom Job Schedule', 
		@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=20070212, 
		@active_end_date=99991231, 
		@active_start_time=30000, 
		@active_end_time=235959
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: