Skip to content

MSSQL: The transaction log for database ‘xxx’ is full.

I got the above error when writing sql scripts to modify an existing database. The reason for the error is that the log file was set to fixed size, upon reach the db would not accept any more modifications.

The solution is quite simple. You can either set it to a bigger size manually, set it to autogrow or shrink the log file. There are a lot of articles covering each of the methods. I decided to shrink it back to 0 since it is not a productive db.

DBCC SHRINKFILE ('database_log_file', 0)

DBCC shrinks the desired log file back to 0. My problem was that the above command would always return: Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘database_log_file’ for database ‘database’ in sys.database_files. The file either does not exist, or was dropped.

I was working on a sql backup from my ISP. When I recovered the db to my local SQL server I renamed the database because the ISP was using a cryptic name for the db (actually my contract ID with the ISP). So, in order to shrink the log file I had to use the name of the original file which I got with this simple SQL statement:

use <database_name>
SELECT * from sysfiles;

The result showed the original log filename.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

*