-
-
Save potatoqualitee/e509e9f541eb406db7edd6e16a358026 to your computer and use it in GitHub Desktop.
export jobs
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
/* | |
Created by BASE\ctrlb using dbatools Export-DbaScript for objects on sql2017 at 04/01/2018 21:58:36 | |
See https://dbatools.io/Export-DbaScript for more information | |
*/ | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'CommandLog Cleanup', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'CommandLog Cleanup', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "DELETE FROM [dbo].[CommandLog] WHERE StartTime < DATEADD(dd,-30,GETDATE())" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\CommandLogCleanup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
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'Daily Full Backups', | |
@enabled=1, | |
@notify_level_eventlog=0, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'No description available.', | |
@category_name=N'[Uncategorized (Local)]', | |
@owner_login_name=N'BASE\ctrlb', @job_id = @jobId OUTPUT | |
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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseBackup - SYSTEM_DATABASES - FULL', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'DatabaseBackup - SYSTEM_DATABASES - FULL', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''SYSTEM_DATABASES'', @Directory = N''\\nas\sql\backups'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = 24, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseBackup - USER_DATABASES - DIFF', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'DatabaseBackup - USER_DATABASES - DIFF', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''\\nas\sql\backups'', @BackupType = ''DIFF'', @Verify = ''Y'', @CleanupTime = 24, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseBackup - USER_DATABASES - FULL', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'DatabaseBackup - USER_DATABASES - FULL', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''\\nas\sql\backups'', @BackupType = ''FULL'', @Verify = ''Y'', @CleanupTime = 24, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseBackup - USER_DATABASES - LOG', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'DatabaseBackup - USER_DATABASES - 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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = ''USER_DATABASES'', @Directory = N''\\nas\sql\backups'', @BackupType = ''LOG'', @Verify = ''Y'', @CleanupTime = 24, @CheckSum = ''Y'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseIntegrityCheck - SYSTEM_DATABASES', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'DatabaseIntegrityCheck - SYSTEM_DATABASES', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ''SYSTEM_DATABASES'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseIntegrityCheck_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DatabaseIntegrityCheck - USER_DATABASES', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'DatabaseIntegrityCheck - USER_DATABASES', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ''USER_DATABASES'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseIntegrityCheck_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
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'Hourly Log Backups', | |
@enabled=1, | |
@notify_level_eventlog=0, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'No description available.', | |
@category_name=N'[Uncategorized (Local)]', | |
@owner_login_name=N'BASE\ctrlb', @job_id = @jobId OUTPUT | |
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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IndexOptimize - USER_DATABASES', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'IndexOptimize - USER_DATABASES', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize] @Databases = ''USER_DATABASES'', @LogToTable = ''Y''" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\IndexOptimize_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
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'Job One', | |
@enabled=1, | |
@notify_level_eventlog=0, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'No description available.', | |
@category_name=N'[Uncategorized (Local)]', | |
@owner_login_name=N'BASE\ctrlb', @job_id = @jobId OUTPUT | |
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 | |
COMMIT TRANSACTION | |
GOTO EndSave | |
QuitWithRollback: | |
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION | |
EndSave: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Output File Cleanup', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'Output File Cleanup', | |
@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'CmdExec', | |
@command=N'cmd /q /c "For /F "tokens=1 delims=" %v In (''ForFiles /P "$(ESCAPE_SQUOTE(SQLLOGDIR))" /m *_*_*_*.txt /d -30 2^>^&1'') do if EXIST "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v echo del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v& del "$(ESCAPE_SQUOTE(SQLLOGDIR))"\%v"', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\OutputFileCleanup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Checkup' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Checkup' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Replication agents checkup', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Detects replication agents that are not logging history actively.', | |
@category_name=N'REPL-Checkup', | |
@owner_login_name=N'BASE\ctrlb', @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'Run agent.', | |
@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'sys.sp_replication_agent_checkup @heartbeat_interval = 10', | |
@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_jobschedule @job_id=@jobId, @name=N'Replication agent schedule.', | |
@enabled=1, | |
@freq_type=4, | |
@freq_interval=1, | |
@freq_subday_type=4, | |
@freq_subday_interval=10, | |
@freq_relative_interval=1, | |
@freq_recurrence_factor=0, | |
@active_start_date=20160514, | |
@active_end_date=99991231, | |
@active_start_time=0, | |
@active_end_time=235959, | |
@schedule_uid=N'51ac5c44-82b3-4c4d-a835-32984fe8ef8a' | |
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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'sp_delete_backuphistory', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'sp_delete_backuphistory', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_delete_backuphistory @oldest_date = @CleanupDate" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\sp_delete_backuphistory_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
BEGIN TRANSACTION | |
DECLARE @ReturnCode INT | |
SELECT @ReturnCode = 0 | |
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1) | |
BEGIN | |
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance' | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
END | |
DECLARE @jobId BINARY(16) | |
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'sp_purge_jobhistory', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Source: https://ola.hallengren.com', | |
@category_name=N'Database Maintenance', | |
@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'sp_purge_jobhistory', | |
@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'CmdExec', | |
@command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d msdb -Q "DECLARE @CleanupDate datetime SET @CleanupDate = DATEADD(dd,-30,GETDATE()) EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate" -b', | |
@output_file_name=N'$(ESCAPE_SQUOTE(SQLLOGDIR))\sp_purge_jobhistory_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt', | |
@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: | |
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'SSIS Server Maintenance Job', | |
@enabled=1, | |
@notify_level_eventlog=2, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'Runs every day. The job removes operation records from the database that are outside the retention window and maintains a maximum number of versions per project.', | |
@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'SSIS Server Operation Records Maintenance', | |
@step_id=1, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=2, | |
@on_fail_step_id=0, | |
@retry_attempts=3, | |
@retry_interval=3, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N' | |
DECLARE @role int | |
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] =''SSISDB'') | |
IF DB_ID(''SSISDB'') IS NOT NULL AND (@role IS NULL OR @role = 1) | |
EXEC [SSISDB].[internal].[cleanup_server_retention_window]', | |
@database_name=N'msdb', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSIS Server Max Version Per Project Maintenance', | |
@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=3, | |
@retry_interval=3, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N' | |
DECLARE @role int | |
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] =''SSISDB'') | |
IF DB_ID(''SSISDB'') IS NOT NULL AND (@role IS NULL OR @role = 1) | |
EXEC [SSISDB].[internal].[cleanup_server_project_version]', | |
@database_name=N'msdb', | |
@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'SSISDB Scheduler', | |
@enabled=1, | |
@freq_type=4, | |
@freq_interval=1, | |
@freq_subday_type=1, | |
@freq_subday_interval=0, | |
@freq_relative_interval=0, | |
@freq_recurrence_factor=0, | |
@active_start_date=20001231, | |
@active_end_date=99991231, | |
@active_start_time=0, | |
@active_end_time=120000, | |
@schedule_uid=N'7469eccf-0a71-4cbc-a089-cb0167efe6e8' | |
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: | |
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'syspolicy_purge_history', | |
@enabled=1, | |
@notify_level_eventlog=0, | |
@notify_level_email=0, | |
@notify_level_netsend=0, | |
@notify_level_page=0, | |
@delete_level=0, | |
@description=N'No description available.', | |
@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'Verify that automation is enabled.', | |
@step_id=1, | |
@cmdexec_success_code=0, | |
@on_success_action=3, | |
@on_success_step_id=0, | |
@on_fail_action=1, | |
@on_fail_step_id=0, | |
@retry_attempts=0, | |
@retry_interval=0, | |
@os_run_priority=0, @subsystem=N'TSQL', | |
@command=N'IF (msdb.dbo.fn_syspolicy_is_automation_enabled() != 1) | |
BEGIN | |
RAISERROR(34022, 16, 1) | |
END', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Purge history.', | |
@step_id=2, | |
@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 msdb.dbo.sp_syspolicy_purge_history', | |
@database_name=N'master', | |
@flags=0 | |
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback | |
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Erase Phantom System Health Records.', | |
@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'PowerShell', | |
@command=N'if (''$(ESCAPE_SQUOTE(INST))'' -eq ''MSSQLSERVER'') {$a = ''\DEFAULT''} ELSE {$a = ''''}; | |
(Get-Item SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))$a).EraseSystemHealthPhantomRecords()', | |
@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_jobschedule @job_id=@jobId, @name=N'syspolicy_purge_history_schedule', | |
@enabled=1, | |
@freq_type=4, | |
@freq_interval=1, | |
@freq_subday_type=1, | |
@freq_subday_interval=0, | |
@freq_relative_interval=0, | |
@freq_recurrence_factor=0, | |
@active_start_date=20080101, | |
@active_end_date=99991231, | |
@active_start_time=20000, | |
@active_end_time=235959, | |
@schedule_uid=N'c0a7efb2-8887-41af-b668-9dd45f243206' | |
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: |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment