Post reply

Bulk Insert of 128M rows fills Log file

  • August 21, 2006 at 10:22 am

    #153142

    I'm trying to load a table with the Federal Do Not Call list -- 128M phone numbers. The database has been set to Recovery Mode=Bulk Logged (also tried Simple) and the original command was:

    BULK

    INSERT [dbo].[NATIONAL_DNC_REGISTRY] FROM 'I:\DoNotCall\dnc.txt'

    That ran 19 minutes then failed with an error saying the log was full.

    Per our DBAs I tried:

    BULK

    INSERT [dbo].[NATIONAL_DNC_REGISTRY] FROM 'I:\DoNotCall\dnc.txt' WITH (ROWS_PER_BATCH = 50000)

    That ran 16 minutes before filling the log.

    The log is at 1.5GB when full. Should Bulk Insert be generating that much log data? Am I missing something?

    Help!!

     

  • August 21, 2006 at 10:58 am

    #655941

    From BOL (mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_9esz.htm)

    When using the full recovery model, all row-insert operations performed by bcp are logged in the transaction log. For large data loads, this can cause the transaction log to fill rapidly. To help prevent the transaction log from running out of space, a minimally logged bulk copy can be performed if all of these conditions are met:

    • The recovery model is simple or bulk-logged.
    • The target table is not being replicated.
    • The target table does not have any triggers.
    • The target table has either 0 rows or no indexes.
    • The TABLOCK hint is specified.

    Any bulk copy into an instance of Microsoft® SQL Server™ that does not meet these conditions is logged.

    Try matching all those rules and try again.

    Hope this helps,

    Zubeyir

  • August 21, 2006 at 1:37 pm

    #655969

    Was set to Bulk Logged
    No triggers
    No replication

    ...Thought I had already dropped the PK (script was supposed to Drop the table then Create it again) but apparently it was still there. Also needed the TABLOCK hint.

    Ended up with:

    BULK INSERT [dbo].[NATIONAL_DNC_REGISTRY] FROM 'I:\DoNotCall\dnc.txt'

    WITH (TABLOCK, KILOBYTES_PER_BATCH = 200000)

    Ran in 2 minutes, 41 seconds, minimal impact to the transaction log. Rebuilding the Primary Key only took 3:30.

    All is right with the world... 

     

  • August 22, 2006 at 5:02 am

    #656087

    Glad it worked

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply