SQL-LOG-DAILY CYCLE - Job Script

-- Simple Job Script to cycle the SQL Error Log every 24 hours
-- 	1. Should set the SQL Log files retention to 15 days (default = 6)
-- 	2. This assists in keeping large logs manageable
--Jbabington@hotmail.com
-- NOTES:
--	@output_file_name is a settable path value
-- 	@enabled is set to 0 (disabled) so you can review the 
--		job before attempting to run it
--	@owner_login_name Set to SA however you may wish to use
--		another existing service account
-- 	@active_start_time = 235900 This is 11:59:00 PM 
--		and is alterable as needed



BEGIN TRANSACTION            
  DECLARE 	@JobID 		BINARY(16)  
  DECLARE 	@ReturnCode 	INT    
  SELECT 	@ReturnCode 	= 0     
IF (SELECT COUNT(*) 
FROM msdb.dbo.syscategories 
	WHERE name = N'Database Maintenance') 	< 1 
EXECUTE msdb.dbo.sp_add_category @name 		= N'Database Maintenance'

-- Delete the job with the same name (if it exists)
  
SELECT @JobID 		= job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'SQL-LOG-DAILY CYCLE')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
-- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
-- There is, so abort the script 
    RAISERROR (N'Unable to import job ''SQL-LOG-DAILY CYCLE'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'SQL-LOG-DAILY CYCLE' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  
EXECUTE @ReturnCode 		= msdb.dbo.sp_add_job @job_id = @JobID OUTPUT 
	,@job_name 		= N'SQL-LOG-DAILY CYCLE'
	,@owner_login_name 	= N'SA'
	,@description 		= N'Cycle the SQL Server Error log every 24 hours'
	,@category_name 	= N'Database Maintenance'
	,@enabled 		= 1
	,@notify_level_email 	= 0
	,@notify_level_page 	= 0
	,@notify_level_netsend 	= 0
	,@notify_level_eventlog = 2
	,@delete_level		= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	GOTO QuitWithRollback 

  -- Add the job steps
  
EXECUTE @ReturnCode 		= msdb.dbo.sp_add_jobstep @job_id = @JobID
	, @step_id 		= 1
	, @step_name 		= N'Cycle Log (SQL Error Log)'
	, @command 		= N'EXEC SP_CYCLE_ERRORLOG'	
	, @database_name 	= N'master'
	, @server 		= N''
	, @database_user_name 	= N''
	, @subsystem 		= N'TSQL'
	, @cmdexec_success_code = 0
	, @flags 		= 0
	, @retry_attempts 	= 0
	, @retry_interval 	= 1
	, @output_file_name 	= N'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\CYCLE_SQL_ERRORLOG'--Settable Path
	, @on_success_step_id 	= 0
	, @on_success_action 	= 1
	, @on_fail_step_id 	= 0
	, @on_fail_action 	= 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID
	, @start_step_id 	= 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	GOTO QuitWithRollback 

  -- Add the job schedules
  
EXECUTE @ReturnCode 		= msdb.dbo.sp_add_jobschedule @job_id = @JobID
, @name 			= N'CYCLE SQL ERROR LOG DAILY'
, @enabled 			= 0 -- currently disabled to enable set to 1 
, @freq_type 			= 4
, @active_start_date 		= 20040920
, @active_start_time 		= 235900
, @freq_interval 		= 1
, @freq_subday_type 		= 1
, @freq_subday_interval 	= 0
, @freq_relative_interval 	= 0
, @freq_recurrence_factor 	= 0
, @active_end_date 		= 99991231
, @active_end_time 		= 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	GOTO QuitWithRollback 

  -- Add the Target Servers
  
EXECUTE @ReturnCode 		= msdb.dbo.sp_add_jobserver @job_id = @JobID
	, @server_name 		= N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
	GOTO QuitWithRollback 

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