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. 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 2000 allows the following three types of database files:
Each database file has five properties: a logical filename, a physical filename, an initial size, a maximum size, and a growth increment. The properties of each file, along with other information about the file, are noted in the sysfiles table (shown 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 the number of pages or a percentage of the file size, depending on the value of status. |
status | 0x1 = Default device (unused in SQL Server 2000). 0x2 = Disk file. 0x40 = Log device. 0x80 = File has been written to since last backup. 0x4000 = Device created implicitly by CREATE DATABASE 0x8000 = Device created during database creation. 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. |