sql server - The transaction log for database 'databasename' is full.

Question

The transaction log for database '' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases. Please Help me to solve this issue. Thanks in advance.

Answer 1

Undoubtedly, you are running the database in Full Recovery Mode, and failed to take Transaction log backups.

Right-click your database in SQL Server Manager, and check the Options page. Switch Recovery Model from Full to Simple then right-click the database again. Select Tasks Shrink, Files. Shrink the log file to a proper size (I generally stick to 20-25% of the size of the data files).

Switch back to Full Recovery Model, and take a full database backup straight away.

You now need to figure out whether you need the ability to recover your database to a point-in-time. If so, schedule regular transaction log and perhaps differential backups according to the requirements of your business.

In case the business can live with a certain amount of data loss, schedule full backups and switch to Simple mode.