SQL SERVER files and filegroups _mssql

1. Implications and relationships of documents and filegroups

Each database has one master data file. and several from files. files are the physical embodiment of a database. Filegroups can include files that are distributed across multiple logical partitions to achieve load balancing. Filegroups allow you to group files to facilitate management and data allocation/placement. For example, you can create three files (DATA1.NDF, DATA2.NDF, and DATA3.NDF) on three hard drives, and assign the three files to the filegroup fgroup1. You can then explicitly create a table on the filegroup fgroup1. Queries on the data in the table are dispersed to three disks, thereby improving performance. Creating a single file on a RAID (redundant array of disks) stripe set can also achieve the same performance improvement. However, files and filegroups allow you to easily add new files to a new disk. In addition, if the database exceeds the maximum size of a single Microsoft Windows file, you can use a secondary data file to allow the database to grow.

2. Common problems in the application of file and file group in practice

Typically, we construct a database with only two files, MDF files, and LDF files. But there are two drawbacks:

(i) easy to cause the file too large

We know that the MDF file is a database file, this means that as the database increases MDF will increase, obviously in the current application of data expansion is too common, when your application becomes larger, MDF file will become larger, however, windows on the size of the file is required, This can easily cause the MDF file to reach the limits of the file size that Windows allows (and the database crashes).

(ii) No use of disk arrays

Large servers have a lot of disk arrays, you can make the disk array a simple illusion of n a rotating disk, the disk array is designed to be through the concatenation of multiple disks to achieve greater reading and writing efficiency. But if your database has only one MDF file (LDF file is not considered for the moment), Then you will always be able to use only one disk in this disk array. In that case, the efficiency of the expensive disk array is changed from parallel to concatenation. Imagine if we could split the MDF into multiple files, such as allocating a file to each disk on the disk array, and then distributing the data in MDF into each file , I read in tandem in the reading, so that the full use of disk array access efficiency.

These two problems are not normally met by us, but they are almost fatal when doing a large service development.

3. query file groups and file statements

This is under 2005, running

SELECT DF. [Name], Df.physical_name, DF. [Size], df.growth, F.[name][filegroup], f.is_default from sys.database_files DF JOIN sys.filegroups F on df.data_space_id = f.data_space_id

4. MSDN Official Explanation

Understanding Files and Filegroups

Each SQL Server database has at least two operating system files: One data file and one log file. Data files contain data and objects, such as tables, indexes, stored procedures, and views. The log file contains the information needed to recover all the transactions in the database. For ease of allocation and management, you can set up data files and put them in a filegroup.

Database files

-->

The SQL Server database has three types of files, as shown in the following table:

file	Description
Main	The primary data file contains the startup information for the database and points to other files in the database. User data and objects can be stored in this file or in secondary data files. Each database has one primary data file. The recommended file name extension for the primary data file is. mdf.
Secondary	Secondary data files are optional and are defined by the user and store user data. Secondary files can be used to spread data across multiple disks by placing each file on a different disk drive. In addition, if the database exceeds the maximum size of a single Windows file, you can use secondary data files so that the database continues to grow. The recommended file name extension for secondary data files is. ndf.
Transaction log	The transaction log file holds the log information used to recover the database. Each database must have at least one log file. The proposed file name extension for the transaction log is. ldf.

For example, you can create a simple database, Sales, which includes a primary file containing all the data and objects and a log file that contains transaction log information. You can also create a more complex database Orders, which includes a primary file and five secondary files. The data and objects in the database are scattered across all six files, and four log files contain transaction log information.

By default, data and transaction logs are placed under the same path on the same drive. This is the method used to process a single disk system. However, in a production environment, this may not be the best approach. It is recommended that you put data and log files on separate disks.

File groups

-->

Each database has one primary filegroup. This filegroup contains the primary data files and all secondary files that are not placed in other filegroups. You can create user-defined filegroups to assemble data files for ease of management, data allocation, and placement.

For example, you can create three files data1.ndf, DATA2.NDF, and DATA3.NDF on three disk drives, and then assign them to a filegroup fgroup1. You can then explicitly create a table on the filegroup fgroup1 . Queries on the data in the table are dispersed to three disks, which improves performance. The same performance gains can be achieved by using a single file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups enable you to easily add new files to a new disk.

The following table lists all the data files that are stored in the filegroup.

file Groups	Description
Main	The filegroup that contains the primary file. All system tables are assigned to the primary filegroup.
User-defined	Any filegroup that is explicitly created when the user first creates the database or modifies the database at a later time.

Default file group

If you create an object in the database without specifying the filegroup to which the object belongs, the object is assigned to the default filegroup. Only one filegroup can be specified as the default filegroup at any time. The files in the default filegroup must be large enough to hold all new objects that are not assigned to other filegroups.

The PRIMARY filegroup is the default filegroup unless the change is made using the ALTER DATABASE statement. However, system objects and tables are still assigned to the PRIMARY filegroup rather than the new default filegroup.