Database Files


A database file is nothing more than an operating system file. (In addition to database files, SQL Server also has backup devices, which are logical devices that map to operating system files or to physical devices such as tape drives. In this chapter, I won't be discussing files that are used to store backups.) A database spans at least two, and possibly several, database files, and these files are specified when a database is created or altered. Every database must span at least two files, one for the data (as well as indexes and allocation pages) and one for the transaction log.

SQL Server 2005 allows the following three types of database files:

  • Primary data files Every database has one primary data file that keeps track of all the rest of the files in the database, in addition to storing data. By convention, a primary data file has the extension .mdf.

  • Secondary data files A database can have zero or more secondary data files. By convention, a secondary data file has the extension .ndf.

  • Log files Every database has at least one log file that contains the information necessary to recover all transactions in a database. By convention, a log file has the extension .ldf.

Each database file has five properties that can be specified when you create the file: a logical filename, a physical filename, an initial size, a maximum size, and a growth increment. The value of these properties, along with other information about each file, can be seen through the metadata view sys.database_files, which contains one row for each file used by a database. Most of the columns shown in sys.database_files are listed in Table 4-1. The columns not mentioned here contain information dealing with transaction log backups relevant to the particular file, and I'll be discussing the transaction log in Chapter 5.

Table 4-1. The sys.database_files View

Column

Description

fileid

The file identification number (unique for each database).

file_guid

GUID for the file.

NULL = Database was upgraded from an earlier version of Microsoft SQL Server.

type

File type:

0 = Rows

1 = Log

2 = Reserved for future use.

3 = Reserved for future use.

4 = Full-text

type_desc

Description of the file type:

ROWS

LOG

FULLTEXT

data_space_id

ID of the data space to which this file belongs. Data space is a filegroup.

0 = Log file.

name

The logical name of the file.

physical_name

Operating-system file name.

state

File state:

0 = ONLINE

1 = RESTORING

2 = RECOVERING

3 = RECOVERY_PENDING

4 = SUSPECT

5 = Reserved for future use.

6 = OFFLINE

7 = DEFUNCT

state_desc

Description of the file state:

ONLINE

RESTORING

RECOVERING

RECOVERY_PENDING

SUSPECT

OFFLINE

DEFUNCT

size

Current size of the file, in 8-kilobyte (KB) pages.

0 = Not applicable

For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.

max_size

Maximum file size, in 8-KB pages:

0 = No growth is allowed.

1 = File will grow until the disk is full.

268435456 = Log file will grow to a maximum size of 2 terabytes.

growth

0 = File is fixed size and will not grow.

>0 = File will grow automatically.

If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB.

If is_percent_growth = 1, growth increment is expressed as a whole number percentage.

is_media_read_only

1 = File is on read-only media.

0 = File is on read/write media.

is_read_only

1 = File is marked read-only.

0 = File is marked read/write.

is_sparse

1 = File is a sparse file.

0 = File is not a sparse file.

(Sparse files are used with database snapshots, discussed later in this chapter.)

is_percent_growth

See description for growth column, above.

is_name_reserved

1 = Dropped file name (name or physical_name) is reusable only after the next log backup. When files are dropped from a database, the logical names stay in a reserved state until the next log backup. This column is relevant only under the full recovery model and the bulk-logged recovery model.




Inside MicrosoftR SQL ServerT 2005. The Storage Engine
Inside Microsoft SQL Server 2005: The Storage Engine (Solid Quality Learning)
ISBN: 0735621055
EAN: 2147483647
Year: 2004
Pages: 115
Authors: Kalen Delaney

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net