Created
March 2, 2018 12:47
-
-
Save tech-zombie/c5d29d1509cdfde8e92326dacb1adf0e to your computer and use it in GitHub Desktop.
weekly sql error log rotation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE [msdb] | |
GO | |
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=N'DBA - Recycle SQL Server Agent Error Logs', | |
@enabled=1, | |
@notify_level_eventlog=0, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'This job will Recycle SQL Server Agent Error Logs once every week | |
on Sunday at 12:00:00 AM.', | |
@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'Recycle SQL Server Agent Error Log', | |
@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'Use MSDB | |
GO | |
EXEC dbo.sp_cycle_agent_errorlog | |
GO', | |
@database_name=N'master', | |
@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_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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment