Thursday, April 20, 2006

SQL Server: Error 9002, Log file is full

I got this error when I had backed up a database but neglected to truncate its log file and shrink the database. Normally you don't notice this because the default in SQL Server is to allow unlimited growth of data files. Here's the error message:




Name:  Database log file is full. Back up the transaction log for the database to free up some log space.
Description:  Error: 9002, Severity: 17, State: 6
The log file for database 'FOO' is full. Back up the transaction log for the database to free up some log space.


Here's the code to fix it:




use master

BACKUP LOG foo
with truncate_only

/*
 * The second parameter is the percentage 
 * of free space you want to leave for 
 * further growth.
 */
DBCC SHRINKDATABASE (foo, 10)

Posted by Timothy Chen Allen at 11:29 PM

Labels:

1 comment:

  1. UnknownOctober 30, 2015 at 4:06 AM

    Thanks Timothy Chen Allen for sharing this informative article with us, it helps me a lot. I have found another article on same error, must see: http://www.sqlserverlogexplorer.com/error-9002-transaction-full/

    ReplyDelete

I moderate comments blog posts over 14 days old. This keeps a lot of spam away. I generally am all right about moderating. Thanks for understanding.

Subscribe to: Post Comments (Atom)