SQL : SQL Transaction Log File Full Resolution

SQL Transaction Log File Full Resolution

In the event you recieve the following notification on a SQL server:

Error: 9002, Severity: 17, State: 6
The log file for database ‘DB_Name’ is full. Back up the transaction log for the database to free up some log space. 

The following SQL code will dump the transaction log and shrink the transaction log file:

sp_helpdb db_name

       USE db_name

GO

BACKUP LOG db_name WITH TRUNCATE_ONLY

DBCC SHRINKFILE (db_name_log, TRUNCATEONLY)

Use the command below to set a simple recovery mode if desired – this will stop this happening again but will stop you being able to perform a point in time recovery:

ALTER DATABASE db_name SETRECOVERY SIMPLE

Once this process has been performed you should ensure a full backup is taken of the database.