When using Bulk-Logged Recovery, the transaction log behaves exactly as if it were in
Full Recovery mode, with one exception: bulk operations are minimally logged. Bulk
operations include:
CHAPTER 2 n MAKING DATABASE BACKUPS 18
??? Bulk imports of data, such as those you might perform using Bulk Copy Program
(BCP), the BULK INSERT command, or the OPENROWSET command with its BULK clause
??? Large object (LOB) operations, such as WRITETEXT or UPDATETEXT for TEXT, NTEXT, and
IMAGE columns
??? SELECT INTO statements
??? CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD, or DBCC REINDEX
As all of these operations are logged in Full Recovery mode, the transaction log can
grow tremendously. The advantage to the Bulk-Logged Recovery mode is that it prevents
unwanted or unanticipated log growth.
Whenever a bulk-logged operation occurs, SQL Server only records the IDs of the
data pages that have been affected (see Figure 2-4). SQL Server pages have internal IDs,
such as 5:547, so you can squeeze a larger amount of page IDs into a small portion of
the log.
Figure 2-4. Bulk-Logged Recovery is designed to minimize the use of the active
transaction log.
Pages:
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82