Transaction Log FULL due to ACTIVE_TRANSACTION error?

Posted on 2014-08-08
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
    LVL 142

    Expert Comment

    by: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
    LVL 17

    Author Comment

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

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

    Author Comment

    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 142

    Assisted Solution

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

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now