SQL Server System Databases

An Introduction to SQL Server System Databases:

SQL Server 2008/2005) contain 5 special databases:
1. master
2. model
3. tempdb
4. msdb
5. and mssql system resource.

These databases are used by SQL Server for its own maintenance and management.


Master Database:
----------------
The master database contains all of the system level information for SQL Server – all of the logins, linked servers, endpoints, and other system-wide configuration settings. The master database is also where SQL Server stores information about the other databases on this instance and the location of their files. If the master database is not present, SQL Server cannot start.

Always take regular backups of the master database.
Since the master database hold all of the information related to logins, endpoints, linked servers, and user databases, it’s important that you take a backup of the master database after configuring any of these server level changes. Otherwise, if your SQL Server suffers a catastrophic failure, those changes will be lost to the sands of time.

 “Do not create user objects in master. Otherwise, master must be backed up more frequently.”

Model Database:
---------------
The model database is used as a template whenever a new user database is created. You can change most database properties, create users, stored procedures, tables, views, etc – whatever you do will be applied to any new databases.
The nice thing is that you can create a guaranteed set of users, stored procedures, and options (including collation options) by making changes to the model database. Once those changes are in place, they are applied to every new database.
Outside of its role as a template, model doesn’t do anything else.

MSDB Database:
-----------------
msdb is used by the SQL Server Agent, database mail, Service Broker, and other services. If you aren’t actively working with things like jobs, alerts, log shipping, etc you can pretty safely ignore msdb… sort of.
One important item is that msdb holds backup history. Using the msdb tables (you can start by taking a look at msdb.dbo.backupset), it’s possible to determine when each database and filegroup was last backed up. This is very useful, especially when you’ve just started working at a new company or taken over the maintenance of new servers.
A word of warning: you need to make sure that you are pruning old backup history from msdb. Leaving old backup data can slow down the performance of backup and restore operations, the sp_delete_backuphistory system stored procedure is mentioned. This stored procedure will delete information older than the @oldest_date parameter. It is important that you are incredibly careful when using this store procedure and don’t attempt to delete all of the backup history data in your msdb at once. Attempting to clear out a large number of database backup history records in one fell swoop can have an adverse effect on performance. I suggest removing the data one to two days at a time.

Resource database:
-------------------
The resource database is a hidden system database. This is where system objects are stored. It isn’t possible to see the resource database by normal means. However you can see the data file by navigating to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn. The exact size and modification data of this file will be different from version to version, but the modified date should be the same date that you see when you run SELECT @@version.
It is best to think of the resource database as if it were another system DLL. The resource database is designed to make it easy for quick database upgrades. If new system objects are being put in place, it is only necessary to swap out the resource database MDF file.
Typically, the only way to view the contents of the resource database is using the OBJECT_DEFINITION system function.
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

-- on my test system this yields the following:
CREATE VIEW sys.objects AS
    SELECT name, OBJECT_ID, principal_id, schema_id, parent_object_id,
        type, type_desc, create_date, modify_date,
        is_ms_shipped, is_published, is_schema_published
    FROM sys.objects$


Tempdb Database:
----------------
We come, at last, to tempdb. Tempdb is the workhorse of the system databases. It is the workspace that SQL Server uses to store the intermediate results of query processing and sorting. You know how you see those spools in your execution plans? When you see one of those, SQL Server is probably spooling the data to a temporary table in the tempdb. Outside of storing temporary results, tempdb is also used during snapshot isolation and for user created temporary tables (this includes table variables).
One thing that is interesting to note about tempdb is that it is re-created every time the SQL Server service is started. Any objects that you have created in tempdb will be gone once the server restarts. If you want specific tables or stored procedures to always be available in tempdb, you will need to add them to the model database or else use a stored procedure to create them in tempdb when the SQL Server is started.
A properly sized and configured tempdb is vital for effective SQL Server performance. By default tempdb starts at 8MB in size and will continue to grow by ten percent until the drive is full. If the tempdb is sized too small, system resources will be used growing the tempdb file. This overhead can be avoided by increasing the default size of tempdb.