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: