SQL Server 2005 System Databases

The System Database is the first folder within the Database tree. It consists of all the system databases that make up SQL server 2005. The system databases consist of:

  • Master Database – The master database is an important system database in SQL Server 2005. It stores all the system-level data, including system configuration settings, login information, disk space, stored procedure, linked servers, existence of other databases and location of those, along with other critical information. SQL Server cannot start if the master database is unavailable.
  • Model DatabaseThe model database serve as the template for creating new databases in SQL Server 2005. The tempdb is recreated with the help of model database every time SQL Server is started, so model database should always exist on a SQL Server system.
  • Msdb Database – The msdb database is used mostly by the SQL Server Agent. It stores alerts, schedule jobs, and operations. In addition, it stores historical information about backups and restores, SQL Mail, and Service Broker.
  • Tempdb – It is workspace to hold temporary information, including tables, stored procedures, and objects, and intermediate result sets. Each time SQL server is started, the tempdb database starts with a clear copy.

This article has been inspired by ‘Microsoft SQL Server 2005 Management and Administration’ by Ross Mistry.

Also refer http://msdn.microsoft.com/en-us/library/ms178028%28SQL.90%29.aspx

Advertisements

2 responses to this post.

  1. Great overview of system databases.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: