Does SQLBulkCopy significantly increase the size of the.mdf file?

sqlbulkcopy sql-server

Question

I am bulk inserting data into a SQLServer database using the SqlBulkCopy class.

The.mdf file linked to the DB has a 1508 Mb original size.

When I use the same data (approximately 4 million records) and run it with:
The size of the.mdf increases to 1661 MB when the batchSize is 100000.
The size of the.mdf increases to 1659 MB with a batchSize of 1000000.

Why is this change? It's true that such a little fluctuation is insignificant, yet The.mdf file increases wildly when my Tester runs it (on the same data) with a batch size of 100 before exhausting the 20 gigabytes allocated to it and failing owing to a lack of space.

Is this a result of SqlBulkCopy allocating certain blocks with a predetermined size?
With BatchSizes more than 100000, it works without a hitch, but I'd still want to know what's causing this odd behavior or issue.

1
0
4/17/2009 11:05:52 AM

Popular Answer

Your mdf file, which houses your data, should expand approximately in line with the amount of data you have. However, as "ck" has previously said, SQL Server lets you set a certain growth pattern, so the mdf file doesn't expand byte by byte each time a row is inserted. Therefore, when it requires additional room, it will "jump" in size depending on your configuration.

Using SqlBulkLoad or a standard INSERT command to load your data has no bearing on this.

Marc

3
4/17/2009 11:22:17 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow