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.