Saturday, January 1, 2011

User Database Maintenance

As I said, this is a fully-functional SQL Server Agent job for User 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 [User_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'User_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 user 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'User 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 = ''USER_DATABASES''" -b',
@output_file_name=N'C:\MSSQL\JobLogs\UserDatabaseMaintenance_$(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 = ''USER_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\UserDatabaseMaintenance_$(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 = ''USER_DATABASES'', @Directory = N''C:\MSSQL\Backup'', @BackupType = ''FULL'', @Compress = ''Y'', @Verify = ''Y'', @CheckSum = ''Y''" -b',
@output_file_name=N'C:\MSSQL\JobLogs\UserDatabaseMaintenance_$(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 = ''yourBlindCopyRecipientsIfYouHaveAny'',
  @subject = ''User Database Maintenance'',
  @body = ''servername  User Database Maintenance has completed successfully.''
',
@database_name=N'msdb',
@output_file_name=N'C:\MSSQL\JobLogs\UserDatabaseMaintenance_$(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 = ''yourRecipientNames'',
  @blind_copy_recipients = ''yourBlindCopyRecipientsIfYouHaveAny'',
  @subject = ''User Database Maintenance Failure'',
  @body = ''servername  User Database Maintenance has failed.''
',
@database_name=N'msdb',
@output_file_name=N'C:\MSSQL\JobLogs\UserDatabaseMaintenance_$(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'User 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=3000,
@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

2 comments:

  1. I think its very handy to have scripts like this.
    Here are a few other items I would incorporate into my maint scripts (as well as the items you noted).

    Free ad hoc SQl cache
    update database statistics





    Database Integrity Check
    Index Optimize
    Backup Database

    ReplyDelete
  2. You are right, Laurence. I do have the jobs for the statistics update. I will try to get it up here as well.

    ReplyDelete