Page 57 - DCAP508_DATABASE_ADMINISTRATION

Unit 4: SQL Server Storage Architecture

4.3.4 Database Snapshot Files Notes

The form of file that is used by a database snapshot to store its copy-on-write data depends on
whether the snapshot is created by a user or used internally:

A database snapshot that is created by a user stores its data in one or more sparse files.
Sparse file technology is a feature of the NTFS file system. At first, a sparse file contains no
user data, and disk space for user data has not been allocated to the sparse file.

Database snapshots are used internally by certain DBCC commands. These commands
include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC
CHECKFILEGROUP. An internal database snapshot uses sparse alternate data streams of
the original database files.

Notes Like sparse files, alternate data streams are a feature of the NTFS file system. The
use of sparse alternate data streams allows for multiple data allocations to be associated
with a single file or folder without affecting the file size or volume statistics.

4.4 Transaction Log

Every SQL Server 2005 database has a transaction log that records all transactions and the
database modifications made by each transaction. The transaction log is a critical component of
the database and, if there is a system failure, the transaction log might be required to bring your
database back to a consistent state. The transaction log should never be deleted or moved unless
you fully understand the ramifications of doing this.

4.4.1 Operations Supported by the Transaction Log

The transaction log supports the following operations:
Recovery of individual transactions.
Recovery of all incomplete transactions when SQL Server is started.
Rolling a restored database, file, filegroup, or page forward to the point of failure.
Supporting transactional replication.

Supporting standby-server solutions.
Recovery of Individual Transactions

If an application issues a ROLLBACK statement, or if the Database Engine detects an error such
as the loss of communication with a client, the log records are used to roll back the modifications
made by an incomplete transaction.

Recovery of all Incomplete Transactions when SQL Server is Started

If a server that is running SQL Server fails, the databases may be left in a state where some
modifications were never written from the buffer cache to the data files, and there may be some
modifications from incomplete transactions in the data files. When an instance of SQL Server is
started, it runs a recovery of each database. Every modification recorded in the log which may
not have been written to the data files is rolled forward. Every incomplete transaction found in
the transaction log is then rolled back to make sure the integrity of the database is preserved.

LOVELY PROFESSIONAL UNIVERSITY 51