System Database Maintenance

As I said, this is a fully-functional SQL Server Agent job for System Database Maintenance.  Take a look at the @command lines for my working database reference, and be sure to change any directory paths and email recipients, to fit your environment.  Otherwise, just cut/paste this into your SSMS query window, and use it to create your maintenance routine.

USE [msdb]

GO

/****** Object:  Job [System_Database_Maintenance]    ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/***** Object:  JobCategory [[Uncategorized (Local)]]]   ******/

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'System_Database_Maintenance',

 @enabled=1,

 @notify_level_eventlog=2,

 @notify_level_email=0,

 @notify_level_netsend=0,

 @notify_level_page=0,

 @delete_level=0,

 @description=N'General system databse maintenance:

Database Integrity Check

Index Optimize

Backup Database

* Backup files (*.bak) are written here:  C:\MSSQL\Backup\databasename

* Execution log is date-stamped, and written here:  C:\MSSQL\JobLogs\

* DBA Operator is notified upon failure

',

 @category_name=N'[Uncategorized (Local)]',

 @owner_login_name=N'sa', @job_id = @jobId OUTPUT

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

/****** Object:  Step [User Database Integrity Check]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Database Integrity Check',

 @step_id=1,

 @cmdexec_success_code=0,

 @on_success_action=4,

 @on_success_step_id=2,

 @on_fail_action=4,

 @on_fail_step_id=5,

 @retry_attempts=0,

 @retry_interval=0,

 @os_run_priority=0, @subsystem=N'CmdExec',

 @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d dbawork -Q "EXECUTE [dbo].[usp_DatabaseIntegrityCheck] @Databases = ''SYSTEM_DATABASES''" -b',

 @output_file_name=N'C:\MSSQL\JobLogs\SystemDatabaseMaintenance_$(ESCAPE_SQUOTE(STRTDT)).txt',

 @flags=0

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

/****** Object:  Step [Optimize Indexes]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Optimize Indexes',

 @step_id=2,

 @cmdexec_success_code=0,

 @on_success_action=4,

 @on_success_step_id=3,

 @on_fail_action=4,

 @on_fail_step_id=5,

 @retry_attempts=0,

 @retry_interval=0,

 @os_run_priority=0, @subsystem=N'TSQL',

 @command=N'EXECUTE dbo.usp_IndexOptimize @Databases = ''SYSTEM_DATABASES'',

@FragmentationHigh_LOB = ''INDEX_REBUILD_OFFLINE'', @FragmentationHigh_NonLOB = ''INDEX_REBUILD_ONLINE'',

@FragmentationMedium_LOB = ''INDEX_REORGANIZE'', @FragmentationMedium_NonLOB = ''INDEX_REORGANIZE'',

@FragmentationLow_LOB = ''NOTHING'', @FragmentationLow_NonLOB = ''NOTHING'',

@FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @PageCountLevel = 1000,

@fillfactor = 80,@execute = ''n''',

 @database_name=N'dbawork',

 @output_file_name=N'C:\MSSQL\JobLogs\SystemDatabaseMaintenance_$(ESCAPE_SQUOTE(STRTDT)).txt',

 @flags=2

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

/****** Object:  Step [DatabaseBackup]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DatabaseBackup',

 @step_id=3,

 @cmdexec_success_code=0,

 @on_success_action=1,

 @on_success_step_id=4,

 @on_fail_action=4,

 @on_fail_step_id=5,

 @retry_attempts=0,

 @retry_interval=0,

 @os_run_priority=0, @subsystem=N'CmdExec',

 @command=N'sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d dbawork -Q "EXECUTE [dbo].[usp_DatabaseBackup] @Databases = ''SYSTEM_DATABASES'', @Directory = N''C:\MSSQL\Backup'', @BackupType = ''FULL'', @Compress = ''Y'', @Verify = ''Y'', @CheckSum = ''Y''" -b',

 @output_file_name=N'C:\MSSQL\JobLogs\SystemDatabaseMaintenance_$(ESCAPE_SQUOTE(STRTDT)).txt',

 @flags=2

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

/****** Object:  Step [Notification - Success]     ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Notification - Success',

 @step_id=4,

 @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 sp_send_dbmail

 @profile_name = ''yourProfilename'',

 @recipients = ''yourRecipientNames'',

 @blind_copy_recipients = ''blindCopyRecipientsIfYouHaveAny'',

 @subject = ''User Database Maintenance'',

 @body = ''servername  System Database Maintenance has completed successfully.''

',

 @database_name=N'msdb',

 @output_file_name=N'C:\MSSQL\JobLogs\SystemDatabaseMaintenance_$(ESCAPE_SQUOTE(STRTDT)).txt',

 @flags=2

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

/****** Object:  Step [Notification - Failure]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Notification - Failure',

 @step_id=5,

 @cmdexec_success_code=0,

 @on_success_action=2,

 @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 sp_send_dbmail

 @profile_name = ''yourProfilename'',

 @recipients = ''yourRecpientNames'',

 @blind_copy_recipients = ''blindCopyRecipientsIfYouHaveAny'',

 @subject = ''User Database Maintenance Failure'',

 @body = ''servername  System Database Maintenance has failed.''

',

 @database_name=N'msdb',

 @output_file_name=N'C:\MSSQL\JobLogs\SystemDatabaseMaintenance_$(ESCAPE_SQUOTE(STRTDT)).txt',

 @flags=2

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'System Database Maintenance 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=20101220,

 @active_end_date=99991231,

 @active_start_time=0100,

 @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