SQL Server - System Databases

SQL Server is deployed with master, tempdb, model, msdb and resource (it was introduced from SQL Server 2005) System Databases. System Database is contains collection of tables, data types, message types, stored procedures, functions and other database objects that used to execute Database Operation in the SQL Server System.


Master Database
The master database records all of the system level information for a SQL Server System. This master database is maintains System Configuration, User Accounts, User Defined Database data and log file location information.

Master database information is stores in the master.mdf data file and mastlog.ldf log file. Initial configuration for master database is approximately 6 MB and 1.25 MB for the data and log files. These files are grow when the user database system becomes more complex and maintain more databases.


Tempdb Database
The tempdb database is a global resource that is available to all users connected to the instance of SQL Server System. This tempdb database is maintains user defined global or local temporary tables, temporary stored procedures, table variables, cursors along with database engine defined versions of the tables for snapshot isolation and temporary sorted rowsets when rebuilding indexes.

Tempdb database information is stores in the tempdb.mdf data file and templog.ldf log file. Tempdb database size is effect on the performance of user defined database operation. Temporary stored information is dropped automatically when shut down SQL Server System and tempdb is re-created automatically when SQL Server is started so the system starts with a clean copy of the database.


Model Database
The model database is template database that permanently exist in the SQL Server System. When execute CREATE DATABASE statement, the SQL Server System automatically copy the model database template into User Defined Database. User Defined Functions, Stored Procedures, Triggers and other data objects can create and store in the Model database that inherit when create new database. Model database information is stores in the model.mdf data file and modellog.ldf log file. 


Msdb Database
The msdb database is used by SQL Server Agent for scheduling alerts and jobs, Database Mail and Operation. Msdb database information is stores in the MSDBData.mdf data file and MSDBLog.ldf log file.


Resource Database
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. When upgrading new version of SQL Server, this database information upgrading automatically and deploy with new SQL Server System.

Resource database cannot view in the SQL Server Management Studio and logically this information is deployed with System Database. Resource database information is stores in the mssqlsystemresource.mdf data file and mssqlsystemresource.ldf log file.

These files are located in :\Program Files\Microsoft SQL Server\MSSQL10_50.\MSSQL\Binn\.

SQL Server cannot back up the Resource Database that should talk a backup through file-based or disk-based backup option. Resource Database should be restores in the same location of Master Database.

Microsoft Customer Support Services specialist is helps to provide guidance to modify the Resource Database when occur error. The ID of the Resource Database is always 32767.

The following SQL Query is helps to find out Resource Database Version Number, Last Update Date Time and System Objects Information.

SELECT SERVERPROPERTY('ResourceVersion');

GO

  

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

   

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'));

GO