Prevent growing transaction log with INSERT BULK with TABLOCK

apache-spark bulkinsert jdbc sqlbulkcopy sql-server

Question

I'm working on creating a procedure that will allow Spark-Scala applications to write to SQL Server. It is producing numerous INSERT BULK statements for each data partition with a batch size of 100K records, as intended.

I was hoping that INSERT BULK would prevent the transaction log from filling up as I saw it build up.

Please provide any advice on how I can get commits per batch.

1
2
9/16/2018 8:58:10 AM

Popular Answer

Bulk inserts will write to the transaction log when your database recovery model is set to full, and the transaction logs will keep expanding. These are your two choices:

  1. While the data is being loaded, change the database recovery model to bulked logged, and then reset it to full.

  2. Change your procedure to backup transaction logs after the loading of each batch (or every few batches)

2
9/16/2018 2:25:30 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