Shulou(Shulou.com)06/01 Report--

SQLServer maps the database to a set of operating system files.

Data and log information are never mixed in the same file, and a file can only be used by one database. Filegroups are named collections of files that assist in master data layout and administrative tasks, such as backup and restore operations.

Database file

SQLServer database has three types of files: .MDF, .ldf, ndf (primary data file, log file, secondary data file). Note: the suffix name of the file can be named independently.

Master data file: each database will have a master database file, which is the starting point of the database and points to other files in the database.

Secondary data files: the database can have no secondary data files or multiple data files

Log files: log files are used to store the log information of the database and contain all the log information used to restore the database

In SQLServer, the location of all files in the database is recorded in the master file of the database and in the master database. In most cases, the SQLserver database engine uses the file location information in the master database.

The database engine initializes the file location entry in the master database with the file location information of the primary file in the following cases:

When attaching a database using a CREATE DATABASE statement with the FOR ATTACH or FOR ATTACH_REBUILD_LOG option.

When upgrading from SQL Server version 2000 or version 7.0.

When restoring the master database.

Logical and physical file names (SQLServer files have two names: logical_file_name, os_file_name)

Logical_file_name is the name used when referencing a physical file in all T-SQL statements. The logical file name must conform to the SQLSERVER identifier rules and must be unique.

Os_file_name is the physical file name that includes the directory path. It must conform to the operating system file naming convention.

Data file page

The pages in the SQL Server data file are numbered sequentially, and the first page of the file begins with 0. Each file in the database has a unique file ID number. To uniquely identify pages in the database, you need to use both the file ID and the page number. The following example shows the page numbers in a database that contains 4-MB primary data files and 1-MB secondary data files.

The first page of each file is the header page of a file that contains information about the attributes of the file. The other pages at the beginning of the file also contain system information (such as allocation mapping). A system page stored in the main data file and the first log file is a database boot page that contains database property information.

File size

SQLServer files can grow automatically from the size they originally specified. When you define a file, you can specify a specific increment. Each time the file is populated, its size increases in this increment. If there are multiple files in the filegroup, they do not grow automatically until all the files are filled. When filled, these files will grow in a cycle.

Each file can also specify a maximum size. If you do not specify a maximum size, the file can grow until all available space on the disk is used up. This feature is particularly useful if SQL Server is embedded in an application as a database and users of that application cannot quickly contact the system administrator. Users can make files grow automatically as needed to reduce the administrative burden of monitoring free space in the database and manually allocating additional space.

Database snapshot file

The file format in which database snapshots store their "copy on write" data depends on whether the snapshot is created by the user or used internally:

User-created database snapshots store their data in one or more sparse files. Sparse file technology is a function of NTFS file system. First, sparse files do not contain any user data, and no disk space is allocated for user data for sparse files. For general information about using sparse files in database snapshots and how database snapshots grow, see how database snapshots work and understand sparse file sizes in database snapshots.

Database snapshots are used internally through specific DBCC commands. These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. Internal database snapshots use sparse alternate data streams from the original database files. Like sparse files, alternate database streams are a feature of the NTFS file system. With sparse alternate data streams, multiple data allocations can be made to associate them with a single file or folder without affecting file size or volume statistics.

Database filegroup

For ease of allocation and management, database objects and files can be divided into filegroups together. There are two types of filegroups:

Primary filegroup

The primary filegroup contains the master data file and any other files that are not explicitly assigned to other filegroups. All pages of the system table are assigned to the primary filegroup.

User-defined filegroup

A user-defined filegroup is any filegroup specified by using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

Log files are not included in the filegroup. Log space is managed separately from data space.

A file cannot be a member of more than one filegroup. Tables, indexes, and large object data can be associated with specified filegroups. In this case, all their pages will be assigned to the filegroup, or tables and indexes will be partitioned. The data of partitioned tables and indexes is divided into units, each of which can be placed in a separate filegroup in the database. For more information about partitioned tables and indexes, see partitioned tables and partitioned indexes.

One filegroup is designated as the default filegroup in each database. If no filegroup is specified when the table or index is created, it is assumed that all pages are allocated from the default filegroup. Only one filegroup can be the default filegroup at a time. Members of the db_owner fixed database role can switch default filegroups from one filegroup to another. If no default filegroup is specified, the primary filegroup is used as the default filegroup.

File and filegroup exampl

The following example creates a database on the SQL Server instance. The database includes a master data file, a user-defined filegroup, and a log file. The primary data file is in the primary filegroup, while the user-defined filegroup contains two secondary data files. The ALTER DATABASE statement specifies a user-defined filegroup as the default. The table is then created by specifying a user-defined filegroup.

Example:

USE master

GO

CREATE DATABASE MyDB

ON PRIMARY

(NAME='MyDB_Primary',FILENAME='c:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ data\ MyDB_Prm.mdf'

SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB)

FILEGROUP MyDB_FG1

(NAME = 'MyDB_FG1_Dat1'FILENAME =' c:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ data\ MyDB_FG1_1.ndf'

SIZE= 1MB, MAXSIZE=10MB,FILEGROWTH=1MB)

(NAME = 'MyDB_FG1_Dat2',FILENAME =' c:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ data\ MyDB_FG1_2.ndf'

SIZE= 1MB maxie 10MB maxim FILEGROWTH 1MB)

LOG ON

(NAME='MyDB_log',FILENAME ='c:\ Program Files\ Microsoft SQL Server\ MSSQL10_50.MSSQLSERVER\ MSSQL\ data\ MyDB.ldf'

SIZE=1MB,MAXSIZE=10MB, FILEGROWTH=1MB)

GO

ALTER DATABASE MyDB

MODIFY FILEGROUP MyDB_FG1 DEFAULT

GO

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.