SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

What are the system databases?

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...