About database filesIn general, database files include the following files :·
A primary data file (. MDF
)·
0
Auxiliary data files (. NDF
)·
1
Log Files (. LDF
)What are their respective roles? The answer is actually obvious. I would like to emphasize again :·
Master Data File: In addition to data storage, you can also track other database files. It is estimated that the results of the index and allocation pages are stored. In short, it is used to store system tables and data. When you use the simplest create database Command to create a database, the default size of the master data file is the size of the master data file of the master database. ·
Auxiliary Data Files: It is used to store data after the maximum value of the primary data file. When a user inserts a record, whether it is inserted into the primary data file or the secondary data file, or insert the same record for both database files. This issue will be discussed later. ·
Log Files: Stores all the necessary information for restoring the transaction. Because the transaction reply is mainly completed by logs, log files are necessary in the database file, at least one. When you create a database using the simplest create database Command, the default log file size is 1 MB. When creating a database file, we must specify five attributes (TB, GB, MB, MB by default ):·
Logical name (name): The physical name is stored in the database. ·
Physical name (filename): This is a real database file stored on a disk of the operating system. ·
Initial Capacity (size): Defines the minimum capacity of database files. If you use the simplest create dabatase to create a database, the initial capacity of the primary data file is at least equal to the size of the primary data file in the master database by default, and the initial capacity of the log file is 1 MB by default; if only the initial capacity of the data file is specified during database creation, but the initial capacity of the log data file is not specified, the default size of the log data file is 25% of all data files. ·
Maxsize)
:After a database is created, the data file increases gradually, but the maximum capacity that can be increased is defined by this value. If the maximum capacity is not specified, the entire disk is always filled with files. ·
Filegrowth)
:Specifies the increment of the operating system file used to store tables, indexes, or log files, that is, the increase in the database capacity each time. If the increment is not specified, the default increment of the data file is 1 MB (different from that in Yukon), and the default increment of the log file is 10% (Katmai and Yukon are the same ).
All information about databases and database files/file groups can be viewed through the following views and stored procedures. ·
SYS. Databases·
SYS. database_files·
Sp_helpdb·
Sp_databases·
Sp_helpfile·
Sp_helpfilegroup
In addition, when creating a data file, we can also specify some other attributes :·
File Group:The file group uses a proportional filling policy for all files in the group. When writing data to a file group, Microsoft SQL server writes a certain proportion of data to each file in the file group based on the amount of available space in the file, instead of filling all the data in the first file, then write the next file. For example, if file F1 has 100 MB space available and file F2 has 200 MB space available, an extended Disk Area is allocated from file F1, allocate two extended disk areas from file F2, and so on. In this way, the two files are almost filled at the same time and can be simply striped.
Once all the files in the file group are full, SQL Server automatically expands a file at a time in a circular manner to accommodate more data (assuming that the database is set to automatically grow ). For example, a file group consists of three files, all of which are set to auto-increment. When all files in the file group are used up, only the first file is extended. When the first file is full and more data cannot be written to the file group, expand the second file. When the second file is full and more data cannot be written to the file group, expand the third file. If the third file is full and more data cannot be written to the file group, expand the first file again, and so on.
When using file and file groups, you can improve database performance by allowing databases to be created across multiple disks, multiple disk controllers, or raid (Redundant Array of Independent Disks) systems. For example, if a computer has four disks, you can create a database consisting of three data files and one log file, and place one file on each disk. When accessing data, the four read/write headers can simultaneously access data in parallel to accelerate database operations.
In addition, files and file groups allow data layout because tables can be created in specific file groups. Because all inputs/outputs of a specific table can be directed to a specific disk, the performance can be improved. For example, you can place the most common tables in a file group, which is located on a disk; put other infrequently accessed tables in the database into other files in another file group, which is located on the second disk. Create Database crosssqlon primary-master data file group, which can be used directly or omitted. Primary is the default file group (name = crosssql1, -- Logical name of the database, stored in the database file filename = 'C:/crossgql/crossgql_dat1.mdf ', -- physical name of the database, that is, name in the file system. A database has only one MDF size = 10, -- initial capacity; default unit: MB maxsize = 50; -- maximum capacity: filegrowth = 15% -- Growth Value; default value: MB; percentage can also be used as the growth rate ), -- multiple files in a group are separated by commas (,) (name = crosssql2, filename = 'C:/crossgql/crossgql_dat2.ndf', size = 10, maxsize = 50, filegrowth = 15%), filegroup group1 -- this is a custom file group. You can use a file group to distribute data and increase the data access rate (name = crosssql3, filename = 'C:/crossgql/crossgql_dat3.ndf ', size = 10, maxsize = 50, filegrowth = 5), (name = crosssql4, filename = 'd:/crossgql/crossgql_dat4.ndf ', size = 10, maxsize = 50, filegrowth = 5), filegroup group2 (name = crosssql5, filename = 'e:/crossgql/crossgql_dat5.ndf ', size = 10, maxsize = 50, filegrowth = 5), (name = crosssql6, filename = 'C:/crossgql/crossgql_dat6.ndf ', size = 10, maxsize = 50, filegrowth = 5) log on -- log storage, A database must have at least one log data file. Generally, the log data file size is/4 (name = 'crosssql _ log1', filename = 'C: /crossgql/crossgql_log1.ldf ', size = 5 MB, maxsize = 25 MB, filegrowth = 5 MB) Go
