CODEWITHSUNDEEP
sqlsql-server-2008transactionstransaction-log
gruber
gruber

Reputation: 29779

SQL server 2008R2 The transaction log for database 'MGR' is full due to 'ACTIVE_TRANSACTION'

I run a query in which I wanted to update more then 130 mln of records. After few hours I got an error:

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

now I ve got 70 MB free on my C disk drive.

I supose that the problem was with to little disc space and thats why query failed but how can I now regain the lost disc space from before query ?

Im using sql server 2008 R2

Thanks for any hints

Upvotes: 1

Views: 852

Answers (1)

Pedro Parada
Pedro Parada

Reputation: 21

The problem has to do with how sql logs all the changes during an active transaction. While a transaction is active, the log cannot be flushed, so if you have a huge active transaction the log keeps growing until it reaches a point where it can exceed its capacity. The amount of logging depends on many factors: the recovery mode (full recovery mode is the one that generates more logging activity). Also, you can breakdown the transaction in small chunks to enable log flushing in between. Also look into table hint TABLOCK. The lost amount of disk must possibly have gone to the log file. Check that out.

Upvotes: 2

Related Questions