Posted by arlene

Shrinking a transaction log is a little different from shrinking a data file. The main difference is when the process occurs.

When a data file is shrunk, the task is performed almost immediately, whereas with a transaction log it is not. The log file is marked, so SQL Server 2000 knows that it needs to be shrunk later on. When the transaction log is truncated or backed up, SQL Server 2000 will attempt to shrink the log to the size that has been specified. However, if active sections are on the end of the log, it will not be shrunk until the active sections are moved to the beginning of the transaction log.

An active portion (section) of the log cannot be truncated. It is used to recover the database to any point in time, so all incomplete transactions must be able to be identified so they can be rolled back. It is always present within the database so if the server fails, the database can be recovered when the server restarts.

Active transactions that have not yet been either committed or rolled back are marked as being incomplete transactions.

Some DBAs cause the active sections of the log to be moved (in quiet databases) to the beginning of the log by filling the log up with dummy transactions.

What defines a section of the transaction log as active? Transaction logs are made up of smaller logs. These smaller logs are referred to as Virtual logs.

Virtual logs make up the transaction log. They mark which transactions are active and, when the active transactions are committed or rolled back, the virtual logs are then freed to allow new active transactions to be written. After SQL Server 2000 has written to a virtual log, it will move on to the next virtual log, whether the previous one is free or not. Only when the end of the log file is reached is the beginning used again. However, if the log is totally full (full of active transactions) the log size is increased.

For example, we have a log file of 200MB on our database; this potentially could be made up of five virtual logs each 40MB in size.

When a virtual log contains no further active transactions in the database, the log is truncated or marked for shrinking, and the space can then be reallocated to further transactions.

Truncating the Transaction Log

Truncating the transaction log allows us to reallocate the space that we have on the transaction log after there are no further active transactions. In our SQLSpyNet database we set this to happen for us automatically, but in a production system this is not something that we would normally do.

We truncate the log in our development database because it is not mission critical that we have a point-in-time recovery plan. For the data we are using, it is perfectly okay to restore to last night’s backup rather than have the extra overhead of backing up our transaction logs every 10 minutes.

Keeping the Faith with Fail-Over Clustering

If we can’t manage memory or if we simply outgrow our resources, it’s time to consider adding new servers to help share the load and keep our single server from failing if something goes wrong. Clustering is the ability to add a group of SQL Servers together to support each other..

A cluster allows us to scale our applications indefinitely because when resources get a little low, we just add another server.

In a cluster, we can have many nodes (these are servers), but what happens when one of these nodes fails? This is where fail-over clustering support comes in. This is a process that defines what happens when one of the servers fails. This can be a little like load balancing. When one server fails the others take over the load that the server had. This makes scheduled maintenance a walk in the park!

In SQL Server 2000, clustering is easy to set up and support and is an improvement over SQL Server 7.0. In the setup of SQL Server 2000 you can configure clustering right from the CD by selecting the Virtual Server option from the installation wizard. However, you must install the Microsoft Cluster Service (MSCS) to select the Virtual Server option. Check Appendix B for information on customizing your installation and setting this option.

So clustering allows us to achieve high availability of our applications. If a server fails, we have another to take over. This means that although our response time might slow, our application databases will still be there!

Possibly related posts: (automatically generated)
Altering the Size of the Transaction Log Files

• Website Hosting Sever, some Pitfalls you need to avoid part 1
• Sharing Data, Database Design and Data Backup
• Website Hosting Sever, some Pitfalls you need to avoid part 3
• Network and Servers Technical Compare continue...
• Internet Commerce Virtual Warehouse
• Client/Server must Know
• The Post Office Protocol (POP3)
• Keeping Multiple Database Applications in Sync with Replication
• Ten Scamps of a Data Warehousing Failure continue...
• Using the New Wizards in SQL Server 2000