MS SQL Server System Databases

There are four visible system databases and one is hidden system database.

Visible Databases:

  1. Master
  2. Model
  3. MSDB
  4. TempDB

Capture

Hidden Database:

  1. Resource Database

All system databases are important and play a distinct role, the description is as below:

Master Database: The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Model Database: The model database is used as the template for all databases created on an instance of SQL Server. The entire contents of the model database, including database options, are copied to the new database.  Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system. Newly created user databases use the same recovery model as the model database.

MSDB Database: The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb. By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb.

TempDB: The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server. It is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Resource Database: The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\.

Advertisements

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Database Administration, System databases and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s