We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

Transaction Log FULL due to ACTIVE_TRANSACTION error?

17,394 Views
Last Modified: 2014-08-20
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

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.