Subscribe to the TST RSS Feed
Understanding Microsoft SQL Server 2005 System Databases
A database server application such as Microsoft SQL Server 2005 uses a lot of information in order to operate. Considering the purpose of a database is to store information, it only makes sense that SQL Server dips into its own technology for storing and accessing the information required for operating in a set of its own databases. Each of the databases used plays a specific role in the operation of the SQL Server. The system databases use the same components of databases created by users with: tables, views, stored procedures, and other database objects.
Microsoft SQL Server 2005 uses five system databases:
- master – uses master.mdf for data and masterlog.ldf for logging
- model – uses model.mdf for data and modellog.ldf for logging
- msdb – uses msdbdata.mdf for data and msdblog.ldf for logging
- resource – uses mssqlsystemresource.mdf for data and mssqlsystemresource.ldf for logging
- tempdb – uses tempdb.mdf for data and templog.ldf for logging
A default installation of SQL Server stores all the system databases under
Here’s a Closer Look at Each System Database …
The master Database
The master database stores all the system-level information for SQL Server. The data stored by the master database includes information for: configuration settings, logon accounts, linked servers and endpoints, user database file locations, and properties.
Due to the nature of the data stored, SQL Server cannot operate without the master database. So it is a very good idea to backup this database after changing the SQL Server configuration, modifying, adding, or removing any databases.
The model Database
SQL Server uses the model database for creating new databases. When the “create database” statement is used, SQL Server copies the contents of the model database to the newly created database. If there are any common database objects that would prove useful in all databases created by SQL Server, it is possible to add those objects to the model database. Then when a database is created by the SQL Server instance, the user defined objects will be copied to it along with the default objects. Since SQL Server recreates the tempdb database every time it is started, the model database is required in order for SQL Server to start.
The msdb Database
The msdb database is used by SQL Server to store information on operations performed by SQL Server. This includes information for: the SQL Server Agent, Database Mail, the Service Broker, log shipping, backup, and maintenance plan job parameters.
The resource Database
The resource system database was introduced with SQL Server 2005. This database is used for storing all the system views and stored procedures. Logically, each SQL Server database will contain all these system objects, however, they are physically stored within the resource database. The resource database is read-only and does not include any user data.
In previous versions of SQL Server, the system objects were stored in the master database. The motivation behind moving the objects to a separate database is to make updating the SQL Server more efficient. Improvements and fix-ups to the SQL Server system generally manifest mostly on the system objects. A separate database to store the system objects reduces the number of files that need to be replaced with an update.
The tempdb Database
As the name implies, SQL Server uses the tempdb database for storing temporary data and data objects. The tempdb database is used when an operation requires a temporary table, stored procedure, or other database object to be performed. Intermediary data for large sort operations is also stored in the tempdb database as well as temporary data for internal SQL Server operations.
Every time SQL Server is restarted, the tempdb system database is recreated thus clearing any temporary data stored during the last SQL Server session. In cases where a high volume of users and operations are performed with SQL Server the tempdb database can grow to use a significantly large amount of disk space. It is important to plan accordingly in these scenarios since running out of disk space where the tempdb database is stored will have catastrophic effects on the operation of SQL Server.
System Database Maintenance is Essential …
SQL Server’s system databases are crucial to the operation of SQL Server. If any of the system databases were to become corrupted, chances are SQL Server will no longer be able to function. For this reason it is just as important (if not more important) that you backup the system databases as regularly as the user databases. This applies to all the system databases except for the tempdb and resource databases.
Users are restricted from performing many operations on the system databases such as: changing the database owner, dropping the guest user, or mirroring. The databases cannot be dropped or set to offline, nor do the database files support renaming.
Related Posts:
3 Responses to “Understanding Microsoft SQL Server 2005 System Databases”
Leave A Comment:
Subscribe To Our eNewsletter
Peter Gonzalez Says:
August 17th, 2007 at 10:52 am
You’ve removed release date for the SQL 2005 training video. Do you have future plans to release it?
Nico Borger Says:
August 17th, 2007 at 11:36 am
I’m seeking input on SQL server 2005. Great article, usefull tagged links. A pitty that you don’t don’t have a SQL server 2005 training (yet?)
very helpfull Says:
September 11th, 2008 at 4:08 am
i am very find your websites that is very useful