SQL with Manoj

While working on SQL Server Management Studio you might have seen some default databases in System Database folder. And you would have observed the same set of databases on other machines or database servers you might have worked on or seen, as shown in the image below (red-circled):

There are total 5 System Databases, out of these you can see 4 under the System Databases folder (above image, ignore the blue circled). The 5th database is Resource which does not appear on SSMS.
 

–> Let’s check about these databases individually here:
 

1. master: database records all the system-level information for an instance of SQL Server, which includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

It records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.

SQL Server cannot start if the master database is unavailable. If in case you would like to move the master database or have moved it and ran into issues, check this post Move Master database.

Starting SQL Server 2005 and ahead, system objects are no longer stored in the master database, instead, they are stored in the Resource database, mentioned below (5th type).
 

2. model: database is used as the template for all databases created on an instance of SQL Server. The entire contents of the model database, including database options, are copied to the new database. Thus, if you modify the model database, all databases created afterward will inherit those changes.

Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
 

3. msdb: database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SSMS, Service Broker and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb, which includes the name of the party that performed the backup, the time of the backup, and the devices or files where the backup is stored.
 

4. tempdb: database is a workspace for holding temporary objects or intermediate result sets.

tempdb is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

– Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

– Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

– Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

– Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Thus, there is never anything in tempdb to be saved from one session of SQL Server to another.
 

5. Resource: database does not appear on SSMS, and is a read-only database that contains all the system objects that are included with SQL Server.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

The ID of the Resource database is always 32767.

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf, and are located in :\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn\ folder.

Check the Resource Database version and the last updated date:

SELECT SERVERPROPERTY('ResourceVersion') AS ResourceVersion;
GO
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime;
GO

Check my other post on Resource Database.
 

–> You can also query all these system databases, except the Resource database.

And you can see above first 4 database IDs, 1-4 are reserved for master, tempdb, model and msdb databases.
 

–> There are two more databases created while you install SQL Server with Reporting Services (SSRS). You can see these databases appearing in SSMS in the image above (circled blue):

1. reportServer: stores following:

– Items managed by a report server (reports and linked reports, shared data sources, report models, folders, resources) and all of the properties and security settings that are associated with those items.

– Subscription and schedule definitions.

– Report snapshots (which include query results) and report history.

– System properties and system-level security settings.

– Report execution log data.

– Symmetric keys and encrypted connection and credentials for report data sources.

2. reportServerTempdb: Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database.