Page 59 - DCAP508_DATABASE_ADMINISTRATION

Unit 4: SQL Server Storage Architecture

10. Every SQL Server 2005 database has a ………………… that records all transactions and the Notes
database modifications made by each transaction.

4.5 Summary

The Resource database is a read-only database that contains all the system objects that are
included with SQL Server. SQL Server system objects, such as sys.objects, are physically
persisted in the Resource database, but they logically appear in the sys schema of every
database. The Resource database does not contain user data or user metadata.
The physical file names of the Resource database are mssqlsystemresource.mdf and
mssqlsystemresource.ldf.
SQL Server cannot back up the Resource database.
The Resource database should only be modified by or at the direction of a Microsoft
Customer Support Services (CSS) specialist. The ID of the Resource database is always
32767.

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per
megabyte. Each page begins with a 96-byte header that is used to store system information
about the page.

Extents are the basic unit in which space is managed. An extent is eight physically
contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

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, secondary and Log files.

The logical_file_name is the name used to refer to the physical file in all Transact-SQL
statements.
The os_file_name is the name of the physical file including the directory path.

Every SQL Server 2005 database has a transaction log that records all transactions and the
database modifications made by each transaction.

4.6 Keywords

Extent: Extents are a collection of eight physically contiguous pages and are used to efficiently
manage the pages.
File Groups: File groups are named collections of files and are used to help with data placement
and administrative tasks such as backup and restore operations.
logical_file_name: The logical_file_name is the name used to refer to the physical file in all Transact-
SQL statements.

os_file_name: The os_file_name is the name of the physical file including the directory path.
Page: The fundamental unit of data storage in SQL Server is the page
Resource Database: The Resource database is a read-only database that contains all the system
objects that are included with SQL Server.
Transaction Log: Every SQL Server 2005 database has a transaction log that records all transactions
and the database modifications made by each transaction.

LOVELY PROFESSIONAL UNIVERSITY 53