In this article I would like to show you the different methods you can focus on if your Transaction Log is Full for interim results. There could be many reasons of why the log file is full and you can find many resources either online or on BOL, but I want to concentrate on reacting to the situation and provide some quick, simple and straight forward steps on what you can do if your transaction log if Full.

There is a right tool for each job and using the wrong tool can have unpleasant side effects. So, let's follow some of the best practices and I will try my best to cover all of them in my first article. You can respond to the scenario above in different ways according to your needs and your available resources. Let's talk about the different ways of reacting, and I will provide you with the quick checklist so that you can bookmark them.

Truncating the Log

The first step which comes into a non-experienced DBA's mind is Truncating the log and reducing the log as much as they can in order to escape from the panicky situation. Well, this seems to be a good idea but the after effects of truncating the log could be disastrous (remember here we are focussing only on best practices) as it will break the log chain and a Full or Differential Backup is needed to restart the log chain(You can check at Gail Shaw's excellent article for more explanation in managing transaction logs and the effects of truncting the log). Instead you can Shrink the log file which does not affect performance and the size of the log files is physically reduced. Alter the database to modify the transaction log file to the appropriate size in one step:

DBCC SHRINKFILE (transactionloglogicalfilename, TRUNCATEONLY)

So, our first option is, 1) Shrinking the log file For more information -> Shrinking the Transaction log

Add a Log File

Quite contrary to the method above, you can also enlarge or add a log file. If disk space is available then you can enlarge your existing log file. You can also add a log file on another disk.

One point to note here even if you add an additional log file to the SQL Server it does not give you any signnificant performance. SQL server does not stripe across multiple transaction log files, it uses them sequentially.

- To add the log file you can use ADD LOG FILE clause of the ALTER DATABASE
- To enlarge the Transaction log file size you can use the modify file clause of the ALTER DATABASE

So our second option is, 2) Enlarge or Add a Log file. For more information see the > ALTER DATABASE (Transact SQL) command.

Now, this is the point where we realise the importance of log backups and database being on Full Recovery model. If you do not follow the recommended practices this is one of the consequences: your transaction log will grow full and hence the database will be inaccessible.

Backing Up

In the current situation where the transaction log is full, you can backup the log if your database is in Full/Bulk Logged Recovery model through which you can reclaim some space in your transaction log file. If your database is in the Simple Recovery model, a Full or a Full Differential backup should truncate the log using Checkpoints.

So, our third option is, 3) Backing up the log. For more information -> Backing up the Transaction log

Find Disk Space

Yet another alternative option you have is freeing up your disk space. You can achieve this by deleting or moving other files on the disk to reclaim space, which allows the database system to enlarge the log file automatically

So, our fourth option is, 4) Freeing up Disk space

Move the Log

Remember we were talking about best practices? One of the best practices is to isolate the transaction log file to its own physical disk and also you should make the disk configuration as efficient as possible. But, we will not go deep into the disk efficiency and all that, as our goal is to quickly respond to the full transaction log. One more option you have got is moving the log file to another drive with enough space because you don't want to get back to the same situation again. You can move the log file to a dedicated disk or move to a disk where there is sufficient space to accommodate the log file or for future growth.
You can move the log file using the stored procedures sp_detach_db and sp_attach_dbstored procedures but Microsoft recommends using ALTER DATABASE command

So, our fifth option is, 5) Moving the Log file to another disk. For more information-> ALTER DATABASE (Transact SQL)

Conclusion

To wrap up, You can consider any of the following methods to respond to a full transaction log efficiently and immediately according to your requirements:

1) Shrinking the log file
2) Enlarge or Add a Log file
3) Backing up the log
4) Freeing up Disk space
5) Moving the Log file to another disk

Transaction log is a critical piece and the most important part of a database, to "Manage a transaction log file" is the basic knowledge that a DBA should possess. Eventhough a DBA does not have control over the number or size of virtual files but a DBA definetely has control over the transaction log file size and should be able to manage efficiently when the transaction log is full.