SQL Server System and Database Administration : System Databases

4/2/2011 3:39:26 PM

System Administrator Responsibilities

A system administrator is responsible for the integrity and availability of the data in a database. This is a simple concept, but it is a huge responsibility. Some large corporations place a valuation on their data as high as $1 million per 100MB. The investment in dollars is not the only issue; many companies that lose mission-critical data simply never recover.

Job descriptions for system administrators vary widely. In small shops, the administrator might lay out the physical design, install SQL Server, implement the logical design, tune the installation, and then manage ongoing tasks, such as backups. At larger sites, tasks might be broken out into separate job functions. Managing users and backing up data are common examples. However, a lead administrator should still be in place to define policy and coordinate efforts.

Whether performed by an individual or as a team, the core administration tasks are as follows:

  • Install and configure SQL Server.

  • Plan and create databases.

  • Manage data storage.

  • Control security.

  • Tune the database.

  • Perform backup and recovery.

Another task sometimes handled by administrators is managing stored procedures. Because stored procedures for user applications often contain complex Transact-SQL (T-SQL) code, they tend to fall into the realm of the application developer. However, because stored procedures are stored as objects in the database, they are also the responsibility of the administrator. If an application calls custom stored procedures, the system administrator must be aware of this and coordinate with the application developers.

The system administration job can be stressful, frustrating, and demanding, but it is a highly rewarding, interesting, and respected position. As a system administrator, you are expected to know all, see all, and predict all, but you should be well compensated for your efforts.

System Databases

SQL Server uses system databases to support different parts of the database management system (DBMS). Each database plays a specific role and stores information that SQL Server needs to do its job. The system databases are much like the user databases created in SQL Server. They store data in tables and contain the views, stored procedures, and other database objects that you also see in user databases. They also have associated database files (that is, .mdf and .ldf files) that are physically located on the SQL Server machine. Table 1 lists system databases and their related database filenames.

Table 1. System Databases and Their Associated Database Files
Database.mdf Filename.ldf Filename


You can use the sys.master_files catalog view to list the physical locations of the system database files as well as the user database files. This catalog view contains a myriad of information, including the logical name, current state, and size of each database file.

The folder where each of these database files is located depends on the SQL Server installation. By default, the installation process places these files in a folder named <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. You can move these files after the installation by using special procedures that are documented in the SQL Server Books Online topic “Moving System Databases.”

The following sections describe the function of each system database.

The master Database

The master database contains server-wide information about the SQL Server system. This server-wide information includes logins, linked server information, configuration information for the server, and information about user databases created in the SQL Server instance. The actual locations of the database files and key properties that relate to each user database are stored in the master database.

SQL Server cannot start without a master database. This is not surprising, given the type of information that it contains. Without the master database, SQL Server does not know the location of the databases that it services and does not know how the server is configured to run.

The resource Database

The resource database contains all the system objects deployed with SQL Server 2008. These system objects include the system stored procedures and system views that logically appear in each database but are physically stored in the resource database. Microsoft moved all the system objects to the resource database to simplify the upgrade process. When a new release of the software is made available, upgrading the system objects is accomplished by simply copying the single resource database file to the local server. Similarly, rolling back an upgrade only requires overwriting the current version of the resource database with the older version.

You do not see the resource database in the list of system databases shown in SQL Server Management Studio (SSMS). You also cannot add user objects to the resource database. For the most part, you should not be aware of the existence of the resource database. It has database files named mssqlsystemresources.mdf and mssqlsystemresources.ldf that are located in the Binn folder, but you cannot access the database directly. In addition, you do not see the database listed when selecting databases using system views or with system procedures, such as sp_helpdb.

The model Database

The model database is a template on which all user-created databases are based. All databases must contain a base set of objects known as the database catalog. When a new database is created, the model is copied to create the requisite objects. Conveniently, objects can be added to the model database. For example, if you want a certain table created in all your databases, you can create the table in the model database, and it is then propagated to all subsequently created databases.

The msdb Database

The msdb database is used to store information for the SQL Server Agent, the Service Broker, Database Mail, log shipping, and more. When you create and schedule a SQL Server Agent job, the job’s parameters and execution history are stored in msdb. Backups and maintenance plan information are stored in msdb as well. If log shipping is implemented, critical information about the servers and tables involved in this process is stored in msdb.

The distribution Database

The distribution database, utilized during replication, stores metadata and history information for all types of replication. It is also used to store transactions when transactional replication is utilized. By default, replication is not set up, and you do not see the distribution database listed in SSMS. However, the actual data files for the distribution database are installed by default.

The tempdb Database

The tempdb database stores temporary data and data objects. The temporary data objects include temporary tables, temporary stored procedures, and any other objects you want to create temporarily. The longevity of data objects in the temporary database depends on the type of object created. Ultimately, all temporary database objects are removed when the SQL Server service is restarted. The tempdb database is re-created, and all objects and data added since the last restart of SQL Server are lost.

tempdb can also be used for some of SQL Server’s internal operations. Large sort operations are performed in tempdb before the result set is returned to the client. Certain index operations can be performed in tempdb to offload some of the space requirements or to spread I/O. SQL Server also uses tempdb to store row versions that are generated from database modifications in databases that use row versioning or snapshot isolation transactions.

Maintaining System Databases

You should give system databases the same attention that you give your user databases. These databases should be backed up on a regular basis and secured in the event that one of them needs to be restored. All the system databases, with the exception of tempdb and resource, can be backed up. These same databases can also be restored to bring them back to a previous state.


Although you cannot back up the resource database using SQL Server’s BACKUP and RESTORE commands, you can make a backup copy of it by performing a file-based or disk-based backup of the mssqlsystemresource.mdf file (SQL Server must not be running at the time). Likewise, you can manually restore a backup copy of the mssqlsystemresource.mdf file only when SQL Server is not running. You must be careful not to overwrite the current resource database with a version for a different release level of SQL Server.

It’s important that you monitor the size of your system databases. The amount of data that accumulates in these databases can be significant. This is particularly true for the tempdb, msdb, and distribution databases. Large sort or index operations can increase the size of your tempdb database in a short period of time. The msdbdistribution databases contain a great deal of historical information. Consider, for example, a server with hundreds of databases that have log backups occurring every 15 minutes. The information captured for each individual backup is not significant, but the total number of databases and frequency of the backups cause many rows to be stored in the msdb database. Cleanup tasks and similar activities that remove older historical data can help keep the database size manageable. and

  •  SQL Server 2008 : Monitoring Your Server - Monitoring Your CPU
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 3) - XML DML & Converting a Column to XML
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 2) - SQL Server 2005 XQuery in Action
  •  Programming Microsoft SQL Server 2005 : Querying XML Data Using XQuery (part 1) - XQuery Defined
  •  SQL Server 2008 : Monitoring Your Server - Familiarizing Yourself with the Performance Monitor
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 2) - FOR XML EXPLICIT
  •  Programming Microsoft SQL Server 2005 : FOR XML Commands (part 1) - FOR XML RAW & FOR XML AUTO
  •  SQL Server 2008 : Audit-Related Startup and Shutdown Problems
  •  SQL Server 2008 : Creating SQL Server Audits Using the GUI
  •  SQL Server 2008 : Creating Database Audit Specifications
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 3) - XML Indexes
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 2) - XML Schemas
  •  Programming Microsoft SQL Server 2005 : The XML Data Type (part 1) - Working with the XML Data Type as a Variable & Working with XML in Tables
  •  SQL Server 2008 : Auditing SQL Server - Creating Server Audit Specifications
  •  SQL Server 2008 : Auditing SQL Server - Creating SQL Server Audits with T-SQL
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - Security
  •  Programming Microsoft SQL Serve 2005 : An Overview of SQL CLR - CLR Aggregates
  •  SQL Server 2008: Monitoring Resource Governor
  •  SQL Server 2008: Managing Resources with the Resource Governor (part 3) - Classifier Function
    Top 10
    Exchange Server 2010 : Active Manager - Automatic database transitions & Best copy selection
    Exchange Server 2010 : Breaking the link between database and server
    iPhone 3D Programming : Drawing an FPS Counter (part 2) - Rendering the FPS Text
    iPhone 3D Programming : Drawing an FPS Counter (part 1) - Generating a Glyphs Texture with Python
    Mobile Application Security : Mobile Geolocation - Geolocation Methods & Geolocation Implementation
    Mobile Application Security : SMS Security - Application Attacks & Walkthroughs
    Transact-SQL in SQL Server 2008 : Table-Valued Parameters
    Transact-SQL in SQL Server 2008 : New date and time Data Types and Functions
    Windows 7 : Working with User Accounts (part 2)
    Windows 7 : Working with User Accounts (part 1)
    Most View
    Collaborating Within an Exchange Server Environment Using Microsoft Office SharePoint Server 2007 : Understanding the History of SharePoint Technologies
    Handling User Interaction and Events in XAML
    SQL Azure : Managing a Shard (part 1) - Managing Exceptions & Managing Performance
    Working with Logon and Startup Policies in Vista
    SharePoint 2010 : Workflow Modeling and Development Tools (part 2) - Visual Studio 2010 & Workflow Modeling Tools Comparison
    Programming the Mobile Web : Widgets and Offline Webapps - Platforms (part 4) - Windows Mobile & BlackBerry
    Programming with DirectX : Game Input - Win32 Input
    Visual Studio 2010 : Understanding Solutions and Projects (part 1)
    New Wireless Improvements in Vista
    Algorithms for Compiler Design: THE LR PARSER
    Documenting an Exchange Server 2010 Environment : Exchange Server 2010 Project Documentation
    Algorithms for Compiler Design: STRAIGHTFORWARD CODE GENERATION
    Windows 7 : Navigating the Computer Security Maze
    Implement an Observer (aka Subscriber) Pattern
    Optimizing for Vertical Search : Optimizing for Image Search (part 2) - Optimizing Through Flickr and Other Image Sharing Sites
    Exchange Server 2010 : Installing OCS 2007 R2 (part 3) - Configuring Prerequisites & Deploying an OCS 2007 Server
    Windows 7 : Mapping Your Networking Infrastructure (part 1) - Using the Network and Sharing Center
    Windows Server 2008 : Securing Internet Information Services 7.5
    Windows Phone 7 Development : Internationalization - Storing and Retrieving Current Culture Settings
    Programming Symmetrical Encryption (part 3) - Encrypting and Decrypting Data