The log file for database is full

Question

So our SQL Server 2000 is giving me the error, "The log file for database is full. Back up the transaction log for the database to free up some log space."

How do I go about fixing this without deleting the log like some other sites have mentioned?

Additional Info: Enable AutoGrowth is enabled growing by 10% and is restricted to 40MB.

Answer 1

I don't think renaming or moving the log file will work while the database is online.

Easiest thing to do, IMO, is to open the properties for the database and switch it to Simple Recovery Model. then shrink the database and then go back and set the DB to Full Recoery Model (or whatever model you need).

Changing the logging mode forces SQL Server to set a checkpoint in the database, after which shrinking the database will free up the excess space.

Answer 2

You have the answer in your question: Backup the log, then it will be shrunk. Make a maintenance plan to regularly backup the database and don't forget to select "Backup the transaction log". That way you'll keep it small.

Answer 3

Well you could take a copy of the transaction log, then truncate the log file, which is what the error message suggests.

If disk space is full and you can't copy the log to another machine over the network, then connect a drive via USB and copy it off that way.

Answer 4

If it's a non production environment use

dump tran <db_name> with no_log;

Once this has completed shrink the log file to free up disk space. Finally switch database recovery mode to simple.

Answer 5

My friend who faced this error in the past recommends:

Try

• Backing up the DB. The maintenance plan includes truncation of these files.
• Also try changing the 'recovery mode' for the DB to Simple (instead of Full for instance)

Cause: The transaction log swells up due to events being logged (Maybe you have a number of transactions failing and being rolled back.. or a sudden peaking in transactions on the server )

Answer 6

You may want to check related SO question:

• How do you clear the transaction log in a SQL Server 2005 database?