Tables in the MSDB Database
Blitz Result: Tables in the MSDB Database
The MSDB database is a system database – it’s not really for you to poke around in and store stuff. We usually design different backup & recovery processes for the MSDB database as opposed to user databases.
While it might be okay to put stored procedures like sp_WhoIsActive or sp_Blitz in the master database, we’d recommend against saving data there in tables. This part of our SQL Server sp_Blitz script checks msdb.sys.tables looking for tables where is_ms_shipped = 0.
To Fix the Problem
Review the contents of the tables. Check with your users and developers to make sure they’re not in use. Back up the database, then rename the tables. Wait a week or two to make sure no one complains, and then drop the tables – just hang on to that backup just in case.
If they’re your own tables, like for DBA utility tools, consider building a separate database called DBAtools where your data is stored. You can leave it in simple mode and avoid backups if it’s truly throwaway data that you can live without.
After this change, your MSDB database backups may run slightly faster.
5 Comments.
This section reports the DTA_ tables which are created by the Database Tuning Advisor. You might want to exclude those.
Jason – actually, I don’t think those tables should be in MSDB either. I’m a big fan of dropping those. I don’t want to continually back those tables up every day.
Brent,
Great utility. When I ran it I got this listing of tables from msdb. They are all shown as user tables in sys.tables but appear to have been installed with SQL 2008 SP2 to support Data Tier Application (DAC):
sysdac_history_internal
sysdac_instances_internal
sysutility_mi_configuration_internal
sysutility_mi_cpu_stage_internal
sysutility_mi_dac_execution_statistics_internal
sysutility_mi_session_statistics_internal
sysutility_mi_smo_objects_to_collect_internal
sysutility_mi_smo_properties_to_collect_internal
sysutility_mi_smo_stage_internal
sysutility_mi_volumes_stage_internal
Thanks again,
Lee
Lee – thanks! I’m going to leave those in the checks for now – I would want to know about those if I was the DBA.
Agreed, so would I. I do find it odd though that a service pack adds tables to a system database but marks them as user tables. Oh well, if that is the weirdest thing I see this month, I’m doing great. Thanks again for a truly useful utility.