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.
In 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 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 and 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 pain 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.