Tuesday, 20 May 2008

SQL Transaction Logs

SQL Database transaction logs can become quite large to put it politely if you are running full recovery model on the database but to be honest if not monitored closely, the other option is to put it in simple I don’t know of a company that would use simple as who would say that their database is not so important.

So how do you keep the transaction log from getting massive? Well the simple fact is that you need to backup the transaction log, but this just clears the inactive transaction it doesn’t shrink it so the question is what are you looking forwhen you say it’s getting to big, because you have two kinds of space to think of.

Space on disk (the physical space occupied by the transaction log)
and you have white space (the white space inside the transaction log)

White Space is what happens when you back up the transaction log is clears content. as its been backed up but it does not shrink the transaction log so the physical disk space doesn’t change however you can now fill the white space within the transaction log with new information and changes.

Depending on the way you grow your databases by auto grow or manually adding space as you go along as to how this will affect you.
If you manually grow the transaction log then you will need to make sure you backups happen frequently enough that you are not having to grow the transaction log every few days or hours.

Good rule of thumb is to backup the transaction log files regularly to delete the inactive transactions in your transaction log.
Design the transactions to be small.
Make sure that no uncommitted transactions continue to run for an indefinite time. Schedule the Update Statistics option to occur daily.
To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.

Note: In Microsoft SQL they are planing to change the command in later versions with ALTER INDEX statments.

The simple recovery model allows you to recover data only to the most recent full database or differential backup. Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.

The full recovery model uses database backups and transaction log backups to provide complete protection against failure. Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time. All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.

The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.

So once again, based on the information above it looks like the Full Recovery model is the way to go. Given the flexibility of the full recovery model, why would you ever select any other model? The following factors will help you determine when another model could work for you:

Select Simple if:
Your data is not critical.
Losing all transactions since the last full or differential backup is not an issue.
Data is derived from other data sources and is easily recreated.
Data is static and does not change often.
Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.)

Select Bulk-Logged if:
Data is critical, but logging large data loads bogs down the system.
Most bulk operations are done off hours and do not interfere with normal transaction processing.
You need to be able to recover to a point in time.

Select Full if:
Data is critical and no data can be lost.
You always need the ability to do a point-in-time recovery.
Bulk-logged activities are intermixed with normal transaction processing.
You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.

Note: will full recovery you will need to backup the transaction log frequently to prevent it growing out of control, if you don’t do this you will need to manually shrink it with TRUNCATE_ONLY command.

BACKUP LOG database WITH TRUNCATE_ONLY not recommended!!
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE. So you will be unable to restore to point in time other wise.

So to make it clear after all this you should do the following.
Best practice and way to prevent having to run the NO_LOG or TRUNCATE_ONLY commands is to put db in recovery model best for your database.

Do regular log backups to keep the transaction log a reasonable size, and run a maintenance plain that shrinks the log files and database on your server regularly, as well as reindexing and other fine tuning tasks.

No comments: