What are the system databases?5
May 21, 2018 by Kenneth Fisher
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.
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.
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.
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.
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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, System Databases | Tags: microsoft sql server, system databases
5 thoughts on “What are the system databases?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
No mention of distribution databases?
Any database that is flagged as is_distributor in the sys.databases table on master. Used for storing metadata and history data for all types of replication, and transactions for transactional replication from one server/database (publisher) to another (subscriber). Typically only one named distributor, but it can be renamed and there can be multiple distribution databases if more than one publisher is using the same distribution server.
Fair enough. I came up with 10 for my system databases die (including SSISDB, distributor, reportserver, reportservertempdb, and mdw) but I was only hitting the top 5 here 🙂
Ahhh, you stole all my fun. I was starting in alphabetical order and was going to work through that same list. 😉
Keep up the great articles, I always enjoy the read.
Glad to hear it 🙂 And you know, nothing says you couldn’t do your own blog “System Databases that Kenneth missed” or something 😀
[…] heard of tempdb right? It’s one of the system databases. All of the system databases are important but poor tempdb gets beaten on more than any of the […]