What are the system databases?

Kenneth Fisher, 2018-05-21

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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads