The transaction log for database is full due to 'ACTIVE_TRANSACTION'

Question

The transaction log for database is full due to 'ACTIVE_TRANSACTION'

Orni

SSC Enthusiast

Points: 178
More actions
March 13, 2019 at 11:53 am

#382015

Hi all,
I am an accidental DBA that has been assigned to monitor and optimize the database (SQL Server 2016).
I am receiving the below error an is not clear to me how to fix it
The transaction log for database '' is full due to 'ACTIVE_TRANSACTION'

In fact it has occurred only two times till now while there were some long running jobs that blocked each other (an update in the table from a Agent job and a select in the same table from a SSRS report).
The update uses also e xp_cmdshell that remains open during all the time.

The recovery model of the database is simple and i do not see any problem with the available space in the server.
Please can anyone help me with this issue or just direct me to the appropriate direction to further investigate.

Thank you
Orni

bobba SSCommitted Points: 1605 More actions · Answer 1

Review the following URL, it may provide some guidance (copy and paste the url into a browser):
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fea1c821-5092-4e32-8559-f695d6a83a76/sql-server-2014-transaction-log-for-database-is-full-due-to-activetransaction-error?forum=sqldatabaseengine

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Orni - Wednesday, March 13, 2019 11:53 AM
Hi all,
I am an accidental DBA that has been assigned to monitor and optimize the database (SQL Server 2016).
I am receiving the below error an is not clear to me how to fix it
The transaction log for database '' is full due to 'ACTIVE_TRANSACTION'
In fact it has occurred only two times till now while there were some long running jobs that blocked each other (an update in the table from a Agent job and a select in the same table from a SSRS report).
The update uses also e xp_cmdshell that remains open during all the time.
The recovery model of the database is simple and i do not see any problem with the available space in the server.
Please can anyone help me with this issue or just direct me to the appropriate direction to further investigate.
Thank you
Orni

Another thing to check is that it could be that the log has a max size set and you have reached that limit. You can execute sp_helpfile in the database to see the files, max size, growth settings. Or in SSMS, right click on the database, select properties and select Files for the page on the left. Then you can view the same properties.

Sue

Alejandro Santana SSCommitted Points: 1835 More actions · Answer 3

I have been getting those messages at the QA and Development instances and its like Sue_H said, check your log size, when its full that's the message it will throw.
Change the max size.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Orni - Wednesday, March 13, 2019 11:53 AM
Hi all,
I am an accidental DBA that has been assigned to monitor and optimize the database (SQL Server 2016).
I am receiving the below error an is not clear to me how to fix it
The transaction log for database '' is full due to 'ACTIVE_TRANSACTION'
In fact it has occurred only two times till now while there were some long running jobs that blocked each other (an update in the table from a Agent job and a select in the same table from a SSRS report).
The update uses also e xp_cmdshell that remains open during all the time.
The recovery model of the database is simple and i do not see any problem with the available space in the server.
Please can anyone help me with this issue or just direct me to the appropriate direction to further investigate.
Thank you
Orni

How big is the log file in question?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)