asked on

Transaction Log FULL due to ACTIVE_TRANSACTION error?

I was running a simple TRUNCATE TABLE and INSERT INTO table script for a very large table that has over 100 Million Rows of data. I'm transferring this table data from our PRODUCTION server to our DEVELOPMENT server and during the transfer, 3 hours into the process, I received this ERROR:

(1 row(s) affected)
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'DB1' is full due to 'ACTIVE_TRANSACTION'.

I think the process completed, but checking into this now.

Can someone please assist me in understanding what this error means and what (if anything) is the issue?

Guy Hengel [angelIII / a3]

In short it means the transaction log file was full, could not be extended (either because max size being set, disk full or extension could not be completed fast enough) and as a result, the transaction failed and shall be rolled back.

For such large transfers, rather use export/import or/and in several parts

Marcus Aurelius

ASKER

My original table was TRUNCATED and not reloaded...so,... what do I need to do now?

If transaction log is full, do I need to TRUNCATE it,...or..?

Marcus Aurelius

ASKER

BTW, ...this is our DEV server so not worried too terribly much about fix, just need to be able to move the data into this TABLE somehow.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial