-- Author: Tyler Duni
-- Date: Mar 19, 2008
-- tyler_duni@bigfix.com

DECLARE @databaseName nvarchar(64)
SET @databaseName = N'BFEnterprise'

BEGIN TRANSACTION
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
  DECLARE @QRJobID nchar(36)

BEGIN
-- QUESTION RESULTS DEFRAG JOB
  EXECUTE msdb.dbo.sp_delete_job
	@job_name = N'BFEnterprise QuestionResults Defragment Job'

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
	@job_id = @QRJobID OUTPUT,
	@job_name = N'BFEnterprise QuestionResults Defragment Job', 
	@enabled = 1, 
	@category_id = 3, 
	@description = N'BigFix Enterprise QuestionResults table defragmentation Job'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @QRJobID,
	@step_id = 1, 
	@step_name = N'Enable Page Locking',
	@command = N'EXEC sp_indexoption QUESTIONRESULTS, AllowPageLocks, TRUE',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 3, --Go to next step
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @QRJobID,
	@step_id = 2, 
	@step_name = N'Defragment QuestionResults IX_CID',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, QUESTIONRESULTS, IX_CID )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 3, --Go to next step
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @QRJobID,
	@step_id = 3, 
	@step_name = N'Defragment QuestionResults IX_Sequence',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, QUESTIONRESULTS, IX_Sequence )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 3, --Go to next step
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @QRJobID,
	@step_id = 4, 
	@step_name = N'Defragment QuestionResults PK_QUESTIONRESULTS',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, QUESTIONRESULTS, PK_QUESTIONRESULTS )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @QRJobID,
	@step_id = 5, 
	@step_name = N'Disable Page Locking',
	@command = N'EXEC sp_indexoption QUESTIONRESULTS, AllowPageLocks, FALSE',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
	@job_id = @QRJobID,
	@name = N'Schedule: Runs every 6 hours, 3 times a day and starts at 6am.',
	@enabled = 1,
	@freq_type = 4, -- 4 -> Daily
	@active_start_date = NULL, -- NULL -> Today
	@active_start_time = 060000, -- HHMMSS -> Starts at 6 am.
	@freq_interval = 1, -- 1 -> every day
	@freq_subday_type = 8, -- 8 -> hour
	@freq_subday_interval = 6, -- every 6 hours
	@freq_relative_interval = 0,
	@freq_recurrence_factor = 1,
	@active_end_date = 99991231, -- end_date -> End of (SQL) Time
	@active_end_time = 220000 -- HHMMSS -> Ends at 10pm. (re-index runs at midnight instead of defrag)
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
	@job_id = @QRJobID,
	@server_name = N'(local)'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


-- FIXLET RESULTS DEFRAG JOB
  EXECUTE msdb.dbo.sp_delete_job
	@job_name = N'BFEnterprise FixletResults Defragment Job'

  DECLARE @FRJobID nchar(36)

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
	@job_id = @FRJobID OUTPUT,
	@job_name = N'BFEnterprise FixletResults Defragment Job', 
	@enabled = 1, 
	@category_id = 3, 
	@description = N'BigFix Enterprise FixletResults table defragmentation Job'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @FRJobID,
	@step_id = 1, 
	@step_name = N'Defragment FixletResults INDEX_FIXLETRESULTS_ROWVERSION',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, FIXLETRESULTS, INDEX_FIXLETRESULTS_ROWVERSION )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 3, --Go to next step
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @FRJobID,
	@step_id = 2, 
	@step_name = N'Defragment FixletResults PK_FIXLETRESULTS',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, FIXLETRESULTS, PK_FIXLETRESULTS )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
	@job_id = @FRJobID,
	@name = N'Schedule: Runs every 6 hours, 3 times a day and starts at 8am.',
	@enabled = 1,
	@freq_type = 4, -- 4 -> Daily
	@active_start_date = NULL, -- NULL -> Today
	@active_start_time = 080000, -- HHMMSS -> Starts at 8 am.
	@freq_interval = 1, -- 1 -> every day
	@freq_subday_type = 8, -- 8 -> hour
	@freq_subday_interval = 6, -- every 6 hours
	@freq_relative_interval = 0,
	@freq_recurrence_factor = 1,
	@active_end_date = 99991231, -- end_date -> End of (SQL) Time
	@active_end_time = 235959 -- HHMMSS -> Ends at 12pm. (re-index runs at 2am instead of defrag)
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
	@job_id = @FRJobID,
	@server_name = N'(local)'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


-- ACTION RESULTS DEFRAG JOB
  EXECUTE msdb.dbo.sp_delete_job
	@job_name = N'BFEnterprise ActionResults Defragment Job'

  DECLARE @ARJobID nchar(36)

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
	@job_id = @ARJobID OUTPUT,
	@job_name = N'BFEnterprise ActionResults Defragment Job', 
	@enabled = 1, 
	@category_id = 3, 
	@description = N'BigFix Enterprise ActionResults table defragmentation Job'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @ARJobID,
	@step_id = 1, 
	@step_name = N'Defragment ActionResults INDEX_ACTIONRESULTS_ROWVERSION',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, ACTIONRESULTS, INDEX_ACTIONRESULTS_ROWVERSION )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 3, --Go to next step
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @ARJobID,
	@step_id = 2, 
	@step_name = N'Defragment ActionResults PK_ACTIONRESULTS',
	@command = N'DBCC INDEXDEFRAG( BFEnterprise, ACTIONRESULTS, PK_ACTIONRESULTS )',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
	@job_id = @ARJobID,
	@name = N'Schedule: Runs every 6 hours, 3 times a day and starts at 10am.',
	@enabled = 1,
	@freq_type = 4, -- 4 -> Daily
	@active_start_date = NULL, -- NULL -> Today
	@active_start_time = 0100000, -- HHMMSS -> Starts at 10 am.
	@freq_interval = 1, -- 1 -> every day
	@freq_subday_type = 8, -- 8 -> hour
	@freq_subday_interval = 6, -- every 6 hours
	@freq_relative_interval = 0,
	@freq_recurrence_factor = 1,
	@active_end_date = 99991231, -- end_date -> End of (SQL) Time
	@active_end_time = 020000 -- HHMMSS -> Ends at 2am. (re-index runs at 4am instead of defrag)
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
	@job_id = @ARJobID,
	@server_name = N'(local)'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- QUESTION RESULTS REINDEX JOB
  EXECUTE msdb.dbo.sp_delete_job
	@job_name = N'BFEnterprise QuestionResults Reindex Job'

  DECLARE @QRRJobID nchar(36)

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
	@job_id = @QRRJobID OUTPUT,
	@job_name = N'BFEnterprise QuestionResults Reindex Job', 
	@enabled = 1, 
	@category_id = 3, 
	@description = N'BigFix Enterprise QuestionResults table reindex Job'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @QRRJobID,
	@step_id = 1, 
	@step_name = N'Reindex QuestionResults',
	@command = N'DBCC DBREINDEX(''BFEnterprise.dbo.QUESTIONRESULTS'')',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
	@job_id = @QRRJobID,
	@name = N'Schedule: Runs once a day at midnight.',
	@enabled = 1,
	@freq_type = 4, -- 4 -> Daily
	@active_start_date = NULL, -- NULL -> Today
	@active_start_time = 000000, -- HHMMSS -> Starts at midnight.
	@freq_interval = 1, -- 1 -> every day
	@freq_subday_type = 1, -- 1 -> At the specified time.
	@freq_subday_interval = 0,
	@freq_relative_interval = 0,
	@freq_recurrence_factor = 0,
	@active_end_date = 99991231, -- end_date -> End of (SQL) Time
	@active_end_time = 235959
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
	@job_id = @QRRJobID,
	@server_name = N'(local)'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- FIXLET RESULTS REINDEX JOB
  EXECUTE msdb.dbo.sp_delete_job
	@job_name = N'BFEnterprise FixletResults Reindex Job'

  DECLARE @FRRJobID nchar(36)

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
	@job_id = @FRRJobID OUTPUT,
	@job_name = N'BFEnterprise FixletResults Reindex Job', 
	@enabled = 1, 
	@category_id = 3, 
	@description = N'BigFix Enterprise FixletResults table reindex Job'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @FRRJobID,
	@step_id = 1, 
	@step_name = N'Reindex FixletResults',
	@command = N'DBCC DBREINDEX(''BFEnterprise.dbo.FIXLETRESULTS'')',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
	@job_id = @FRRJobID,
	@name = N'Schedule: Runs once a day at 2:00 AM.',
	@enabled = 1,
	@freq_type = 4, -- 4 -> Daily
	@active_start_date = NULL, -- NULL -> Today
	@active_start_time = 020000, -- HHMMSS -> Starts at 2am.
	@freq_interval = 1, -- 1 -> every day
	@freq_subday_type = 1, -- 1 -> At the specified time.
	@freq_subday_interval = 0,
	@freq_relative_interval = 0,
	@freq_recurrence_factor = 0,
	@active_end_date = 99991231, -- end_date -> End of (SQL) Time
	@active_end_time = 235959
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
	@job_id = @FRRJobID,
	@server_name = N'(local)'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- ACTION RESULTS REINDEX JOB
  EXECUTE msdb.dbo.sp_delete_job
	@job_name = N'BFEnterprise ActionResults Reindex Job'

  DECLARE @ARRJobID nchar(36)

  EXECUTE @ReturnCode = msdb.dbo.sp_add_job 
	@job_id = @ARRJobID OUTPUT,
	@job_name = N'BFEnterprise ActionResults Reindex Job', 
	@enabled = 1, 
	@category_id = 3, 
	@description = N'BigFix Enterprise ActionResults table reindex Job'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep 
	@job_id = @ARRJobID,
	@step_id = 1, 
	@step_name = N'Reindex ActionResults',
	@command = N'DBCC DBREINDEX(''BFEnterprise.dbo.ACTIONRESULTS'')',
	@database_name = @databaseName,
	@subsystem = N'TSQL',
	@on_success_step_id = 0,
	@on_success_action = 1, --Quit with success
	@on_fail_step_id = 0,
	@on_fail_action = 2 -- Quit with failure
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule 
	@job_id = @ARRJobID,
	@name = N'Schedule: Runs once a day at 4 AM.',
	@enabled = 1,
	@freq_type = 4, -- 4 -> Daily
	@active_start_date = NULL, -- NULL -> Today
	@active_start_time = 040000, -- HHMMSS -> Starts at 4 am.
	@freq_interval = 1, -- 1 -> every day
	@freq_subday_type = 1, -- 1 -> At the specified time.
	@freq_subday_interval = 0,
	@freq_relative_interval = 0,
	@freq_recurrence_factor = 0,
	@active_end_date = 99991231, -- end_date -> End of (SQL) Time
	@active_end_time = 235959
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver
	@job_id = @ARRJobID,
	@server_name = N'(local)'
  IF ( @@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
  IF ( @@TRANCOUNT > 0 ) ROLLBACK TRANSACTION
EndSave: