SQL Server Large Dataset Import Issue

Richard Long 1 Reputation point
2021-03-24T22:16:16.503+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-03-24T22:46:11.387+00:00

    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.)

    0 comments
  2. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2021-03-25T03:09:15.217+00:00

    Hi @Richard Long ,

    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.

    0 comments
  3. Jeffrey Williams 1,886 Reputation points
    2021-03-25T16:25:55.797+00:00

    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.

    0 comments