Error 9002 Transaction log full SUSDB

Question

I am running a WSUS Server on Windows Server 2016. The WSUS Service stops because transition log is full.

The Event viewer says:

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.

When i start SQL Management Studio and connect to the database i tried to follow the technet article for Troubleshooting a Full Transaction Log. It says that i could Back up the log. So i select the database SUSDB and try to run a backup by pointing to Tasks and klicking on Back Up.... But as Backup Type i can't select Transition Log, because the Recovery Model is SIMPLE.

Then i tried to change the Recovery Model to Full in Database Properties >> Options. But when i try to do that, it says

Alter failed for Database 'SUSDB'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.
The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.
ALTER DATABASE statement failed.
Could not write a checkpoint record in database SUSDB because the log is out of space.

Also in Database Properties >> Files the SUSDB_log has a Limit to 2.097.152 MB but the actual size of the log file is 220 MB on my disk. The disk free space is 230 GB.

After that i tried to run the query:

ALTER DATABASE susdb SET RECOVERY FULL;

But this also leaded to the error

The transaction log for database 'SUSDB' is full due to 'CHECKPOINT'.

I'm not able to restart the WSUS Service becaus of this error. Has anyone any suggestions what to do?

Answer 1

First of all you should find out why your log cannot be truncated:

select log_reuse_wait_desc
from sys.databases
where name = 'SUSDB';

It's certainly NOT log backup that it's waiting for, because recovery model of your db is simple.

You should also check free space on the disk where the log file is living, and if someone limited the growth of your log file:

use susdb;
select physical_name,
       size * 8 / 1024 as size_in_Kb, 
       max_size * 8 / 1024 as max_size_in_Kb, 
       growth, 
       is_percent_growth
from sys.database_files
where file_id = 2;


exec xp_fixeddrives;

For now, please update your question with the results of 3 queries above.