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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/* 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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 |
/* 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 |
/* 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.
You have set fill factor = 90. This makes your index 10% bigger than they need to be. Is this a good idea?
Yes it is a good idea. A fillfactor of 100 is great if the data in the table is always static / never changing. You will want to allow room for new rows to be added without causing unwanted page splits.
90% is not a good idea if the tables primary key is an identity as data then is always going to be added at the end of a data page so your just slowing down reads as you will be pulling back more data pages from your query. same as your writing to more unnecessary data pages.
Actually that is more of an “it depends” rather than the rule. Read these debunked myths which I am sure contributes to your statement.
https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2530-fill-factor/
In addition, the comment about the PK on an identity is not absolute either. My PK may or may not be a clustered index. The PK is irrelevant to the fillfactor as the CI is what matters for the sort and data fill of an individual index. That said, the CI does not force NCIs to be physically sorted and thus the argument about fillfactor and Identity columns is irrelevant. The NCI will insert in the middle, the end, the beginning or where it sees fit depending on the definition of the NCI and the data being inserted. A fillfactor setting of other than 100% is very beneficial for NCIs. And certainly in the case of the indexes in these examples – the fillfactor is extremely beneficial.