SQL Server Logging

3 4

To understand how backup and restore operations work in conjunction with database recovery, you must first understand how SQL Server logging works. This section provides an overview of SQL Server logging and checkpoints and shows you how to back up the transaction log.

The Transaction Log

The transaction log is used to record all transactions and the modifications that those transactions make to the database. This record makes recovery possible. When a transaction is committed, the commit operation is not completed until the commit record for that transaction has been written to the transaction log. Because changes to the database are not necessarily written to disk immediately, this log is the only means whereby transactions can be recovered in the event of a system failure.

NOTE


If a data file is damaged and must be restored from backup, all of the transactions that have occurred on that data file must be replayed to recover the database to the state it was in just before the failure. Because the transaction log is critical for this process and has limited space, transaction log backups must be performed. You must save all of the transaction log data that has been generated since the last backup in order to recover the database. If you are interested in restoring only to the last backup, you can skip transaction log backup, but the current transaction log will not be able to recover the transactions that have occurred since that backup.

The Lazywriter Thread

Changes made to a database are first made to data inside the SQL Server cache. Cache data is changed first mainly to improve performance, because waiting for I/O operations is quite time consuming. These changes are eventually written to disk, but this process occurs in the background and is unknown to the user. Because modified pages are stored in the cache, a significant amount of time might elapse before these pages (called dirty pages) are written to disk by the SQL Server thread responsible for doing so. This thread is called the lazywriter thread.

The lazywriter thread uses a least recently used (LRU) page list, meaning that the data that has been used least recently is at the beginning of the list to be written to disk and that the data that has just been used is at the end of the list and is least likely to be written to disk. A page that is constantly being modified (and thus moving repeatedly to the end of the list) might never be written to disk by the lazywriter thread. This can increase the recovery time because many log records might have to be read in order to apply all the changes to that data. For example, on a large system with more than 1 gigabyte (GB) of RAM and many changes to the database, the recovery can take several hours.

In addition to the lazywriter thread, the checkpoint thread writes dirty pages to disk. (We'll look at the checkpoint thread in detail later in this section.)

Sequential Logging

Because the transaction log is a history of transactions, the I/O operations to the transaction log are mainly writes and are generally sequential in nature. In the event of a transaction rollback, the transaction log will be read, and the sequential nature of the I/O operations will be disrupted. Because rollbacks are fairly rare (since system crashes are rare and users do not undo transactions often), the I/O pattern in the transaction log is fairly stable. You can greatly enhance I/O performance by placing the transaction log on its own disk drive or RAID array, as explained in Chapters 4 and 5. You should protect the transaction log with RAID because the transaction log is so important to database recovery.

Transaction Log Size

Depending on the number of changes to your database, the transaction log can grow to be quite large. Because the transaction log consists of one or more files whose size is finite, the log will eventually be filled and therefore must be truncated periodically. The log is automatically truncated at the completion of a log backup, as we'll see later in this chapter.

NOTE


You can truncate a database's log without backing up the log by setting the database option trunc. log on chkpt to TRUE for that database. However, after doing so, you will not be able to back up the transaction log. Using this setting will make the database unrecoverable and is therefore not recommended.

Recovery Using the Transaction Log

In the event of a system failure in which data files are not damaged, the current transaction log is used to recover the database because only those transactions that have not yet been written to disk must be recovered. The number of pages that must be recovered depends on the number of dirty pages in the database, which, in turn, is governed by the checkpoint interval. A checkpoint writes dirty pages to disk to reduce the time needed to perform a recovery. Checkpoints and the checkpoint interval are described in detail in the section "Checkpoints" later in this chapter.

Transaction Log Properties

The transaction log has come a long way since Microsoft SQL Server 6.5. The SQL Server 2000 transaction log has the same characteristics as the Microsoft SQL Server 7 transaction log. These characteristics include the following:

  • The SQL Server transaction log is not treated in the same manner as a data file. Transactions are not written to and read from the transaction log in 8-kilobyte (KB) pages as they are for data files. The transaction log can now write in chunks of whatever size it needs—the information no longer follows the format of data pages. Thus, if the logwriter thread needs to write only a small amount, it does not have to write 8 KB of data. If the system is experiencing frequent updates, the logwriter thread can write using a large block size (16 KB, 32 KB, and so on).
  • The transaction log can be configured to automatically grow as needed. This feature allows more space to be added as required, but you should use it with caution to prevent the transaction log from growing uncontrollably and consuming the entire disk drive.
  • The transaction log can now be implemented across several files. These files can be configured to grow automatically as well. The transaction log files are not striped; they are used one after another. (Data striping is explained in Chapter 5.)
  • The transaction log can be shipped to other systems in order to be replayed on a standby system. This is known as log shipping and is described in more detail in the next chapter.

Nonlogged Operations

Now that you are familiar with the rules of logging and recovery, you are ready to learn about the exceptions to those rules. As mentioned, normally, all transactions and the changes they produce are recorded in the transaction log. However, you can perform certain operations that are not recorded in the log. These operations are called nonlogged operations. By performing a bulk operation (which uses a large amount of transaction log resources) as a nonlogged operation, you improve the performance of the operation.

Because nonlogged operations are not recorded in the transaction log, you must redo them if a recovery is necessary. Therefore, you should carefully consider the effects of enabling nonlogged operations before you do so. The operations that can be performed as nonlogged operations are as follows:

  • SELECT INTO
  • BULK COPY and Bulk Copy Program (BCP)
  • CREATE INDEX
  • Certain text operations

Later in this section, we'll look at the operations in the preceding list in a bit more detail.

In order to enable nonlogged bulk operations on a database, you must set the database to run in BULK_LOGGED recovery mode. The other recovery options are FULL and SIMPLE. Set these options by using the ALTER DATABASE command, as shown here for the Northwind database:

 ALTER DATABASE Northwind SET RECOVERY BULK_LOGGED ALTER DATABASE Northwind SET RECOVERY FULL ALTER DATABASE Northwind SET RECOVERY SIMPLE 

When BULKED_LOGGED recovery mode is used, the bulk operations described in the sections that follow will not be logged (with certain exceptions, which are explained in the next few sections), and all other operations are logged. If FULL recovery mode is chosen, all operations are logged. And when SIMPLE recovery mode is implemented, data can be recovered only to the last backup.

NOTE


Because the recovery mode defines your system's level of tolerance of failure, BULK_LOGGED recovery mode should be used with caution. If you use this mode, the performance of nonlogged bulk operations will improve, but in the event of a failure, recovery time will be increased.

SELECT INTO

The SELECT INTO statement is used to create a new table in the database. Because the SELECT INTO statement cannot be used to select data into an existing object, it cannot be used for updating data, only for creating data. This creation process can often be easily repeated; thus, SELECT INTO statement operations are quite suitable for being performed as nonlogged operations.

BULK COPY and BCP

In order for BULK COPY and BCP operations to be performed as nonlogged operations, several requirements must be met. These requirements are as follows:

  • The database option select into/bulkcopy must be set to TRUE.
  • The target table cannot have any indexes unless they are empty when the bulk copy starts.
  • The target table cannot be replicated, because transactional replication uses entries in the transaction log for its replication.
  • The TABLOCK hint must be specified in order to force a table lock.

These restrictions will allow the bulk copy operations both to save space in the transaction log and to run faster. However, in the event that the database needs to be restored from a backup, these nonlogged operations must be redone.

CREATE INDEX

CREATE INDEX statement operations are also suitable for being performed as nonlogged operations because indexes can be re-created as needed. Re-creating indexes is not difficult. However, if the tables are large, the process can be quite time consuming and resource intensive.

Text Operations

Text operations that can be performed as nonlogged operations include WRITETEXT and UPDATETEXT. To enable these operations to run without being logged, you simply use the BULK_LOGGED recovery option, as described earlier.

Checkpoints

A checkpoint is an operation that is used to synchronize the physical data files with the database cache to reduce the necessary recovery time in the event of a system failure. The amount of time needed to recover the database is determined by the amount of time since the last checkpoint and by the number of dirty pages in the buffer cache. Thus, decreasing the checkpoint interval will reduce the recovery time, but at a cost: the checkpoint process incurs a lot of overhead.

Checkpoints occur whenever a CHECKPOINT statement is issued, whenever SQL Server is shut down by means of a SHUTDOWN statement, whenever SQL Server is shut down by means of the Service Control Manager, and whenever SQL Server automatically issues a checkpoint.

Checkpoint Operations

The checkpoint process performs a number of operations, including the following:

  • Writes out all dirty data pages that were dirty at the beginning of the checkpoint All pages that contain changed data and have not yet been written to disk are now written to disk.
  • Writes a list of outstanding transactions to the transaction log This gives SQL Server data about the transactions that were in progress when the checkpoint was occurring. If a failure occurs, the recovery process uses this data to recover these transactions.
  • Writes all dirty log pages to disk This ensures that the log buffer is flushed to disk.
  • Stores checkpoint log records in the database This preserves a record of the checkpoint outside the transaction log, which is needed because the log might be backed up and truncated.

Checkpoint Interval Configuration

The checkpoint interval is defined by the SQL Server configuration option recovery interval. This parameter is set for the entire SQL Server system, not for each database, but checkpoints occur per database. This parameter specifies the number of minutes that SQL Server should take to recover each database in the event of a system failure. The default value 0 instructs SQL Server to determine the checkpoint interval for you—usually less than 1 minute. For systems that have a large amount of memory and a lot of insert and update activity, the default setting might cause an excessive number of checkpoints to occur. In that case, you might want to set the parameter to a higher value. If your users can tolerate a longer recovery in the event of a system failure (30 minutes, for example), transaction performance on your system will improve. You should set this parameter according to your company's ability to handle the downtime and the frequency of failures.

The checkpoint interval is also based on the number of records in the transaction log. It is not based on the system time or the size of the log. The more records in the transaction log, the shorter the checkpoint interval. As more changes are made, more records will be inserted into the transaction log, and consequently, SQL Server will configure the checkpoint interval to write those changes to disk more often. If few or no changes are made to the database, the transaction log will contain only a few records, and the checkpoint interval will be long.

You can change the recovery interval parameter in two ways: by using Enterprise Manager or by using Transact-SQL (T-SQL). To set recovery interval from Enterprise Manager, in the left-hand pane, right-click the name of the server for which you want to set this parameter and choose Properties from the shortcut menu to display the SQL Server Properties window. Click the Database Settings tab, shown in Figure 32-1, and specify the desired recovery interval, in minutes, in the Recovery Interval spin box.

Figure 32-1. The SQL Server Properties window.

To set recovery interval by using T-SQL, use the sp_configure stored procedure, as shown here:

 sp_configure "recovery interval", 1 GO 

You will see the following output:

 DBCC execution completed. If DBCC printed error messages,  contact your system administrator. Configuration option changed. Run the RECONFIGURE statement  to install. 

The change will not be implemented unless you run the RECONFIGURE command. If you are sure of your change, enter the following T-SQL statement:

 RECONFIGURE GO 

The RECONFIGURE command signals SQL Server to accept the configuration changes. SQL Server does not need to be restarted in order for changes to the recovery interval parameter to take effect.

To ensure that the setting you have made is actually in effect, use the following T-SQL statement:

 sp_configure "recovery interval" GO 

The output looks like this:

 name                    minimum maximum config_value run_value ----------------------  ------- ------- ------------ --------- recovery interval (min) 0       32767   1            1 

Notice that the recovery interval parameter has indeed been set.

CAUTION


The recovery interval parameter is an advanced option, and you should change it only after careful planning. Increasing the recovery interval setting will increase the time necessary to perform a database recovery.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net