Most DBAs have at least some idea what the system databases are for. Master has the list of databases & logins, msdb is jobs and backups, tempdb is for temp tables and sorting. Obviously there is a lot more to each of these, but that is the absolute basics, and what a fair number of newer DBAs know. The one I most frequently get questions about though, is what is the model database used for? No one ever goes to it to run a query, it seems to just kind of sit there. So what is it for?

The model database is just what it sounds like. It is the model for all new databases created in the instance. This has several ramifications. The simplest of which is that objects (tables, users, stored procedures, functions etc.) that are placed in the model database will show up in every new database created. I should make a note that the properties of the model database are of course also included, but there is a long list of exclusions on what can be changed. This can be handy but needs to be done with care because of the second ramification of the model database being used for all new databases. The model database is used to create tempdb every time the instance is re-started. This means that if you clutter up your model database, you are also cluttering up the tempdb database. It also means that if you end up corrupting the model database (I’ve done this) then your instance won’t start up until you fix it.

The ways I know to fix a corrupted model database are as follows:

Easiest and best method: if your instance is still up, restore it from backup. This can be done just like restoring a user database.

If the instance is no longer up, then the “best” method is to use the REBUILDDATABASE option of setup. Getting back to where you were is a bit complicated though. Here is the BOL link for 2012. It appears to be similar if not the same as the 2008 R2 BOL: http://msdn.microsoft.com/en-us/library/dd207003.aspx.

Last but not least, is to restore your corrupted model database on another instance to an alternate location, model_fix for example, then detach it and copy over the corrupted version.  This probably isn’t a recommended approach but I’ve never had a problem with it myself.

While writing this I did a few tests just to make sure I knew what I was talking about. (This is always a good idea when a lot of people who are known for expecting accuracy are going to be reading what you wrote.) Here is the test I made to check that objects created in model do in fact show up in tempdb.

First get a list of all of the user objects that are in the model database.

USE model
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

I’m using the IsMSShipped property to exclude objects created by the install. I talked about it here. http://sqlstudies.com/2013/01/23/ismsshipped/

Next check tempdb.

USE tempdb
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

In my case I don’t have any user objects in my model database so I’m going to add one.

USE model
GO
CREATE TABLE TableInModel (
Column1 int,
Column2 varchar(30)
)

Confirm that the object is in the sys.objects table.

USE model
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

Next restart the instance.

Then we run our checks again.

USE model
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0
GO
USE tempdb
GO
SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

Note this time the table exists in tempdb just like it does in model. In fact it even has the same object_id and crdate.