Use of Default Database in SQL SERVER

SQL Server 2008 (and 2005) contain five special databases: master, model, tempdb, msdb, and mssqlsystemresource (aka Resource). These databases are used by SQL Server for its own maintenance and management.

master

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.

Books Online contains a great deal of helpful information about the master database that you can find in the appropriately named master Database article.

Speaking from personal experience, one of the most important points from that article is this: “Do not create user objects in master. Otherwise, master must be backed up more frequently.” I cannot tell you the number of times I have connected to a development server, opened up a file, and run the DDL contents of the file only to realize that I have created new tables on the master database because I did not have a USE AdventureWorks at the top of my DDL script.

model

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. You can find more information in the model Database article of Books Online.

msdb

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. Brent Ozar documented this during his Backup Tricks Week article Checking MSDB Cleanups. In this article, 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. It’s tedious, I know, but your users will thank you.

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

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.

master Database	Records all the system-level information for an instance of SQL Server.
msdb Database	Is used by SQL Server Agent for scheduling alerts and jobs.
model Database	Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
Resource Database	Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb Database	Is a workspace for holding temporary o