Transaction Log FULL due to ACTIVE_TRANSACTION error?

MIKE
MIKE used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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
MIKESoftware Solutions Consultant
Top Expert 2006

Author

Commented:
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..?
MIKESoftware Solutions Consultant
Top Expert 2006

Author

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
To clean the tlog, perform a transaction log backup. Then, copy the data in parts (for example per year data...)
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Sadly, SQL Server does not smoothly handle the restore of a single table.

You can't prevent logging on an INSERT no matter what you do.  And 100M rows would always be a huge amount of logging total, even if done in chunks.

Particularly if this is something you might have to do again in the future, I'd consider a work around.  Restore the database or filegroup to another db name, then use a synonym in the original db to point that table to the new db (as long as foreign keys don't prevent this approach).  You can then drop the original table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial