Because pages that have been recently
changed are only stored in memory, they are susceptible to
system crashes and power outages. To overcome this problem,
SQL Server can use the log information to restore the
modifications to the database if one of these events
occurs. For example, if the power goes out (and our UPS has
failed!) we would have lost updated pages stored in memory.
In order to recover these pages, SQL Server
starts in recovery mode. By using the transaction
log, SQL Server can reapply the changes to the copy of the database
the hard drive.
This brings up another problem
, however; what happens if we have pages that are heavily
updated? How long will this recovery process take? The
lazywriter may not write these heavily accessed pages to
disk. In addition, the number of modifications that would
need to be applied from the transaction log would be very
high. Therefore the amount of time needed to recover the database
after a failure could be enormous. To solve this problem,
SQL Server uses checkpoints to shorten the time needed to
recover a database in the event of a failure. When a
checkpoint occurs all dirty pages that were dirty when the
check point started are written to disk.
addition, the checkpoint also writes outstanding
transactions to the transaction log. In the event a failure
occurs, the recovery process can recover from the last
checkpoint, and it only needs to reapply the transactions
that happened after the last checkpoint or that were
outstanding at the time of the last checkpoint.
So when do checkpoints occur? The
checkpoint interval is based on the recovery interval,
which is a setting global to an instance of SQL Server that
specifies the maximum number of minutes per database SQL
Server would need to perform the recovery process. To
change the recovery interval, you can right-click your SQL
Server in the Enterprise Manager tree pane and select
properties. Once the properties window appears, select the
Database Settings tab.
The default value for the recovery
interval is 0. This means SQL Server will automatically set
the recovery interval for you. Leaving the recovery
interval 0 usually results in the checkpoint interval being
less than one minute. If your server has lots of memory and
your database has many inserts and updates, you may find
the default value causes an excessive number of checkpoints
to occur. To improve performance you may want to set the
recovery interval to 15 or even 30 minutes, depending on
the maximum acceptable downtime.
In addition to the checkpoint
interval, the checkpoint process also occurs when the SQL
Server or the computer are shut down properly. For more
information on the recovery interval, see "recovery interval
Option" in the SQL Server 2000 Books Online.
Now, with a good understanding of what
the transaction is, how the transaction log is used in the
recovery process, and how dirty pages get written to disk,
let's take a closer look at how transactional information
is stored in a transaction log. Unlike data files,
transaction logs are not stored in 8 KB pages, nor do they
use file groups.
When transactional information needs
to be logged, it is written to the disk in whatever size is
needed. For example, if the information being logged is
small, it does not need to write a whole 8 KB page.
Additionally, if a large amount of information needs to be
logged, it can be done by writing a large block such as 16 KB
or 32 KB.
Because logging is a serial operation,
putting a log file on its own hard drive or RAID array can
improve performance. The only time the heads of the disk
drive need to move is when a roll back occurs. The rest of
the time the drive heads are in place (or nearly in place)
for the next write to the log.
That wraps things up for this
week. So, where do we go from here? Now that you have a
good understanding of what the transaction log is used for,
next week we can look at the different backup strategies
As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this
series to be a useful one, and I'm looking forward to your feedback.
See All Articles by Columnist Michael Aubert