Everyone is a DBA (V) SQL Server database files

The SQL Server database is installed with 4 default system databases: Master, model, msdb, tempdb.

SELECT [name]     , database_id    , suser_sname as[owner]     , Create_date    , User_access_desc    , State_descfrom  sys.databases  WHERE<=4;

Master

The master database contains information that records the entire server installation information and all subsequent databases that are created, including disk space information, file allocation information, file usage information, system-level configuration item information, network endpoint information, user account information, information about each database, and so on.

Model

The model database is a template database. Each time a new database is created, SQL Server generates a copy of the model database as the infrastructure for the new database. Therefore, if you want to have some specified objects, permissions, and property settings when you create a new database, you can change the contents of the model database, and the new database automatically inherits those settings.

Msdb

The msdb database is used by some of the features serviced components provided by SQL Server. Including:

• SQL Server Agent: Used to perform scheduled tasks, such as backup and replication tasks.
• Service Broker: Used to provide a mechanism for queuing and reliability messages.
• Jobs
• Alerts
• Log Shipping
• Policies
• Database Mail
• Damaged Pages Recovery

Tempdb

The tempdb database is a SQL Server workspace, which is unique in that it is always rebuilt instead of recovering the database when SQL Server restarts, so the data stored is lost after the database restarts. The tempdb database is used to hold temporary tables (temporary Tables) that are explicitly created by the user, intermediate data for query processing, sorted intermediate data, row version data for Snapshot, cursor-related data, and so on. All users have the right to create and use local and global temporary tables in tempdb, which is a temporary table prefixed with # and # #.

Tempdb is most likely to create and delete databases with the largest number of new objects in a production environment, so the impact of optimizations on tempdb is greater than on the user database. Because there is only one tempdb per instance of SQL Server, the problematic application affects all other users in all other applications.

Mssqlsystemresource

SQL Server actually hides the 5th mysterious system database Mssqlsystemresource, called the system resource database. As the name implies, the Mssqlsystemresource database is stored in system resource-related information, the system executable objects are placed here, such as the sys.objects$ object, the suffix "$" object is not visible in other databases. This database cannot be viewed directly through SQL Server Management Studio or directly, and the control of permissions is not able to modify it. The primary role of the Mssqlsystemresource database is for system upgrades and patch installation to quickly replace resource definitions within the system.

The Mssqlsystemresource database file is stored by default in the Binn directory, the file name is Mssqlsystemresource.mdf, and there is a corresponding log file mssqlsystemresource.ldf. If you want to explore the contents of the Mssqlsystemresource database, you can copy the two files to a new directory, rename the file, restart SQL Server, and then Attach the renamed file to the new database.

CREATE DATABASEDennis_resource_copy on(NAME=data, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. Dennis\mssql\data\mssqlsystemresource_copy.mdf'), (NAME= Log, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. Dennis\mssql\data\mssqlsystemresource_copy.ldf'    ) forATTACH;

In this way, SQL Server does not discriminate against the Dennis_resource_copy database above and other regular databases, and modifying objects in the Dennis_resource_copy database does not affect the Mssqlsystemresource database.

Database files

The database file is actually no different from the normal file system files. A database file of type 3 is allowed in SQL Server:

• Primary datafiles: Each database has a master data file with an. mdf extension.
• secondarydata files: The database can have no or multiple secondary data files, using the. ndf extension.
• logfiles: Each database has at least one log file, using the. ldf extension.

In fact, there are FileStream data files and Full-text data files for special functions.

When you create a database file, each file has 5 properties that you can specify:

• Logical FileName: Logical file name
• Physical FileName: Physical file name
• Initial Size: Initial sizes
• Maximum Size: Maximum sizes
• Growth Increment: Growth increment

CREATE DATABASEDennis_test on PRIMARY(NAME=dennis_test_primary, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. Dennis\mssql\data\dennis_test_primary.mdf', SIZE=  -MB, MAXSIZE=  $MB, FileGrowth=  -MB), (NAME=dennis_test_secondary, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. DENNIS\MSSQL\DATA\DENNIS_TEST_SECONDARY.NDF', SIZE= TenGB, MAXSIZE=  -GB, FileGrowth=  -MB)LOG  on(NAME=Dennis_test_log, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. Dennis\mssql\data\dennis_test_log.ldf', SIZE= 2GB, MAXSIZE= TenGB, FileGrowth=  -MB);

You can view these properties using the metadata view sys.database_files.

SELECT *  from Sys.database_files;

The default size of the data file is the size of the primary data file for the model database (default 2M), and the default size of the log file is 0.5M.

For allocation and management purposes, you can divide data files for a database into filegroups. In some cases, you can put data and index files on a specific filegroup, on a specific drive, to improve performance.

The filegroup that contains the master data file is called Primary Filegroup, and there is only one Primary Filegroup. If you create a database without a specific description of which filegroup you want to put the file in, the default is put in Primary Filegroup. Of course, you can also modify the default filegroup.

CREATE DATABASEDennis_test on PRIMARY(NAME=Dennis_primary1, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. Dennis\mssql\data\dennis_primary1.mdf', SIZE= Ten, MAXSIZE=  -, FileGrowth= Ten), (NAME=Dennis_primary2, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. DENNIS\MSSQL\DATA\DENNIS_PRIMARY2.NDF', SIZE= Ten, MAXSIZE=  -, FileGrowth= Ten), FILEGROUP dennis_group1 (NAME=dennis_grp1fi1e1, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. DENNIS\MSSQL\DATA\DENNIS_GRP1FI1E1.NDF', SIZE= 5, MAXSIZE=  -, FileGrowth= 5), (NAME=dennis_grp1fi1e2, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. DENNIS\MSSQL\DATA\DENNIS_GRP1FI1E2.NDF', SIZE= 5, MAXSIZE=  -, FileGrowth= 5), FILEGROUP dennis_group2 (NAME=dennis_grp2fi1e1, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. DENNIS\MSSQL\DATA\DENNIS_GRP2FI1E1.NDF', SIZE= Ten, MAXSIZE=  -, FileGrowth= 5), (NAME=dennis_grp2fi1e2, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. DENNIS\MSSQL\DATA\DENNIS_GRP2FI1E2.NDF', SIZE= Ten, MAXSIZE=  -, FileGrowth= 5    ) LOG  on(NAME=Dennis_log, FILENAME= 'C:\Program Files\Microsoft SQL Server\mssql11. Dennis\mssql\data\dennis_log.ldf', SIZE= 5MB, MAXSIZE=  -MB, FileGrowth= 5MB);

Database configuration options

Status option (state options)

• Single_user | Restricted_user | Multi_user
• OFFLINE | ONLINE | EMERGENCY
• Read_Only | Read_write

Cursors option (cursor options)

• Cursor_close_on_commit {on | OFF}
• Cursor_default {LOCAL | GLOBAL}

Automatic option (Auto options)

• Auto_Close {on | OFF}
• Auto_create_statistics {on | OFF}
• Auto_shrink {on | OFF}
• Auto_update_statistics {on | OFF}
• Auto_update_statistics_async {on | OFF}

parameterization option (Parameterization options)

• date_correlation_optimization {on | OFF}
• PARAMETERIZATION {Simple | Forced}

SQL option (SQL options)

• Ansi_null_default {on | OFF}
• ANSI_NULLS {on | OFF}
• ansi_padding {on | OFF}
• ansi_warnings {on | OFF}
• ARITHABORT {on | OFF}
• Concat_null_yields_null {on | OFF}
• Numeric_roundabort {on | OFF}
• QUOTED_IDENTIFIER {on | OFF}
• Recursive_triggers {on | OFF}

Database recovery option (recovery options)

• RECOVERY {Full | bulk_logged | Simple}
• torn_page_detection {on | OFF}
• page_verify {CHECKSUM | torn_page_detection | NONE}

External access option (External access options)

• db_chaining {on | OFF}
• Trustworthy {on | OFF}

Database mirroring options (db mirroring Options)

• PARTNER {= ' partner_server '}
• | FAILOVER
• | Force_service_allow_data_loss
• | OFF
• | RESUME
• | SAFETY {Full | OFF}
• | SUSPEND
• | TIMEOUT integer
• }
• WITNESS {= ' witness_server '}| OFF}

Service Broker option (Service Broker options)

• Enable_broker | Disable_broker
• New_broker
• Error_broker_conversations

Change tracking options (Tracking option)

• change_tracking {= on [<change_tracking_settings> | = OFF}

Database encryption option (encryption options)

• Encryption {ON | OFF}

Snapshot isolation option (Snapshot isolation options)

• allow_snapshot_isolation {on | OFF}
• Read_committed_snapshot {on | OFF} [with <termination>]

You can use the sys.databases metadata view to view the configuration of options for each database.

SELECT *  from sys.databases;

You can use the ALTER DATABASE naming to modify the metabase configuration options.

ALTER DATABASE SET Single_user; ALTER DATABASE SET OFFLINE; ALTER DATABASE SET  ALTERDATABASESET with

This series of articles "Everyone is a DBA" by Dennis Gao published from the blog Park Personal technology blog, without the author's consent to prohibit any form of reproduction, any automatic or artificial reptile reproduction or plagiarism behavior are bullying.

Everyone is a DBA (V) SQL Server database files