December 19, 2016 at 12:42 pm
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
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
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 19, 2016 at 3:42 pm
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
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
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