SQL Server System Databases Maintenance

SQL Server Installation by default creates several system databases per instance to maintain and administer that instance. In this article, we will examine these system databases and understand their responsibilities.

SQL Server System Databases

In SQL Server, System databases are created during the installation process to store the SQL Server instance-specific configuration details to function normally. Every installation of SQL Server creates a minimum of 5 system databases and 1 Replication related system database named distribution database which will be created by users if Replication is configured in that instance. Each System database has its purpose and we’ll investigate this in detail later in this article.

The system databases are:

• Master – Installed by Default
• Msdb – Installed by Default
• Model – Installed by Default
• Tempdb – Installed by Default
• Resource – Installed by Default. Introduced in SQL Server 2005 and available in later SQL Server versions and hence not available in SQL Server 2000 and earlier versions.
• Distribution – Created by User Action. Users can create the distribution database to configure Replication.

To View the system database installed in SQL Server, we can use SSMS.

Connect to your SQL Server instance, expand Databases > System databases:

Have you noticed that the Resource database is missing in the above list? The thing is, the resource database is a special system database that is not listed in the SSMS Object Explorer. However, we can query the resource database details from a system DMV named sys.sysaltfiles and execute the query:

SELECT dbid, db_name(dbid) database_name, fileid, name, filename
FROM sys.sysaltfiles
WHERE dbid NOT BETWEEN 5 AND 11

In the results, we can see the system databases in order: master, tempdb, model, msdb, distribution, and, finally, the dbid 32767 which is a resource database. However, this resource database doesn’t display any database name since it doesn’t have an entry in sys.databases. I’ve excluded a couple of user databases between dbid 5 and 11 and included AdventureWorks_REPL as an example to show that DMV can display User databases as well. We’ll go into more detail about the Resource database and other system databases later.

SQL System Databases Restrictions

Since System databases hold critical system configuration details, there should be proper security measures in place to avoid accidental deletion of data. Hence, System databases have the below restrictions compared to user databases:

System Databases Can’t Be Taken Offline

We can take a user database offline using the ALTER DATABASE command as shown below:

ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

However, if we try to take any of the system databases OFFLINE using the above command, then we will receive an error as shown below:

System Databases Cannot Be Dropped

While we can drop the user databases by executing the DROP DATABASE command

DROP DATABASE AdventureWorks

If we try to DROP any of the system databases, then we will receive the error as shown below:

Owner of System Databases Cannot Be Changed

The owner of the system database is sa by default. It can’t be changed. Attempts to rename the System database owner will provoke errors.

However, there is an exception. It is possible to modify the owner of the msdb database.

use [master];
GO
ALTER AUTHORIZATION ON DATABASE::[master] TO [RRJ\RRJ]
GO

Database Name of System Databases Cannot Be Changed

If we try to rename the system databases, then we will get error message as shown below:

ALTER DATABASE master MODIFY NAME = RRJ_master;
GO

The Collation of System Databases Cannot Be Changed

System databases are created with the Collation name chosen during the time of SQL Server installation. Once installed, the Collation of system databases can’t be changed. The only way to change the System databases collation is to reinstall the SQL Server instance with the correct collation.

Primary Filegroup of System Databases Cannot Be Set to READ_ONLY Mode

Since the system database captures critical information related to SQL Server instances, SQL Server doesn’t allow the Primary data files residing in Primary Filegroup to be set as read-only.

Change Data Capture Feature Cannot Be Enabled on System Databases

This feature is used to track every DML change happening on a database on the tracked tables. If we try to enable the Change Data Capture feature on any system databases, the error will take place:

use master
GO
exec sys.sp_cdc_enable_db

Now that we are clear about the difference between system databases and user databases, we can examine the purposes of each system database in more detail.

Master Database in SQL Server

The Master system database holds key configuration details related to the SQL Server instance. SQL Server relies on them when it starts a particular instance. If it is impossible to start the master database for some reason, the SQL Server instance cannot start either.

These key details stored in the master database include the Login accounts, Linked Server details, endpoints, system configuration settings, and details about all User databases.

Now comes the question. How does the SQL Server service know where the data and log files of the master database are available? The answer lies in the startup configuration parameters of the SQL Server service.

To view the Startup Configuration parameters of a SQL Server instance, we should first know about the built-in tool named SQL Server Configuration Manager. It helps to manage all SQL Server-related services of all instances available on the particular Server. To view these data, open up the SQL Server Configuration Manager and it will display the list as shown below:

Click on SQL Server Services to view the list of Services available on this Server or PC:

Wait for a second! It looks familiar to the services.msc listing all services available in the Server but displaying only SQL Server related services.

Let’s open up services.msc to see how it looks and verify the differences across the SQL Server Configuration Manager and services.msc to compare which one is better.

The SQL Server Configuration Manager displays the Process ID of the services that are currently running. We could not find that in services.msc. Of course, we can get this information from the Windows Task Manager, but SQL Server Configuration Manager helped us view this in a single place.

Now, let’s take a detailed view. Right-click on the SQL Server service from services.msc. You will see the below menus: General, Log On, Recovery, and Dependencies.

From SQL Server Configuration Manager, right-click on the SQL Server(MSSQLSERVER) service > Properties. It lists out the below menus – Log On, Service. FileStream, Advanced, Alwayson OnHigh Availability, and Startup Parameters.

The Startup Parameters of the Service which stores the location of the master database data and log files was available only in the SQL Server Configuration Manager.

Click on Startup Parameters to view the details – for Existing Parameters. You will see the following info:

• The physical location of the master database Data File
• The physical location of the master database Transaction Log File
• The physical location of the ErrorLog Folder where errors related to SQL Server Service is located.

We can add more startup parameters like Single user mode (-m), etc. For that, we need to specify the necessary values in Specify a Startup parameter and click Add.

We have noticed that SQL Server Configuration Manager does not only display advanced details but also allows us to do a lot of advanced configurations related to SQL Server service. Hence, I would personally recommend using the SQL Server Configuration Manager to Stop/Start SQL Server-related Services compared to the default Services.msc option.

Recommended Practices for Master Database

Since the master database stores critical details about the SQL Server instance, it is recommended to follow the best practices while handling that database.

• Every configuration change on an instance of SQL Server will be stored in the master database. Thus, you always need to make a full backup of the master database to restore to the latest status in case we are restoring the master database from the Full backup, as required.

• Even though SQL Server allows users to create user tables or other objects in the master database, it is not recommended to do that. The master database should remain simple and clean. If you need to create user objects in the master database, you should make Full Backups of the master database more frequently.

• SQL Server supports the Startup procedure option to execute certain Stored Procedures upon starting a SQL Server instance. It uses the sp_procoption procedure. Be careful while using this option because having a non-optimal code or recursive logic might impact the startup time of the SQL Server instance.

If SQL Server couldn’t start due to any issues with the master database, we need to restore the master database from the last known good backup.

Model Database in SQL Server

As the name indicates, the Model system database acts as a model or template for any user database creation in terms of the file path, initial size, auto-growth settings, and the Recovery model, and other configuration options.

Any User objects like tables, procedures, etc., that are created in the model databases will be also created automatically across new user databases in that SQL Server instance.

Let’s verify this by making a new table in the model database:

Let’s check if this table is present in the model database:

The Model database also stores the default File Path of User databases as shown below in the Database Properties of the msdb database.

As per the current configuration, the Initial File size of both Data and Log Files is set to 8 MB with auto-growth for them both set to 64 MB.

If you need to create a User database in a different file path instead of the model database file location, we can modify it in the Server Properties of that SQL Server Instance.

In SSMS, right-click on Server > Properties > Database Settings.  View the Database default locations:

Change the file path to the desired path and click OK. The User database Data and Log files will be created in the new path you provided.

Let’s create a new database named model_test and check the new database Data and Log file paths along with the Initial and Autogrowth file properties and the model_verify table on the new database.

Let’s verify the model_test Data and Log file paths. Right-click on the model_test database > Properties > Files:

As we can see, the Data and Log files of the model_test database are created according to the path available in the model database. The Initial File size value of the Data and Log files is 8 MB. The Autogrowth value is 64 MB. These values match the configuration of the model database.

Now, we’ll check whether the model_verify table is created in the model_test database. We can see this new database.

Besides tables, this applies to Views, Stored Procedures, Functions, and any objects created in the model databases.

Recommended Practices for Model Database

Since the model database acts as a template for any new user database creation, we should implement the below practices when dealing with it:

• Whenever you want to implement any changes into the model database configuration (e.g., Initial File Size, Autogrowth size, user objects creation, modification, or deletion), take a Full Backup of the model database immediately.

• Since any user objects created in the model databases are created on any user databases, take care to add required objects only. Otherwise, lots of unnecessary objects will be created on all user databases, and you’ll spend much time sorting them and cleaning your databases.

• Configure the Initial File Size and Autogrowth parameters for the Data and Log Files. It helps to manage the Data and Log file sizes in the user databases better and improve performance.

MSDB Database

The msdb system database stores the below critical information across the system tables:

• SQL Server Agent-related items like Jobs, Job histories, Alerts, Operators, Proxies, etc.
• SQL Server features like Service Broker and Database Mail with the configuration and history details.
• SQL Server Backup and Restore details are stored inside the msdb database tables.
• Log Shipping Configurations, Replication Agent Profiles, and Data Collector configurations.
• Maintenance plans, SSIS Packages, and some other details.

In other words, the msdb system database stores all critical information related to SQL Server Agent Services and some other related services.

Recommended Practices for msdb database

The msdb database stores a lot of critical configuration information related to SQL Server Agents, Jobs, and Database Mail. It also stores historical details. Therefore, we should implement the below practices when dealing with the msdb database:

• In an SQL Server instance with many databases or Jobs configured out, the size of the msdb database will increase continuously over some time. Hence, Full Backups should be implemented for the msdb databases daily along with other user databases. If msdb receives a lot of critical information, then we can change the Recovery Model of the msdb database to Full and then implement Transaction Log Backup as well.

• Even though SQL Server allows users to create user objects on the msdb database, it’s recommended not to create any user tables or objects in the msdb database and increase the size of the msdb database further.

• Perform regular cleanup of msdb system tables to keep the msdb database size under control and avoid the performance impacts of having huge data across several tables.

Tempdb Database

The tempdb system database can be considered as a global working area available for all users connected to the SQL Server instance to perform their SELECT or other operations.

Tempdb database will hold the below object types while users perform their operations:

• Temporary objects created explicitly by users can be either Local or Global temporary tables and indexes, Table Variables, tables used in Table-valued functions, and Cursors.
• Internal objects created by the Database engine, such as:
  • Work tables used for intermediate results for spools, cursors, sorts, and temporary large objects (LOB)
  • Work files for Hash Join or Hash aggregate operations
  • Intermediate sort results while dealing with creating or rebuilding indexes if SORT_IN_TEMPDB is set to ON and other operations like GROUP BY, ORDER BY, or UNION queries
• Version Stores which support the Row versioning feature either common version store or online index build version store.

Whenever the SQL Server service starts or restarts, the tempdb database will be created anew with the help of the model database. Thus, tempdb is the only system database that can’t be backed up.

If we try to backup it, we will receive errors:

Since we use tempdb in almost all user operations, this database poses a significant performance bottleneck across several versions of SQL Server. Starting from SQL Server 2016, there were several optimization techniques implemented by Microsoft – we’ll discuss them later.

Before going into the recommended practices for the tempdb database, let’s have a quick look at its data files under the default configuration as shown below:

For my current SQL Server Instance, we have 4 Data files and one Log file for the tempdb database.

Starting from SQL Server 2016, we have the SQL Server installer that allows us to add multiple files to tempdb. The above 4 files with an initial size of 8 MB and Autogrowth size of 64 MB were created using the default options shown below.

If we have a single data file in the tempdb database, all logical cores available in the server try to access the same data file of tempdb, resulting in a performance bottleneck.

Having multiple data files will logically associate certain cores to a single file. Thus, we have lesser contention on tempdb data files. This will improve the performance impact on the tempdb data files.

Recommended Practices for tempdb Database

Since the tempdb database is like a global working area for all user activities, the tempdb size increases based upon the user activities. It can be a performance bottleneck for the entire SQL Server instance.

Hence, we should implement the following practices:

1. Place the tempdb Data and Log files on high-performance storage to get higher IOPS for better performance.
2. Ensure that the tempdb database is divided into multiple data files to reduce contention and avoid performance bottlenecks on the tempdb database.
   • If the number of logical cores is less than 8, we can have one tempdb data file per logical core. In our test instance, we had 4 logical cores. Hence, 4 data files on tempdb should be sufficient.
   • If the number of logical cores is more than 8, start with 8 data files and increase by 4 data files if contention and performance issues are observed on the tempdb database.
   • If the number of logical cores in a server is 32 or 64, we can start with 8 data files. It means that 4 cores or 8 cores are associated logically for a single data file.
     
     For more clarity about multiple tempdb data files, I would recommend you Paul Randal’s excellent article.
     
3. Ensure that tempdb data files are configured with optimal initial file size. Ideally, this should be achieved via a trial and error approach. Tempdb with optimal Initial file size tends to grow fewer times compared to tempdb configured with the less initial file size which tends to grow several times leading to fragmentation. For example, in the current configuration, all files are configured with an 8 MB initial file size which is too little to handle SQL Workloads. Thus, apply the Trial and Error approach and set the Initial File Size to 512 MB or 1 GB, or some other value.
4. Ensure that all tempdb data files are set to equal file size. Auto-growth properties must be defined equally. In our scenario, all files are set to 64 MB autogrowth. Setting the Autogrowth size to 512 MB or 1 GB, or any other appropriate value helps to reduce frequent autogrowth on tempdb data files.
5. Ensure that the Initial File size and Autogrowth for the tempdb log file are configured to an optimal value similar to tempdb data files. Since the Recovery model of tempdb is set to Simple by default and can’t be modified, configuring the initial file size and autogrowth property of the tempdb log file should be sufficient.

Tempdb is vital for SQL Server instance performance. We’ll take a detailed look into the frequent issues faced on tempdb and how to shrink it optimally in our next articles.

Resource Database in SQL Server

The Resource System database is the only read-only system database that is not listed under system databases in SSMS as seen earlier.

The Database ID (dbid) of resource databases across all instances will be 32767 which is also the maximum number of databases supported within a SQL Server instance. It can be queried from the sys.sysaltfiles system DMV. Executing the below SELECT query on sys.sysaltfiles will return the resultset showing where the Data and Log files of the resource database are located:

We can see physical files of the resource database available in the above-mentioned path. The date modified indicates the time of SQL Server instance installation or the last time Service Packs (SP) or Cumulative Update (CU) is applied on this instance.

The Resource database holds all the system objects, such as sys.objects, sys.databases, sys.sysaltfiles, etc. physically inside it. This database logically lists all these objects under the sys schema across all databases available in the instance. Since the resource database is read-only, no user objects or data can be created on it.

The Resource system database was introduced starting from SQL Server 2005 to make the SQL Server Upgrade either to a new version of SP or CU faster. Before introducing that option, all such upgrades and updates meant that changes would apply across all databases, making the upgrade process more complicated and time-consuming. Now, any SQL Server version upgrade or SP or CU update just upgrades or replaces the resource database.

As the resource database is read-only and not visible to users, it does not require any user intervention. If you wish to include back up resource database in your High Availability or Disaster Recovery planning, just make a file backup of mssqlsystemresource.mdf and mssqlsystemresource.ldf files after stopping SQL Server Services (SQL Server service won’t allow to copy the files while SQL Server Service is up and running) and Save it in a secure location. Take double care not to update it on any instance of SQL Server running with a different version of SP or CU levels, as it might cause unexpected issues.

Distribution Database in SQL Server

The distribution system database is the heart of Replication. Users can create or configure distribution database as part of Replication setup with the help of either Configure Distribution Wizard or Create Publication Wizard. We described the distribution database creation steps in detail as part of my previous article about SQL Server Transactional Replication Internals.

Recommended Practices for Distribution Database

Since the distribution database is essential for the Replication functionality, we should implement the following practices:

• Move the distribution database Data and Log files to the drive with good IOPS to avoid distribution performance issues.

• Configure the Initial File Size and Autogrowth properties of the distribution database to a better value to avoid fragmentation issues.

• Include distribution database to the Database Full backup maintenance jobs.

• Include distribution databases to the Index Maintenance jobs to avoid fragmentation and performance issues.

In my article about SQL Server Transactional Replication internals, you will also find recommendations about other efficient practices.

Conclusion

Thanks for going through another power-packed article!

I hope it helped you clarify the essence and purposes of the SQL Server system databases and learn the best practices to avoid Performance issues on these databases.

Tags: sql database, sql server system databases Last modified: September 11, 2023