Database Reference
In-Depth Information
The difference between the FULL and BULK-LOGGED recovery models is in how SQL Server logs bulk copy
operations, such as
CREATE INDEX
,
ALTER INDEX REBUILD
,
BULK INSERT
,
INSERT INTO
,
INSERT SELECT
, and a couple
of others. In the FULL recovery model, those operations are fully logged. SQL Server writes log records for every data
row affected by the operation. Alternatively, in the BULK-LOGGED recovery model, SQL Server does not log bulk copy
operations on a row-by-row basis; rather it logs extents allocation instead. All bulk operations generate new (or a copy
of existing) objects, and extents deallocation rolls back the changes.
■
Note
the SiMpLe recovery model logs bulk operations in a similar manner as the BULK-LoggeD recovery model.
The BULK-LOGGED recovery model reduces transaction log load during bulk operations, but it comes at price.
First, SQL Server is not able to perform point-in-time recovery if bulk operations were running at a particular time.
Moreover, SQL Server must have access to the data files while performing log backups, and it stores data pages
modified by bulk operations as part of the backup file. This can lead to data loss if data files become unavailable in
between log backups. It is worth mentioning that non-bulk operations are always fully logged in the BULK-LOGGED
model, like they are in the FULL recovery model.
Choosing the right recovery model is a very important decision that dictates the potential amount of data loss in
case of disaster. It is an essential part of designing backup and disaster recovery strategies, which we will discuss in the
next chapter, “Designing a Backup Strategy.”
TempDB Logging
All user objects in
tempdb
must be transactionally consistent. SQL Server must be able to roll back transactions that
change data in
tempdb
in the same way as in the users' databases. However,
tempdb
is always recreated at SQL Server
startup. Therefore, logging in
tempdb
does not need to support the redo stage of crash recovery. Log records in
tempdb
store just the
old values
from the modified data rows, omitting
new values
.
This behavior makes
tempdb
a good candidate for a staging area for ETL processes. Data modifications in
tempdb
are
more efficient as compared to users' databases due to the lower amount of logging involved. Log records are not part of
transaction log activity in users' databases, which reduces the size of log backups. Moreover, those modifications are not
transmitted over the network if any transaction-log based high availability technologies are in use.
■
We will talk about high availability technologies in greater detail in Chapter 31, “Designing high
availability Strategy.”
Note
As we discussed in Chapter 12 “Temporary Tables,” using
tempdb
as a staging area introduces a set of challenges
during implementation. All of the data stored in
tempdb
would be lost in the case of a SQL Server restart or failover to
another node. The code must be aware of such a possibility and handle it accordingly.
Excessive Transaction Log Growth
Excessive transaction log growth is one of the common problems that junior or accidental database administrators
have to handle. It happens when SQL Server is unable to truncate the transaction log and reuse the space in the log
file. In such a case, the log file continues to grow until it fills the entire disk, switching the database to read-only mode
with this 9002 error: “
Transaction log full
.”
