RSS Feeds

Article Topics

All Articles

Performance Tuning

Audit

Business Intelligence

Clustering

Reporting Services

SQL Azure

Developer

General DBA

ASP.NET / ADO.NET

SQL Azure

USEFUL SITES :

ASP.NET Tutorials
Windows and SQL Azure Tutorials
Cloud Hosting Magazine
SharePoint Tutorials
Windows Server Help

Write for Us

Share your SQL Server knowledge with others and raise your profile in the community More...

System Databases in SQL Server

By : Ashish Kumar Mehta
Oct 15, 2008

Being a SQL Server Database Administrator one needs to have a sound knowledge of System Databases in SQL Server. System databases are an integral part of the SQL Server product as it depends on the System Databases to function. Having a good knowledge of System Databases will help the Database Administrator to perform day-to-day tasks effectively. Hence, this article covers the System Databases of SQL Server 2005 & 2008 versions.

System Database in SQL Server 2005 & 2008 Versions
Master Database
The Master database is the heart and soul of SQL Server. It basically records all the system level information. Every instance of SQL Server will have an independent Master database; as it captures instance level configuration information. The information which is captured in the Master database includes SQL Server instance level configurations, linked server configurations, SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures, and System level Functions etc. The system and user databases related information such as name and location for user and system database are captured in Master database. The Master database basically consists of two physical files, namely master.mdf (data file) and mastlog.ldf (log file). By default when you are installing SQL Server 2008 the master database related data and log file are installed in the following folder location Drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\. If master database is corrupted or if it is not available then the SQL Server Service will not start. In SQL Server 2005 and later versions the system objects are stored in Resource Database rather than in Master Database. The Master database is created using Simple Recovery Model.

SELECT DATABASEPROPERTYEX('Master','RECOVERY') AS [RECOVERY MODEL]

Model Database
The Model database is basically used as a template when creating databases in SQL Server. Basically SQL Server takes a copy of Model database whenever a user tries to create a new database in SQL Server. This also means that if a user creates any tables, stored procedures, user defined data types or user defined functions within a Model database; then those objects will be available in every newly created database on that particular instance of SQL Server. In SQL Server, TempDB database is recreated every time SQL Server Service is restarted. This also means that the some of the settings of Model database are also used when TempDB is created. The Model database basically consists of two physical files namely Model.mdf (data file) and ModelLog.ldf (log file). By default when you are installing SQL Server 2008 the Model database related data and log file are created in the following folder location Drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\. If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database. The Model database is created using Full Recovery Model. As the Model database is in Full recovery model, all the newly created user databases will be created with Full recovery model. If you want to change the recovery model to Simple or Bulk Logged for all the newly created databases then you can set the recovery model for Model database accordingly.

SELECT DATABASEPROPERTYEX('Model','RECOVERY') AS [RECOVERY MODEL]

MSDB Database
SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc. MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc. This database also stores SSIS packages created in SQL Server 2005 & 2008. The Data Transformation Services (DTS) Packages in SQL Server 2000 are also stored in MSDB Database. The MSDB database basically consists of two physical files namely MSDBData.mdf (data file) and MSDBLog.ldf (log file). By default when you are installing SQL Server 2008 the MSDB database related data and log file are created in the following folder location Drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\. If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities. The MSDB database is created using Simple Recovery Model.
SELECT DATABASEPROPERTYEX('Msdb','RECOVERY') AS [RECOVERY MODEL]

Next Page>>