question

RichardLong-0366 avatar image
0 Votes"
RichardLong-0366 asked Cathyji-msft commented

SQL Server Large Dataset Import Issue

We're trying to import a large dataset (around 1.5GB) into an SQL database, but we're getting a message that the "Log is full" when we try to do the import. We're able to do smaller imports.

We've tried to adjust the autogrowth size of the the log file, but it changes right back.

Is there a way to support imports of this size?

We're running Microsoft SQL Server 2017 14.0.2037.2 (X64)
on a Windows Server 2016 Datacenter host.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RichardLong-0366,

Just checking in to see if the answer(s) helped.If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

And If you have further questions or issues please let us know.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

What are you using for the import?

A common technique is to split up the data in chunks and have transaction log backups running with high frequency so that the log is truncated.

(If you are in simple recovery, you don't need to think about t-log backups.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @RichardLong-0366,

How did you import the large dataset?

Did you try to set the database in bulk-logged recovery model? Minimal logging of bulk-import operations reduces the possibility that a bulk-import operation will fill the log space. The bulk-logged recovery model is designed to temporarily replace the full recovery model during large bulk operations. Suggest you change the recovery mode to Full after imported the dataset.

Please refer to MS document Prerequisites for Minimal Logging in Bulk Import and the blog Minimally Logging Bulk Load Inserts into SQL Server .


If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our **documentation** to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

BCP, BULK INSERT and SSIS all have the ability to define the batch and commit sizes for data imports. If you are using one of these to import the data - then make sure you have set these values to a reasonable size.

If the database is in full recovery model - you can switch the recover model and potentially get a minimally logged operation (if you can meet the requirements). This could also be accomplished when in the simple recovery mode - but if you must stay in full recovery model then you will need to increase the frequency of your transaction log backups.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.