Page 54 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes allocations. If you create an index on an existing table that has enough rows to generate eight
pages in the index, all allocations to the index are in uniform extents.

Figure 4.2

Self Assessment

State true or false:
1. All extents are stored in pages.
2. The fundamental unit of data storage in SQL Server is the page.

3. Data rows are put on the page serially, starting immediately after the header.
4. The minimum amount of data and overhead that is contained in a single row on a page is
8,060 bytes (8 KB).

5. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf
file, and instances are allowed to share this file.
6. An extent is eight physically contiguous pages, or 64 KB.

4.3 Database Files

SQL Server 2005 maps a database over a set of operating-system files. Data and log information
are never mixed in the same file, and individual files are used only by one database. Filegroups
are named collections of files and are used to help with data placement and administrative tasks
such as backup and restore operations.
SQL Server 2005 databases have three types of files:

Primary data files: The primary data file is the starting point of the database and points to
the other files in the database. Every database has one primary data file. The recommended
file name extension for primary data files is .mdf.
Secondary data files: Secondary data files make up all the data files, other than the primary
data file. Some databases may not have any secondary data files, while others have several
secondary data files. The recommended file name extension for secondary data files is
.ndf.

Log files: Log files hold all the log information that is used to recover the database. There
must be at least one log file for each database, although there can be more than one. The
recommended file name extension for log files is .ldf.
SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions
help you identify the different kinds of files and their use.

48 LOVELY PROFESSIONAL UNIVERSITY