526
Part V: Enterprise Data Management
■ (^) Bulk-Logged: The transaction log contains all DML operations, but bulk insert
operations are only marked, not logged.
■ (^) Full: The transaction log contains all changes to the data fi le. This option provides
the greatest recovery potential.
Chapter 21 focuses on recovery planning and operations in detail.
You can set the recovery option in code with the ALTER DATABASE SET RECOVERY
command.
In Management Studio, you can change the recovery model by selecting Simple, Bulk-
logged, or Full in the Recovery Model drop-down list in the Database Properties Options tab
(refer to Figure 19-2).
To set the recovery model AdventureWorks2012 sample database to Bulk-Logged in code, do
the following:
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
Page Verify
Even though SQL Server works with 8KB data pages, the operating system I/O writes in 512-
byte sectors. Therefore, a failure might occur in the middle of a data-page write, resulting
in only some of the 512-byte sectors to be written to disk. This is known as a torn page.
You can tell SQL Server to tell you if a torn page occurs by using the PAGE_VERIFY data-
base option.
The PAGE_VERIFY database option can be set to one of the following values:
■ (^) CHECKSUM: This is the default level for PAGE_VERIFY. With this option, SQL
Server calculates a checksum over the contents of each page and stores the value in
the page header when a page is written to disk. When the page is read from disk,
the checksum is recalculated and compared to the original checksum value.
■ (^) TORN_PAGE_DETECTION: This option instructs SQL Server to toggle a bit on each
512-byte sector with each write operation. If all the sectors were written to disk,
then all the detection bits should be identical. If, on recovery, any of the bits are
different, then SQL Server can detect the torn-page condition and mark the data-
base as suspect.
■ (^) NONE: Database page writes do not generate a CHECKSUM or TORNPAGE
DETECTION value.
To change the PAGE_VERIFY option, you can either use Management Studio or T-SQL-code.
In Management Studio, you can change PAGEVERIFY by selecting CHECKSUM, TORN
PAGE_DETECTION, or NULL in the Page Verify box on the Database Properties Options tab
(refer to Figure 19-02).
c19.indd 526c19.indd 526 7/30/2012 5:43:13 PM7/30/2012 5:43:13 PM
http://www.it-ebooks.info