Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Transaction Log FULL due to ACTIVE_TRANSACTION error?

Posted on 2014-08-08
Medium Priority
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?
Question by:MIKE
  • 2
  • 2
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40249564
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
LVL 17

Author Comment

ID: 40249569
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..?
LVL 17

Author Comment

ID: 40249570
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.
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 40249581
To clean the tlog, perform a transaction log backup. Then, copy the data in parts (for example per year data...)
LVL 70

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 40253698
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question