TempDB Database
The TempDB is the only database in SQL Server which is recreated every time when SQL Server restarts. It basically stores all the temporary objects such as temporary tables, global temporary tables, temporary stored procedures, cursors, table variables, it is also used when indexes are create or rebuild using SORT_IN_TEMPDB option etc. It is not advisable to store any permanent objects within TempDB database as this database is recreated whenever SQL Server is restarted. Another important thing to be noted is that TempDB database doesn’t support database backup and recovery operations. If your application uses TempDB a lot then you need to plan the growth of TempDB database to avoid any performance issues. The TempDB database basically consists of two physical files namely tempdb.mdf (data file) and templog.ldf (log file). By default when you are installing SQL Server 2008 the TempDB database related data and log file are created in the following folder location Drive:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA. The TempDB database is created using Simple Recovery Model and it is not possible to alter the database recovery model.
SELECT DATABASEPROPERTYEX(‘TempDB’,’RECOVERY’) AS [RECOVERY MODEL]
Resource Database
The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database. Resource Database does not contain any user data or any user metadata. By design, Resource database is not visible under SQL Server Management Studio’s Object Explorer | Databases | System Databases Node. The physical file names of Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf. The important thing to be noted by the DBA is that each instance of SQL Server has one and only one associated mssqlsystemresource.mdf & mssqlsystemresource.ldf file and instances do not share this file. In a cluster environment, the Resource database basically exists in the Data folder on a shared disk drive. The ID for the Resource Database will be always 32767. The DBA shouldn’t be renaming or moving the Resource Database file. If the files are renamed or moved from their respective locations then the SQL Server will not start. Another important thing to be considered is not to put the Resource Database files in compressed or encrypted NTFS file system folders as it will hinder the performance and will also prevent upgrades. For more details you can refer to the article titled “Importance of the Resource Database”.
ReportServer
The ReportServer database is created when a user installs SQL Server Reporting Service. This database basically stores all the metadata and object related information which is used by reporting services. For example it includes information related to all the reports, linked reports, data sources, report models, folders, permissions, the properties and security related settings used by all the objects, report execution schedules, report execution logs etc. This database is the heart for reporting services as it stores entire information with respect to SQL Server Reporting Services. The ReportServer database basically consists of two physical files namely ReportServer.mdf (data file) and ReportServer_log.ldf (log file). By default when you are installing SQL Server 2008 Reporting Services the ReportServer database related data and log file are created in the following folder location Drive:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA. The ReportServer database is created using Full Recovery Model.
SELECT DATABASEPROPERTYEX(‘ReportServer’,’RECOVERY’) AS [RECOVERY MODEL]
ReportServerTempDB
The ReportServerTempDB database is created when a user installs SQL Server Reporting Service. This database is basically used by ReportServer database to stores session information, cached reports, working tables used by reporting services etc. The ReportServerTempDB database basically consists of two physical files namely ReportServerTempDB.mdf (data file) and ReportServerTempDB_log.ldf (log file). By default when you are installing SQL Server 2008 Reporting Services the ReportServer database related data and log file are created in the following folder location Drive:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA. The ReportServer database is created using Simple Recovery Model.
SELECT DATABASEPROPERTYEX(‘ReportServerTempDB’,’RECOVERY’) AS [RECOVERY MODEL]
Distribution
The Distribution database is created on the Distribution SQL Server when replication is configured. This database basically stores all the metadata information related to the configuration of replications and it also stores replicated transaction from the publisher database which needs to be delivered to the subscribers in case of transactional replications. However for Merge replication it basically stores synchronization history. The distribution database basically consists of two physical files namely distribution.mdf (data file) and distribution_log.ldf (log file). By default when you are configuring replication the distribution database related data and log file are created in the following folder location Drive:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA. The Distribution database is basically created using Simple Recovery Model.
SELECT DATABASEPROPERTYEX(‘Distribution’,’RECOVERY’) AS [RECOVERY MODEL]
Conclusion
It is very important for a database administrator to have sound knowledge of all the SQL Server System databases. A DBA should make sure that all the system databases are part of Disaster Recovery Document and the databases are backed up regularly except for TempDB database as backup and recovery operations is not supported on TempDB database.
]]>