Tables in the Master Database
Blitz Result: Tables in the Master Database
The master 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 master 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 master.sys.tables looking for tables where is_ms_shipped = 0.
To Fix the Problem
Review the contents of the tables. It’s possible that they were created during a deployment script that went horribly awry – especially since most logins use master as their default database. People forget to change databases when doing deployments, and they leave these little tables behind. 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 master database backups may run slightly faster.