Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 7, 2002

SQL Server 2000 Administration in 15 Minutes a Week: More Database Creation Topics - Page 4

By Michael Aubert

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 stored on 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.

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 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 available.

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM