Rookie learn sqlserver--data files and log files

Yesterday I talked about pages and districts, and today I want to write some data files and log files.

There are three types of files according to MSDN Library,sql Server, respectively:

• Master Data File --suffix. mdf
• secondary data file --suffix. ndf
• log file --suffix. ldf

Data files:

From the sample database we can see that there is a master data file and a log file, and the secondary data file is not required.

At the same time each file has a file number, FileID or filenum, used to identify the file.

For example, the DBCC PAGE command for the previous article, which uniquely identifies a page in the database, requires a database name or dbid + file number FileNum + page number Pagenum

DBCC page ({'dbname'| [])

#命名

Each file has two names, namely the logical name (logical_file_name) and the physical file name (os_file_name) consisting of the path and file name .

The former must be unique, and the latter must conform to the naming rules of the operating system.

#文件组

For files We can also be managed by filegroups, as seen from the number of files in the primary filegroup 1, the filegroup does not contain log files.

In addition, we can also add a customized filegroup.

Use filegroups to help with data layout and management tasks, such as backup and restore

#文件增长

SQL Server enables the user to set the database initial value and maximum value.

For data files, put different files on different hard disks to speed up read and write.

It is important to note, however, that for new data, SQL Server allocates new data to the files that are written by the size of the existing free space for all files in the same filegroup.

For example, two files have free space of 2GB and 1GB respectively, then the new data will be written to two files at 2:1.

Log files are not the same, because log files are written in strict sequential order (estimated to ensure database integrity and ease of data recovery), so that multiple log files can not improve concurrency performance is not proportionally written to say.

The following recommendations are available in the Microsoft SQL Server Enterprise Platform Management practices book:

1. To be set to grow by a fixed size, not proportionally. This avoids the unnecessary hassle of having to grow too much or too little at a time. It is recommended that you compare smaller databases and set the growth time to up to one MB. For large databases, set the growth time to one MB to five MB.
2. To regularly monitor the usage of each data file, try to ensure that each file has the same amount of space, or the desired proportions.
3. Set the maximum file size to prevent SQL Server files from growing out of disk space and affecting the operating system.
4. After the growth, the new data file space allocation should be checked in time. Avoid SQL Server always writing data to individual files.

log file:

TBD to be mended

Reference:

https://msdn.microsoft.com/zh-cn/library/ms179316 (v=sql.105). aspx

Microsoft SQL Server Enterprise platform management practices

Rookie learn sqlserver--data files and log files