The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION’ | Sql server – log is full due to ACTIVE_TRANSACTION

by

This article helps to resolve,

The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION.’ or

Sql server – log is full due to ACTIVE_TRANSACTION

As a DBA we face issue like the transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION. Because of that disk is getting full and we are not able to shrink that log file.

MSDB is one of the important SYSTEM Database in SQL Server. It stores history related to SQL server jobs. Also stored system table related to database mail and many more. MSDB database recovery model is simple so in normal scenario transaction log file get truncated on every system checkpoint or SQL restart.

But some time log file of msdb database grow due to maintenance plan history table which store data related to maintenance plan and you will get.

Resolution 1 : The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION’

In this case you can check table count and identify large table in msdb database.

You can perform below step once you identify large table and if there is any open transaction.

1. Check database log file size and log percentage using below query.
 
dbcc sqlperf(logspace)

Also check reason for log file full using below query.

2. Once you find out log percentage usage of MSDB database you can try to shrink file using if it allows.

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBLog' , 0, TRUNCATEONLY)
GO

3. Sometime above step will not work, and you will not be able to shrink file in that case you need to get table details which are taking most of the space and try to cleanup those.  Most cases it will be maintenance plan history table or backup history table.

Run Below query and check if you are able to delete history data.

checkpoint
msdb.dbo.sp_maintplan_delete_log @oldest_time='01-31-2023';

You can create cleanup job to clear all history. Create maintenance plan for that using below task.

But post performing above steps still MSDB is not shrinking, and cleanup job will also fail and you will get.

Resolution 2 : The transaction log for database ‘msdb’ is full due to ‘ACTIVE_TRANSACTION.’

error again.

In such scenario, you need to perform below step.

Add extra log file in MSDB database. You need to change path in below script and make sure you will create file on another location.

USE [master]
GO
ALTER DATABASE [msdb] ADD LOG FILE ( NAME = N'MSDBLog_1',
FILENAME = N'C:\SQL2019\MSDBLog_1.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

Once new file added try to shrink file with moving all data from old log file to new file using below script.

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBLog' , EMPTYFILE)
GO

Once data moved log file will be shrunk and disk space issue will get resolved. Once you are able to be shrunk file you can remove newly log file create if not required.

Leave a Comment