Why your SQL Log files are growing too big?

Every SQL Server database has at least two files; a data file and a transaction log file.  The data file stores user and system data while the transaction log file stores all transactions and database modifications made by those transactions.  As time passes, more and more database transactions occur and the transaction log needs to be maintained.  If your database is in the Simple recovery mode, then the transaction log is truncated of inactive transaction after the Checkpoint process occurs.  The Checkpoint process writes all modified data pages from memory to disk.  When the Checkpoint is performed, the inactive portion of the transaction log is marked as reusable.

Transaction Log Backups

If your database recovery model is set to Full or Bulk-Logged, then it is absolutely VITAL that you make transaction log backups to go along with your full backups.  SQL Server 2005 databases are set to the Full recovery model by default, so you may need to start creating log backups even if you haven't ran into problems yet.  The following query can be used to determine the recovery model of the databases on your SQL Server instance.

SELECT name, recovery_model_desc

FROM sys.databases

Be aware: Full backups are the starting point for any type of recovery process, and are critical to have in case you run into trouble.  In fact, you cannot create transactional log backups without first having created a full backup at some point.

The Full or Bulk-logged Recovery Mode

With the Full or Bulk-Logged recovery mode, inactive transactions remain in the transaction log file until after a Checkpoint is processed and a transaction log backup is made.  Note that a full backup does not remove inactive transactions from the transaction log.  The transaction log backup performs a truncation of the inactive portion of the transaction log, allowing it to be reused for future transactions.  This truncation does not shrink the file, it only allows the space in the file to be reused It is these transaction log backups that keep your transaction log file from growing too large. 

If your database recovery model is set to FULL, and you're not creating transaction log backups and never have, you may want to consider switching your recovery mode to Simple.  The Simple recovery mode should take care of most of your transaction log growth problems because the log truncation occurs after the Checkpoint process.  You'll not be able to recover your database to a point in time using Simple. To switch your recovery model to Simple mode, issue the following statement in your database:

       ALTER DATABASE YourDatabaseName

       SET RECOVERY SIMPLE

 

How to prevent / fix the problem:

 1) Convert the Recovery Model to Simple Recovery

If you are truncating the transaction logs, this means you are breaking the T-Log LSN (Log Sequence Numbers). This follows that if disaster comes, you would not be able to restore your T-Logs and there would be no option for you to do point in time recovery. If you are fine with this situation and there is nothing to worry, I suggest that you change your recovery model to Simple Recovery Model. This way, you will not have extra ordinary growth of your log file.
OR
2) Start Taking Transaction Log Backup
If your business does not support loss of data or requires having point in time recovery, you cannot afford anything less than Full Recovery Model. In Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits. If you are taking an hourly T-Log backup, your T-Log would grow until one hour but after this the T-Log backup would truncate all the ‘committed’ transactions once you take it. Doing this would lead the size of the T-Log not to go down much, but it would rather be marked as empty for the next hour’s T-Log to populate.

With this method, you can restore your database at Point of Time if a disaster ever happens at your server.

Not performing transaction log backups is probably the main cause for your transaction log growing too large.  However, there are other situations that prevent inactive transactions from being removed even if you're creating regular log backups.  The following query can be used to get an idea of what might be preventing your transaction log from being truncated:

SELECT name, log_reuse_wait_desc

FROM sys.databases

Possible reasons can be:

Long-Running Active Transactions

A long running transaction can prevent transaction log truncation.  These types of transactions can range from transactions being blocked from completing to open transactions waiting for user input.  In any case, the transaction ensures that the log remain active from the start of the transaction.  The longer the transaction remains open, the larger the transaction log can grow.  To see the longest running transaction on your SQL Server instance, run the following statement:

DBCC OPENTRAN

If there are open transactions, DBCC OPENTRAN will provide a session_id (SPID) of the connection that has the transaction open.  You can pass this session_id to sp_who2 to determine which user has the connection open:

EXECUTE sp_who2 spid

Alternatively, you can run the following query to determine the user:

SELECT * FROM sys.dm_exec_sessions
WHERE session_id = spid  --from DBCC OPENTRAN

You can determine the SQL statement being executed inside the transactions a couple of different ways.  First, you can use the DBCC INPUTBUFFER() statement to return the first part of the SQL statement

DBCC INPUTBUFFER(spid)  --from DBCC OPENTRAN

Alternatively, you can use a dynamic management view included in SQL Server 2005 to return the SQL statement:

SELECT     r.session_id,     r.blocking_session_id,     s.program_name,     s.host_name,      t.text FROM     sys.dm_exec_requests r     INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id     CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE     s.is_user_process = 1 AND     r.session_id = SPID  --FROM DBCC OPENTRAN

Backups

Log truncation cannot occur during a backup or restore operation.  If a database backup is keeping your log from being truncated you might consider cancelling the backup to relieve the immediate problem.

Transactional Replication

With transactional replication, the inactive portion of the transaction log is not truncated until transactions have been replicated to the distributor.  This may be due to the fact that the distributor is overloaded and having problems accepting these transactions or maybe because the Log Reader agent should be ran more often.  IF DBCC OPENTRAN indicates that your oldest active transaction is a replicated one and it has been open for a significant amount of time, this may be your problem.

Database Mirroring

Database mirroring is somewhat similar to transactional replication in that it requires that the transactions remain in the log until the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space will grow. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring.

Disk Space

It is possible that you're just running out of disk space and it is causing your transaction log to error. If you cannot free enough disk space on the drive that currently contains the log file then you may need to move the file to a drive with enough space to handle the log.  If your log file is not set to grow automatically, you'll want to consider changing that or adding additional space to the file.  Another option is to create a new log file for the database on a different disk that has enough space by using the ALTER DATABASE YourDatabaseName ADD LOG FILE syntax.

Shrinking the File

Once you have identified your problem and have been able to truncate your log file,  you may need to shrink the file back to a manageable size.  You should avoid shrinking your files on a consistent basis as it can lead to fragmentation issues.  However, if you've performed a log truncation and need your log file to be smaller, you're going to need to shrink your log file.  You can do it through management studio by right clicking the database, selecting All Tasks, Shrink, then choose Database or Files.  If I am using the Management Studio interface, I generally select Files and shrink only the log file.

This can also be done using TSQL.  The following query will find the name of my log file.  I'll need this to pass to the DBCC:

SHRINKFILE command.

SELECT name

FROM sys.database_files

WHERE type_desc = 'LOG'

Once I have my log file name, I can use the DBCC command to shrink the file.  In the following command I try to shrink my log file down to 1GB.

DBCC SHRINKFILE ('SalesHistory_Log', 1000)

Also, make sure that your databases are NOT set to auto-shrink.  Databases that are shrank at continuous intervals can encounter real performance problems.

TRUNCATE_ONLY and NOLOG

If you're a DBA and have ran into one of the problems listed in this article before, you might be asking yourself why I haven't mentioned just using TRUNCATE_ONLY to truncate the log directly without creating the log backup.  The reason is that in almost all circumstances you should avoid doing it.  Doing so breaks the transaction log chain, which makes recovering to a point in time impossible.

Moving forward

You should consider adding notifications to your system to let you know when your database files are reaching a certain threshold.  The more proactive you are in terms of alerts for these types of events, the better chance you'll have to correct the issue before it turns into a real problem…