SQL Server Standard System database

SQL Server system databases are important, and they should not be modified most of the time. The only exception is the model database and the tempdb database. The model database allows deployment changes to any newly created database, such as a stored procedure, and the tempdb database is changed to help extend the database to bear more load.

1. Resource Database

The resource database was introduced from SQL Server 2005. It contains all the read-only critical system tables, metadata, and stored procedures that SQL Server needs to run. It does not contain any information about any user instance or database, it is written only when a new service patch is installed. The resource database contains all the physical tables and stored procedures that are referenced by other database logic. The default installation location is C:\Procgram Files\Microsoft SQL Server\mssql14. Mssqlserver\mssql\binn, there is only one resource per instance.

Note: where C: is the standard setting, MSSQL14 is the version. MSSQLServer is the instance name.

Since version 2012, when upgrading to a new service patch or a quick fix, the old database will be overwritten with a copy of the resource database, allowing users to quickly upgrade the SQL Server directory and roll back to the previous version.

In general, DBAs can make simple queries to the resource database while connecting to any database, without having to directly connect to the resource database.

For example:

Note: Do not place the resource database on an encrypted or compressed drive, as this may lead to upgrade issues or performance issues.

Second, the master database

The master database contains metadata about the data (database configuration file and file location), login, and configuration information about the instance.

Run the following code to see some of the meta-data:

　　

The main difference between the resource database and the master database is that the master database holds the user instance-specific data, while the resource database saves only the schema and stored procedures required by the user instance, and does not contain any instance-specific data.

Try not to create objects in the master database, and if you create them, you may need to make more frequent backups.

Third, the tempdb database

Tempdb is used to store user-created temporary objects, temporary objects required by the database engine, and row version information, similar to the operating system's paging file. The tempdb database is created every time you restart SQL Server, the size reverts to the default size, and we don't have to think about backing him up.

Changes to object data in tempdb can reduce landings;

It is important to allocate enough space for the tempdb database;

In general, the tempdb database should be set to automatically expand when space is required;

Consider leaving enough space under the extended overhead of the 15%~20% if there is not enough space.

Iv. model Database

The model database is the system database that SQL Server serves as a template when it creates a new database. That is, when you create each database, SQL Server copies the model database as a new database, and the only exception occurs when you restore and reconnect the database on the other server. So, if you add your own objects to the model database, you should either include the model database in your backup or maintain a script that contains the changes.

V. MSDB database

MSDB contains information that is used by SQL Server Agent, log shipping, SSIS, and the backup and restore system of the relational database engine. This stored procedure stores all the information about jobs, operations, policing policies, and job history. These are important system-level data, so you should back up the database on a regular basis.

　　

SQL Server Standard System database