Defaults In msdb Database

Today is a day to discuss defaults. It started with the day being TSQL Tuesday and having a topic of “Say No to Defaults.” You can read more about that from the invite – here. I already participated in the party but did also want to discuss defaults a little bit more. That said, this article is not participating in the blog party. That would seem a bit silly.

While, this post is not a part of the party, the defaults to be discussed are fairly important. I have seen severe consequences due to these defaults being ignored and not changed. So today, in addition to my earlier article (you can read it here), I implore you to make some fundamental changes to your production servers with regards to various defaults.

A Trio of msdb Defaults

There aren’t really that many defaults within the msdb database that must be changed, are there? I mean, seriously, beyond the defaults that are available to every database, what could possibly be unique to this database that could have a severe consequence?

I am so glad you asked!

The defaults in the msdb database are more about what is missing than what is actually there. By default, this database is missing quite a few things that could be deemed critical to your environment.

Let’s start with an easy one – Indexes

There are a few out there that may disagree, but the proof really is in the effect on performance for backup jobs and such. I have three indexes I like to put on every instance. I have seen the implementation of these indexes aid in improved job times as well as aid in reduced time to “clean” up the database.

/* Number 1 *//* msdb maintenance activity related indexes */CREATE INDEX IX_maintplan_taskdetailid ON [msdb].[dbo].[sysmaintplan_logdetail]
([task_detail_id])
WITH (FILLFACTOR = 90);
CREATE INDEX IX_backupset_type ON msdb.dbo.backupset
([type],[backup_finish_date])
INCLUDE ([media_set_id], [database_name])
WITH (FILLFACTOR = 90);
CREATE INDEX IX_jobactivity_jobid ON [msdb].[dbo].[sysjobactivity]
([job_id])
INCLUDE ([session_id])
WITH (FILLFACTOR = 90);

Easy enough. These indexes are very straight forward and pretty small in the grand scheme of things. But if the index can help improve performance by a factor of 10, then I am in favor of them (and I have seen that performance gain).

Now that we have some supporting indexes to help a bit with performance, we should take a look at the next item. This one can help with job performance as well as help with keeping the msdb database nice and trim.

Data Pruning

I have walked into client instances that had backup history dating all the way back to 2005 and included two-three full backups a day per database with quarter-hourly log backups. Oh and this was for an instance containing well north of 200 databases. Can you say sluggish backups and sluggish msdb overall?

The fix is very easy! Not only do I recommend pruning the backup history, but also the job history, mail history and maintenance plan history (eew – if you use those things). Think about it – do you really need to know that Job XYZ ran successfully in 2006 and only took 15 seconds? This is 2015 and that kind of data is probably not pertinent at this point.

The pruning of this data is not enabled by default! You have to configure this for each of the servers under your purview. Luckily, this is easy to do!

/* NUMBER 2 *//****** Object:  Job [DBA - Clean msdb Historical data]    Script Date: 03/06/2013 17:40:02 ******/BEGIN TRANSACTION;
DECLARE @ReturnCode INT;
SELECT @ReturnCode = 0;
/****** Object:  JobCategory [DBA Maintenance]]    Script Date: 03/06/2013 17:40:03 ******/IF NOT EXISTS ( SELECT name
FROM msdb.dbo.syscategories
WHERE name = N'DBA Maintenance'
AND category_class = 1 )
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB',
@type = N'LOCAL', @name = N'DBA Maintenance';
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
END;
DECLARE @jobId BINARY(16);
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'DBA - Clean msdb Historical data',
@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'DBA Maintenance', @owner_login_name = N'sa',
@job_id = @jobId OUTPUT;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object:  Step [Purge Backup History]    Script Date: 03/06/2013 17:40:03 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
@step_name = N'Purge Backup History', @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 = 0,
@retry_interval = 0, @os_run_priority = 0, @subsystem = N'TSQL',
@command = N'/* Cleanup old backup history */DECLARE @BackupHistoryOlderThan DATETIME
Set @BackupHistoryOlderThan = DATEADD(d, -90, GETDATE())
EXECUTE msdb.dbo.sp_delete_backuphistory @BackupHistoryOlderThan
', @database_name = N'msdb', @flags = 4;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object:  Step [Purge MaintPlan Log Data]    Script Date: 03/06/2013 17:40:04 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
@step_name = N'Purge MaintPlan Log Data', @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'Declare @OldDate DateTime
Set @OldDate = GETDATE() - 14;
Execute msdb.dbo.sp_maintplan_delete_log @oldest_time = @OldDate',
@database_name = N'msdb', @flags = 4;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object:  Step [Purge Mail History]    Script Date: 03/06/2013 17:40:04 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
@step_name = N'Purge Mail History', @step_id = 3, @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'/* Cleanup old mail items */DECLARE @MailItemHistoryOlderThan DATETIME
Set @MailItemHistoryOlderThan = DATEADD(d, -14, GETDATE())
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @MailItemHistoryOlderThan
', @database_name = N'master', @flags = 0;
IF ( @@ERROR <> 0
OR @ReturnCode <> 0
)
GOTO QuitWithRollback;
/****** Object:  Step [Purge Job History]    Script Date: 03/06/2013 17:40:04 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId,
@step_name = N'Purge Job History', @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'/*Cleanup of Old Job History*/Declare @JobHistoryOlderThan DATETIME
Set @JobHistoryOlderThan = DATEADD(d, -14, GETDATE())
EXECUTE msdb.dbo.sp_purge_jobhistory @oldest_date = @JobHistoryOlderThan',
@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'Daily', @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 = 20120831, @active_end_date = 99991231,
@active_start_time = 0, @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

If you use this code sample, be sure to adjust the number of days shown in the retention to match your specific needs.

Now we have addressed a couple of defaults in msdb that can impact your performance. We are tidying up the database and in a much happier state these days. There is one more default, though, that is really critical to your data’s well being. This one is set within the msdb database but it really is for all of your databases!

Configuring Alerts!

I’m not talking about just any alerts. There are some very specific alerts that really should be configured. These are the alerts that can help you intervene to minimize corruption.

If you haven’t faced a problem with corruption – you will. It is only a matter of time. Corruption happens. When it happens, the earlier one can intervene, usually the better the outcome. Every minute counts, so why not try to reduce that time as much as possible?

This one is not terribly difficult to implement. I happen to have a query ready to go for that as well. All that needs to be done is a minor adjustment to the alert email address:

/* Number 3 */USE [msdb];
GO
IF EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 823' )
EXEC msdb.dbo.sp_delete_alert @name = N'DBA Alert: Message Id: 823';
GO
IF NOT EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 823' )
EXEC msdb.dbo.sp_add_alert @name = N'DBA Alert: Message Id: 823',
@message_id = 823, @severity = 0, @enabled = 1,
@delay_between_responses = 600, @include_event_description_in = 1,
@notification_message = N'I/O error detected. This could be a sign of impending I/O failure. Forward this ticket to the primary DBA for this account for followup.',
@category_name = N'[Uncategorized]',
@job_id = N'00000000-0000-0000-0000-000000000000';
GO
IF EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 824' )
EXEC msdb.dbo.sp_delete_alert @name = N'DBA Alert: Message Id: 824';
GO
IF NOT EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 824' )
EXEC msdb.dbo.sp_add_alert @name = N'DBA Alert: Message Id: 824',
@message_id = 824, @severity = 0, @enabled = 1,
@delay_between_responses = 600, @include_event_description_in = 1,
@notification_message = N'I/O error detected. This could be a sign of impending I/O failure. Forward this ticket to the primary DBA for this account for followup.',
@category_name = N'[Uncategorized]',
@job_id = N'00000000-0000-0000-0000-000000000000';
GO
IF EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 825' )
EXEC msdb.dbo.sp_delete_alert @name = N'DBA Alert: Message Id: 825';
GO
IF NOT EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 825' )
EXEC msdb.dbo.sp_add_alert @name = N'DBA Alert: Message Id: 825',
@message_id = 825, @severity = 0, @enabled = 1,
@delay_between_responses = 600, @include_event_description_in = 1,
@notification_message = N'I/O error detected. This could be a sign of impending I/O failure. Forward this ticket to the primary DBA for this account for followup.',
@category_name = N'[Uncategorized]',
@job_id = N'00000000-0000-0000-0000-000000000000';
GO
IF EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 829' )
EXEC msdb.dbo.sp_delete_alert @name = N'DBA Alert: Message Id: 829';
GO
IF NOT EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 829' )
EXEC msdb.dbo.sp_add_alert @name = N'DBA Alert: Message Id: 829',
@message_id = 829, @severity = 0, @enabled = 1,
@delay_between_responses = 600, @include_event_description_in = 1,
@notification_message = N'I/O error detected. This could be a sign of impending I/O failure. Forward this ticket to the primary DBA for this account for followup.',
@category_name = N'[Uncategorized]',
@job_id = N'00000000-0000-0000-0000-000000000000';
GO
IF EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 832' )
EXEC msdb.dbo.sp_delete_alert @name = N'DBA Alert: Message Id: 832';
GO
IF NOT EXISTS ( SELECT name
FROM msdb.dbo.sysalerts
WHERE name = N'DBA Alert: Message Id: 832' )
EXEC msdb.dbo.sp_add_alert @name = N'DBA Alert: Message Id: 832',
@message_id = 832, @severity = 0, @enabled = 1,
@delay_between_responses = 600, @include_event_description_in = 1,
@notification_message = N'Page memory error detected. This could be a sign of impending memory failure. Forward this ticket to the primary DBA for this account for followup.',
@category_name = N'[Uncategorized]',
@job_id = N'00000000-0000-0000-0000-000000000000';
GO
IF NOT EXISTS ( SELECT name
FROM msdb.dbo.sysoperators
WHERE name = N'DBA Support' )
EXEC msdb.dbo.sp_add_operator @name = N'DBA Support', @enabled = 1,
@weekday_pager_start_time = 90000, @weekday_pager_end_time = 180000,
@saturday_pager_start_time = 90000, @saturday_pager_end_time = 180000,
@sunday_pager_start_time = 90000, @sunday_pager_end_time = 180000,
@pager_days = 0, @email_address = N'support@DBA.com',
@category_name = N'[Uncategorized]';
GO
EXEC msdb.dbo.sp_add_notification @alert_name = N'DBA Alert: Message Id: 823',
@operator_name = N'DBA Support', @notification_method = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'DBA Alert: Message Id: 824',
@operator_name = N'DBA Support', @notification_method = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'DBA Alert: Message Id: 825',
@operator_name = N'DBA Support', @notification_method = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'DBA Alert: Message Id: 829',
@operator_name = N'DBA Support', @notification_method = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'DBA Alert: Message Id: 832',
@operator_name = N'DBA Support', @notification_method = 1;
GO

Wrap

Wow! Now there are three quick defaults that must be changed on every server. These defaults will help improve performance as well as help you stay on top of things when they start to go south (corruption). With timely notifications, and better performance, your servers will be happier, healthier, and longer lasting.

Thanks for reading! This has been another article in the Back to Basics series. You can read many more here.