Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question Unanswered: How can I keep the log from filling up?

    I have a ton of data to load into a SQL 2005 database.
    I just loaded a bunch of data for a number of tables using bcp, and the last table that my script loaded was an 8 million row table. The next table was a 12 million row table, and about 1 million rows into the bcp'ing a log full error was incurred. I have the batch size set to 10000 for all bcp commnads.
    Here is the bcp command that failed:

    HTML Code:
    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" billing_data_repository..mtr_rdng_hrly_arc_t  in mtr_rdng_hrly_arc_t.dat -c 
    -b10000 -Sxxxx -T
    Here is the last part of the output from the bcp command:

    HTML Code:
    ...
    10000 rows sent to SQL Server. Total sent: 970000
    10000 rows sent to SQL Server. Total sent: 980000
    10000 rows sent to SQL Server. Total sent: 990000
    SQLState = 37000, NativeError = 9002
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]The transaction log for database 'billing_data_repository' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
    
    BCP copy in failed
    I thought that a commit was issued after every 10000 rows and that this would keep the log from filling up.

    The log_reuse_wait_desc column in sys.databases is set to 'LOG_BACKUP' for the database being used.

    Does a checkpoint need to be done more often?

    Besides breaking up the 12 million row data file into something more manageable, does anyone have a solution?

    How can I continue to use my same loading script, and keep the log from filling up?

    Thank you.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is the database in FULL recovery mode? If so, all of the rows are being logged.

  3. #3
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58
    I will find out.

    I just read ... "Tip: When loading large datasets into SQL Server, set the recovery model to BULK_LOGGED, as the FULL recovery models build up massive log files for large imports."

    Is this what you were going to suggest?

    Is nothing logged when the recovery model is set to BULK_LOGGED?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You never get no logging. You only get minimally logged. under BULK_LOGGED, only the page allocations are logged, instead of every row.

  5. #5
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    Question BULK_LOGGED option used, still getting log full message

    Thank you.

    We changed to BULK_LOGGED and still got the log full message due to 35,050,000 rows being bcp'd into a table.

    Are my only 2 options at this point to:
    1. increase the log size
    2. break up the bulk inserts into smaller chunks
    ?

    I am using the "-b 10000" bcp option to issue a commit every for every 10000 inserts. Would it help to change this option to 5000?

    I am coming from the Sybase world where we did a checkpoint every 5 minutes. Would it help to do a checkpoint in MS SQL Server more frequently, or to ensure that the log was backed up more frequently?

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I would try simple recovery mode with the -b10000 switch ... that way you wioll get a checkpoint at the end of each batch when the batch is committed.

    If you still run out of log space, I would ask if you could open a 2nd log file on another drive for the duration of the load, and then turn it off and remove it (see ALTER DATABASE in BOL for modifying the added file and then removing the added file).

    EDIT: Always be sure to back-up your database before trying this!

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Jan 2005
    Location
    Atlanta
    Posts
    58

    SIMPLE recovery mode worked

    Thanks.

    The SIMPLE recovery mode worked great. We also increased the log size, and changed the batch size to -b100000. It only took 1 hour to bcp out and bcp in 43 million rows.

  8. #8
    Join Date
    Feb 2008
    Posts
    5
    I would also set this back to full when you are done with the transaction. With SIMPLE recovery, you cant back up the transaction log. Not sure if thats important, but just wanted to make sure you were aware.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Drop indexes prior to load (fast BCP), then re-create after the load. It's senseless to put any type of Datawarehouse (no OLTP) in full recovery mode (Assuming you do nightly full or diff backups), any day's data can be recovered by running day's jobs again.

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by tomstone_98
    Thanks.

    The SIMPLE recovery mode worked great. We also increased the log size, and changed the batch size to -b100000. It only took 1 hour to bcp out and bcp in 43 million rows.
    You think you have it bad, I have 1 billion rows to contend with every 2 weeks for loan performance data, I'm still researching the best way to do this (partition or not partition), and what to partition on.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •