Post reply

BCP Error - Trying to import 2billion rows from text file to table in SQL server

  • December 19, 2016 at 12:42 pm

    #338767

    Hello All, Trying to import 2 billion rows text file into an empty table in SQL server 2016 using BCP utility and getting the error -"SQL server could not allocate space for object dbo.SORT temp run storage in tempdb because PRIMARY filegroup is full"(which was expected for 2billion rows)..any alternative to accomplish this task?....any help is appreciated.

    Thanks!

  • December 19, 2016 at 1:01 pm

    #1918762

    Agnii (12/19/2016)

    Hello All, Trying to import 2 billion rows text file into an empty table in SQL server 2016 using BCP utility and getting the error -"SQL server could not allocate space for object dbo.SORT temp run storage in tempdb because PRIMARY filegroup is full"(which was expected for 2billion rows)..any alternative to accomplish this task?....any help is appreciated.

    Thanks!

    You might want to try playing around with batch size

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • December 19, 2016 at 3:42 pm

    #1918780

    it also seems, altough i am be wrong, that you have indexes on that table.

    For such high volume it is advisable to load to a table without any indexes and create them afterwards.

    And probably even better to partition the table into smaller sets, load in parallel into those tables, add indexes and then switch into the main partitioned table.

    And if the final table should not be partitioned then it is still possible to load into partitioned table, and final step would switch from the partitioned table into that one.

  • December 20, 2016 at 6:29 am

    #1918885

    How is your tempdb configured? Is autogrowth turned off?

    12bn rows is a lot of data and the recommendaion from earlier about the Batch Size is definately something to look at if you want to keep your tempdb size down.

  • December 20, 2016 at 7:29 am

    #1918910

    Thank you!...batch size was the key in this case indeed..2 Bil rows successfully imported. Thank you Phil, Frederico n Kevaburg..appreciate it!

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

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