text: /*
     Copyright 2016 Aaron Morelli
  
     Licensed under the Apache License, Version 2.0 (the "License");
     you may not use this file except in compliance with the License.
     You may obtain a copy of the License at
  
         http://www.apache.org/licenses/LICENSE-2.0
  
     Unless required by applicable law or agreed to in writing, software
     distributed under the License is distributed on an "AS IS" BASIS,
     WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
     See the License for the specific language governing permissions and
     limitations under the License.
  
  	------------------------------------------------------------------------
  
  	PROJECT NAME: ChiRho https://github.com/AaronMorelli/ChiRho
  
  	PROJECT DESCRIPTION: A T-SQL toolkit for troubleshooting performance and stability problems on SQL Server instances
  
  	FILE NAME: ChiRhoMaster.sql
  
  	JOB NAME: <DB name> - Every 15 Min - Daily - ChiRho Master
  
  	AUTHOR:			Aaron Morelli
  					aaronmorelli@zoho.com
  					@sqlcrossjoin
  					sqlcrossjoin.wordpress.com
  
  	PURPOSE: Runs every 15 minutes (by default) and ensures that the AutoWho and ServerEye
  	traces are running (if the current time is inside of a time range where those traces
  	should be running).
  */
  USE [msdb]
  GO
  DECLARE @DBN_input NVARCHAR(256),
  		@JobName NVARCHAR(128);
  SET @DBN_input = N'$(DBName)';
  SET @JobName = @DBN_input +  N' - Every 15 Min - Daily - ChiRho Master'
  
  BEGIN TRANSACTION
  DECLARE @ReturnCode INT
  SELECT @ReturnCode = 0
  
  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=@JobName, 
  		@enabled=0, 
  		@notify_level_eventlog=0, 
  		@notify_level_email=0, 
  		@notify_level_netsend=0, 
  		@notify_level_page=0, 
  		@delete_level=0, 
  		@description=N'Runs frequently throughout the day to 1) check that the AutoWho and ServerEye traces are running 2) if not, start them 3) perform various maintenance tasks on the ChiRho schemas', 
  		@category_name=N'[Uncategorized (Local)]', 
  		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Call ChiRhoMaster', 
  		@step_id=1, 
  		@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 @lmsg VARCHAR(MAX),
  	@procrc INT;
  
  EXEC @procrc = CoreXR.ChiRhoMaster @ErrorMessage=@lmsg OUTPUT;
  
  PRINT ISNULL(@lmsg, ''<null>'');
  
  IF @procrc < 0
  BEGIN
  	RAISERROR(''Procedure failed'', 16, 1);
  END
  ', 
  		@database_name=@DBN_input, 
  		@flags=0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StopAutoWhoTraceOnce', 
  		@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'exec CoreXR.AbortTrace @Utility = N''AutoWho'', @TraceID = NULL, @AbortCode = N''O'', @PreventAllDay = N''N''', 
  		@database_name=@DBN_input, 
  		@flags=0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StopAutoWhoTraceAllDay', 
  		@step_id=3, 
  		@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'exec CoreXR.AbortTrace @Utility = N''AutoWho'', @TraceID = NULL, @AbortCode = N''A'', @PreventAllDay = N''Y''', 
  		@database_name=@DBN_input, 
  		@flags=0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StopServerEyeTraceOnce', 
  		@step_id=4, 
  		@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=0, 
  		@os_run_priority=0, @subsystem=N'TSQL', 
  		@command=N'exec CoreXR.AbortTrace @Utility = N''ServerEye'', @TraceID = NULL, @AbortCode = N''O'', @PreventAllDay = N''N''', 
  		@database_name=@DBN_input, 
  		@flags=0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
  
  EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'StopServerEyeTraceAllDay', 
  		@step_id=5, 
  		@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'exec CoreXR.AbortTrace @Utility = N''ServerEye'', @TraceID = NULL, @AbortCode = N''A'', @PreventAllDay = N''Y''', 
  		@database_name=@DBN_input, 
  		@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'XRMasterEveryXMinutes', 
  		@enabled=1, 
  		@freq_type=4, 
  		@freq_interval=1, 
  		@freq_subday_type=4, 
  		@freq_subday_interval=15, 
  		@freq_relative_interval=0, 
  		@freq_recurrence_factor=0, 
  		@active_start_date=20151002, 
  		@active_end_date=99991231, 
  		@active_start_time=0, 
  		@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:
  
  GO
  
  
  