Tables in the Model Database
Blitz Result: Tables in the Model Database
The model database is a special system database – it’s the template that SQL Server uses whenever a new database is created. Anything in the model database will also be included in new databases as they’re created.
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 model.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 model database backups may run slightly faster.