resolution for the error 'transactional Log for the database is FULL'..

Posted by Bandi under Error and Solution on 7/15/2014 | Points: 10 | Views : 1279 | Status : [Member] [MVP] | Replies : 0

today, I faced the below error while updating large number of records..

Msg 9002, Level 17, State 2, Line 8
The transaction log for database 'DatabaseName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

To resolve the issue, i have tried to look into the log_reuse_wait_desc column data in sys.databases for the name = 'DatabaseName'

SELECT log_reuse_wait_desc, name, state_desc FROM sys.databases WHERE name = 'DatabaseName'

If the log_reuse_wait_desc = 'LOG_BACKUP' then we have to take Transaction LOG backup so that the inactive space in the LOG Space will be truncated and it becomes usable Space.....

In my case i had LOG_BACKUP status for log_reuse_wait_desc, So i took the Transactional Log Backup for that database. Then i was able to do as usual DML operations on the database.

The steps to take Transactional Log Backup is
1. In SQL Server Management Studio, Expand Databases
2. Lets assume the database is TestDB. Right-click TestDB database, point to Tasks, and then click Back Up. The Back Up Database dialog box appears.
3. In the Database list box, verify the database name.
4. Verify that the recovery model is either FULL or BULK_LOGGED.
5. In the Backup type list box, select Transaction Log.

Mark This Response as Answer


(No response found.)

Login to post response