Error : 9002 The Transaction Log for database 'DB' is full due to 'LOG_BACKUP' Sql Server Error in Event Viewer for AX dynamics D365 F&O

Hello Devs!

This is one of the most common error faced by developers often working with database. This error is very much common for SQL servers as when the LOG size runs out in other words reaches 100% then we encounter this error. Being an AX developer, when the log usage reaches to 100% AX stops working and throw reconnecting request error. For this there is a rapid solution and i have written query for it.

There are some alternate Approaches also that will be discussed in future blog.

Step #1:

--this could be use to check you Database log file name and its location

use AxDB--in my case AxDB is my Database Name

select * from sys.database_files

Step #2:

--This could be used to see you log space usage for your particular log

DBCC SQLPERF (LOGSPACE)

Step #3:

--In my case Database log file name is D365DB_log

--use this query to shrink your log file size

USE AxDB;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE AxDB

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (D365DB_log, 1);

GO

-- Reset the database recovery model.

ALTER DATABASE AxDB

SET RECOVERY FULL;

GO

Hopefully this will resolve the error (9002).

SHARE