Log full due to 'ACTIVE_TRANSACTION'

Question

Log full due to 'ACTIVE_TRANSACTION'

PSB

SSChampion

Points: 13195
More actions
December 10, 2015 at 11:33 am

#310105

Hi,
I am receiving the error
The transaction log for database 'ITT' is full due to 'ACTIVE_TRANSACTION'.
when executing a procedure

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ok, so you're filling the log with a transaction. What's the question?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PSB SSChampion Points: 13195 More actions · Answer 2

I receive

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

and

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I notice the G drive where the log is, is full .

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Ok, and?

The log is full, you've seen that. It's full because a transaction is open and hence the space in the log can't be reused. The procedure with the transaction is failing with an error, fully expected in this situation. Either do fewer data modifications in the transaction or put the log on a larger drive.

http://www.sqlservercentral.com/articles/books/94938/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 4

First, I set up the database recovery model as SIMPLE

Then, delete some old files which gave the log file more space to grow.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

johnwalker10 (12/10/2015)
First, I set up the database recovery model as SIMPLE

Which will not do a thing in this situation, as the log is full due to an active transaction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

PSB SSChampion Points: 13195 More actions · Answer 6

I temporarily moved the log file to a different drive.

As of now , auto growth/maxsize is set to By 10%,Limited to 2097152 MB. Can we change anything here.

Thanks,

PSB