By: Eric Blinn
Overview
Just like any other SQL Server database, the MSDB database can grow. In this section we look at growth and typical size of the MSDB database.
How should the database and transaction log growth settings be configured for the MSDB database?
Growth for MSDB database data and logs files should follow all of the same rules most user databases would use. MSDB acts a lot more like a low volume user database than the other system databases. File growth of 64MB is a good starting point. Try to right-size the log file so that it doesn’t need to grow. The right size for an instance will depend on many factors as seen in the next sections.
What is the typical size of the MSDB database?
This can vary greatly –so much so that there really isn’t a typical size. It is not uncommon to see MSDB at 50MB or 3000MB. MSDB holds some tables that can get very large in certain scenarios where on other instances the size might be negligible.
How big does the MSDB database get?
MSDB stores backup and restore history. Imagine a server with 2000 databases that takes a log backup of all of them every 15 minutes. That’s 192,000 new rows into the backupset and backupmediaset tables EVERY DAY! Keeping a few weeks or months of history can cause the size of MSDB to get very large.
In that same database if the backups are called by a SQL Server Agent job that means 96 job history rows just for this one job. Does that job store the output of every step and have no size limit?
Maybe that instance also stores a large series of SSIS packages within MSDB. That can eat up even more space and bring MSDB to several gigabytes in size.
Another server might have no SSIS packages, just 5 user databases and take hourly log backups on only 2 of them. On that instance the MSDB would be smaller by orders of magnitude.
