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, to physical devices such as tape drives , or even to named pipes.) 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 7 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, the name of a primary data file has the extension MDF.
  • Secondary data files A database might have zero or more secondary data files. By convention, the name of a secondary data file has the extension NDF.
  • Log files Every database will have at least one log file that contains the information necessary to recover all transactions in a database. By convention, a log file will have the suffix LDF.

Database files each have several properties. These include a logical filename, a physical filename, an initial size, a maximum size , and a growth increment. The properties of each file are noted in the sysfiles table (depicted in Table 5-1), which contains one row for each file used by a database.

Table 5-1. The sysfiles table.

Column Name Description
fileid The file identification number (unique for each database).
groupid The filegroup identification number.
size The size of the file (in 8-KB pages).
maxsize The maximum file size (in 8-KB pages). A value of 0 indicates no growth, and a value of -1 indicates that the file should grow until the disk is full.
growth The growth size of the database. A value of 0 indicates no growth. Can be either number of pages or percentage of file size, depending on value of status.
status 0x2 = Disk file
.0x40 = Log device.
0x80 = File has been written to since last backup.
0x100000 = Growth is in percentage, not pages.
name The logical name of the file.
filename The name of the physical device, including the full path of the file.

Inside Microsoft SQL Server 7.0
Inside Microsoft SQL Server 7.0 (Mps)
ISBN: 0735605173
EAN: 2147483647
Year: 1999
Pages: 144 © 2008-2017.
If you may any questions please contact us: