Problem

When running some transactions on a database the following error appears:

Msg 9002, Level 17, State 4, Line 2

The transaction log for database 'Database_Name' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution

After the transactions are committed and the data pages are written on the disk, SQL Server tries to clear the transaction log data that is not needed anymore. By doing, it tries to reuse the transaction log for future operations. There are several possible reasons why the transaction log cannot be reused: open transactions, replication, a transactional backup is necessary, etc

To find out which one applies to your case execute the following statement:

SELECT name, log_reuse_wait_desc FROM sys.databases

 Keep in mind that it is possible to have more than one reason preventing log reuse. The log_reuse_wait_desc column will show only one of the reasons and, after fixing it and query the sys.databases view again, you can see a different log_reuse_wait reason.

To better interpret the log_reuse_wait_desc column you can access the following link:

http://msdn.microsoft.com/en-us/library/ms345414.aspx

The above error appears because the transaction log is full (has reached the maximum file size).

...

To solve this issue you have the following options:

1. Increase the maximum transaction log file size

 To increase the maximum transaction log file size, right click in SSMS on the database and choose Properties from the drop down menu. After the Properties window is displayed, go to Files section and click on both the data and log Autogrowth / Maxsize column and increase the maximum transaction log file size or set it to Unlimited.

2. Backup transaction log

 If it is a production database you probably have the Recovery Model set to Full and perform transactional log backups, to be able to recover your database to a point in time.

 In this case, to avoid the ?The transaction log is full? error, you must back up your transaction log file. While performing the backup of your transaction log file, the SQL Server automatically truncates the inactive part of the transaction log. The inactive part contains the completed transactions, and is no longer used by SQL Server during the recovery process. In this way, SQL Server reuses this inactive space in the transaction log, instead of allowing the transaction log to continue to grow and to use more space.

 To back up the transaction log right click in SSMS on the database and choose Properties from the drop down menu and choose Task -> Back Up.

In the Back Up Database window, verify the database name (the Recovery model must be either FULL or BULK_LOGGED) and choose Transaction Log in the drop down menu of Backup type.

Go to Destination and choose the location on the disk where you want to store the backup and then click OK button.

If you have to do this often is a good idea to increase the frequency of the Full or Transaction log backups. 

3. Truncate the transaction log

If it is a test database that is not important and it doesn?t need to be back up, you set the recovery model to Simple and truncate the log file.

To change the Database Recovery Model from Full to Simple, right click in SSMS on the database and choose Properties from the drop down menu. After the Properties window is displayed, go to Options and change the Recovery Model to Simple.

After changing the Recovery Model, you need to shrink the transactional log to free the space. To do this right click again in SSMS on the database and choose Task -> Shrink -> Files.

In the Shrink File window select the File type as 'Log' and the name of the transactional log file will be displayed in the File name drop down list.

In Shrink action choose Release unused space option (radio button) and then click OK button.

The same thing can be accomplished by running the bellow command after replacing database name and the transaction log file name (use sp_helpdb [Database_Name] command to find out the log file name).

USE [Database_Name]
GO
DBCC SHRINKFILE (N'Database_Name_log' , 0, TRUNCATEONLY)
GO