I get to help with technical interviews every now and again, and one of the questions we always ask is “What are the system databases, and what are they for.” It’s a nice easy softball of a question. Or at least so I always thought until I started using it. It has amazed me how many people struggle with this. So for the record here is my answer. Note: This isn’t a complete description of each, this is my attempt (from memory) to answer the question. If I made any mistakes feel free to harass me in the comments. For more complete answers you can follow the links to BOL.

master

The master database contains information about what’s going on in the instance. For example the configurations, list of databases (user and system), and instance level permissions. Its location is determined by one of the startup parameters for SQL Server. The permissions for a lot of the server level code (stored procedures, functions, views etc) go here.

model

This is the template for any new databases created, including tempdb. This means that any changes to model will be reflected in any new databases created on the instance. Also if you want permissions and/or user objects in every new database you can put them in model. Be warned though, this also means they will show up in tempdb. Another effect of this is that a new database cannot be smaller than the model database when created.

msdb

You may have heard that Brent Ozar (b/t) calls tempdb the public toilet of SQL Server. Well to me, msdb is the public trash dump. This is where everything that doesn’t have a better home ends up. DTS packages used to go here, SSIS too (until SSISDB came along). Backup information, SQL Agent jobs, and their log information, etc are all found in msdb. You want to be careful here because the system objects found in msdb are treated more like user objects than anywhere else in SQL Server. You can make changes (and break things) pretty easily.

tempdb

This is the home for temp tables (both local and global), table variables, temporary stored procedures, cursors etc as well as any other form of work done by SQL Server that doesn’t happen in memory. Temporary internal objects to handle things like sorts and spools are also created here. Another big use for tempdb is row versioning.

Remember that tempdb is a resource for the entire instance so every query on that instance is using it. That can cause resource contention (which is why you see the best practice of multiple data files).

Bonus – mssqlsystemresource

This is a hidden system database. You can’t see it, and you can’t back it up normally. This is where the code for SQL Server is kept (sys.objects and sys.databases for example). These objects show up logically all through SQL Server and the permissions are put in the database where the objects appear to be. The main reason for this database is that it makes system updates easier.