/******************************
** FileName: CycleErrorLogs.sql 
** Description: This script will create a job to check the age of the errorlog. if the file is older than 14 days cycle the errorlog
** SQL Server Version: SQL2008R2 and lower (for SQL2012 and higher change the max file size)
*******************************/
use [msdb]
go
/*parameters*/
declare 
  @JobID binary(16)
, @ScheduleID int

/*Check for the existence of job Category "Server Maintenance". */
if not exists (select name from msdb.dbo.syscategories where name=N'Server Maintenance' and category_class=1)
begin
	exec msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Server Maintenance'
end

/*Create Job Cycle errorlogs*/
exec msdb.dbo.sp_add_job 
  @job_name=N'Cycle errorlogs'
, @enabled=1
, @notify_level_eventlog=0
, @notify_level_email=0
, @notify_level_netsend=0
, @notify_level_page=0
, @delete_level=0
, @description=N'Cycles the SQL Server and SQL Server Agent errorlog if it contains data for more than 14 days'
, @category_name=N'Server Maintenance'
, @owner_login_name=N'sa'
, @job_id = @JobID output

/*Create jobstep within the job*/
EXEC msdb.dbo.sp_add_jobstep 
  @job_id=@JobID
, @step_name=N'Cycle Errorlogs'
, @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 @LogStartDate datetime

create table #result
( [LogDate] datetime
, [ProcessInfo] nvarchar(20)
, [Message] nvarchar(max)
);

insert into #result
EXEC sp_readerrorlog 0 ,1,''Microsoft SQL Server'';

select @LogStartDate = MIN(Logdate) 
from #result


if DATEDIFF(DAY,@LogStartDate,GETDATE()) > 14
begin
	exec sp_cycle_errorlog
	exec sp_cycle_agent_errorlog
end
'
, @database_name=N'msdb'
, @flags=0

/*Change the job to start at step 1*/
exec msdb.dbo.sp_update_job 
  @job_id = @JobID
, @start_step_id = 1


exec msdb.dbo.sp_add_jobserver 
  @job_id = @JobID
, @server_name = N'(local)'

/*Create a Job Schedule. every Saturday at 10AM*/
exec msdb.dbo.sp_add_jobschedule 
  @job_name=N'Cycle errorlogs'
, @name=N'CycleErrorLogs'
, @enabled=1
, @freq_type=8
, @freq_interval=64
, @freq_subday_type=1
, @freq_subday_interval=0
, @freq_relative_interval=0
, @freq_recurrence_factor=1
, @active_start_date=20130906
, @active_end_date=99991231
, @active_start_time=100000
, @active_end_time=235959
, @schedule_id = @ScheduleID output
