what does this error mean?

Question

Post reply

what does this error mean?

water490

SSC Eights!

Points: 959
More actions
February 25, 2024 at 6:45 am

#4384419

I was running a SP and I got this. This is the first time I have ever received this. What does it mean? How do I fix it so it doesn't happen again?
Msg 9002, Level 17, State 4, Procedure dbo.Query, Line 223 [Batch Start Line 2]
The transaction log for database 'myDB' is full due to 'ACTIVE_TRANSACTION'.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16155 More actions · Answer 1

and what have you found in google? lots of hits including some in this site

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

water490 wrote:

I was running a SP and I got this. This is the first time I have ever received this. What does it mean? How do I fix it so it doesn't happen again?
Msg 9002, Level 17, State 4, Procedure dbo.Query, Line 223 [Batch Start Line 2] The transaction log for database 'myDB' is full due to 'ACTIVE_TRANSACTION'.

Usually, it means one of three things...

You have a database in the FULL or BULK LOGGED Recovery Model that you've taken a FULL backup on. That starts the "Log File Chain", which must be accompanied by regular Transaction Log Backups in order to "truncate" the log file instead of it just growing forever.If this is the problem, the fix is to do one of two things... instantiate an actual, proper, viable backup plan for the database according to the RPO and RTO requirements for the company and the data. If you don't know what those are, then you have some serious study about all of this ahead of you because it's going to fall on you to make it happen.
It could also be that you have some bad code that did an insane number of Inserts, Updates, Deletes or "page moves". "Page moves" occur for things like index maintenance and the worst offender there is actually the use of REORGANIZE, I did an experiment a long time back on a 147 GB Clustered Index Table that only had 12% logical fragmentation but had also suffered a fair number of row deletes in the "hot spot". The log file exploded from just 20GB to 225GB because the REORGANIZE at only 12%.If problem #1 is not present, then the error contains the name of the proc and the row number of the code that caused the issue. Start there for troubleshooting. Perhaps the log file has been limited to a too-small side OR...
It can also be because of badcode that's doing an Insert (or similar) and it has an accidental many-to-many JOIN in it.The fix here is obvious... the code needs to be fixed. Don't just expand the size of the log file because that's not a good fix for bad code.

For problems #2 or #3 above, I've not actually tried it on any database other than TempDB but you could modify the code explained in the following "Erik Darling" article to find out what is causing the growth the next time it happens. Of course, you'll first need to get your logfile down to size first.

https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/

--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)