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.

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!

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:

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.

4 Comments - Leave a comment
  1. You have set fill factor = 90. This makes your index 10% bigger than they need to be. Is this a good idea?

    • Jason Brimhall says:

      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.

  2. Carolyn Richardson says:

    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.

    • Jason Brimhall says:

      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.

Leave a Reply to Henrik Staun Poulsen Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.



Calendar
January 2019
M T W T F S S
« Dec   Feb »
 123456
78910111213
14151617181920
21222324252627
28293031  

Welcome , today is Sunday, November 17, 2019