Role of SQL Server system database

SQL Server System database function-sqlserver,tempdb, record, master, number, size,-North network-training

　　System Database

SQL Server's system database is divided into: master, model, msdb, and tempdb, these four databases do their job in SQL Server, as developers, it is necessary to understand the responsibilities of these databases, let's look at the role of these several databases.

　　1. master database

The master database records all system-level information for the SQL Server System (table sysobjects). He records all login accounts (table sysusers) and system configuration. The master database is a database that records all other databases (table sysdatabases), including the location of the database files. The master database records the initialization information for SQL Server, and he always points to an available backup of the latest master database.

　　2. Model Database

The model database is the template that is used to create the database on the system. When the system receives the "CREATE DATABASE" command, the first part of the newly created database is copied from the model database and the remainder is populated with empty pages, so the mode database must be in the SQL Server data.

　　3. msdb database

The msdb database is used by the SQL Server Agent to dispatch alerts and jobs, and to record operators. For example, we backed up a database and inserted a record in the table Backupfile to record the associated backup information.

　　4. tempdb database

The tempdb database saves temporary tables and stored procedures that are generated during the operation of the system. Of course, it also satisfies other temporary storage requirements, such as saving SQL Server-generated storage tables, and so on. The tempdb database is a global advisory, and any user connected to the system can generate temporary tables and stored procedures in the database. The tempdb database empties the contents of the database every time SQL Server starts, so the table is clean every time SQL Server is started. Temporary tables and stored procedures are automatically dropped when the connection is broken, and there is no active connection after the system shuts down, so nothing in the tempdb database is saved from one session of SQL Server to another.

By default, when SQL Server runs, the tempdb database automatically grows as needed. However, unlike other databases, it resets to its initial size each time the database engine is started. If the size defined for the tempdb database is small, then each time you restart SQL Server, the size of the tempdb database is automatically increased to the size required to support the workload, which may become part of the system processing load. To avoid this overhead, you can use ALTER DATABASE to increase the size of the tempdb database.

Role of SQL Server system database