Limitation on a file size import

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4462

    Is there a restriction of file size that can be imported into SQL? 5 gig, 10 gig???

  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    I am assuming you are importing a flat file into SQL Server via SSIS.  If so, the limitations I know of are only based on the amount of memory you have available to SSIS and in the database.  If you max file size  is 10 GB, you won't be able to put more than 10 GB into the file.

    If the file being loaded into memory is 10 GB and SSIS only has 5 GB to work with, I think it will page to disk or possibly fail (offhand, I do not remember the behavior, but I think it pages to disk which will be incredibly slow).

    If you are meaning something else by "importing into SQL" than through SSIS, could you elaborate?

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    You also have to be aware of transaction log usage - if you import a large file in a single batch you will need at least that much space available in the transaction log.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996843

    cbrammer1219 wrote:

    Is there a restriction of file size that can be imported into SQL? 5 gig, 10 gig???

    Are you running into any problems right now?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 996843

    Jeffrey Williams wrote:

    You also have to be aware of transaction log usage - if you import a large file in a single batch you will need at least that much space available in the transaction log.

    "It Depends".  I've not had such an issue with minimally logged imports.  Of course, I don't use SSIS for anything of this nature.

    The real key for me is... why is the OP asking?  Did he run into a problem?  If so, he needs to post the error or symptoms.  If he hasn't run into a problem, why is he asking the question instead of just trying it out?

    Also, what IS he using to do the "import"?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeffrey Williams

    SSC Guru

    Points: 88603

    Jeff Moden wrote:

    Jeffrey Williams wrote:

    You also have to be aware of transaction log usage - if you import a large file in a single batch you will need at least that much space available in the transaction log.

    "It Depends".  I've not had such an issue with minimally logged imports.  Of course, I don't use SSIS for anything of this nature.

    The real key for me is... why is the OP asking?  Did he run into a problem?  If so, he needs to post the error or symptoms.  If he hasn't run into a problem, why is he asking the question instead of just trying it out?

    Also, what IS he using to do the "import"?

    This all depends on that one question - what is being used to perform the import and how is that set up?  I don't have issues using SSIS because I *always* set a batch and commit size.  However, I have had plenty of users fill the log drive because they tried to load a huge file in a single transaction.

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff Moden

    SSC Guru

    Points: 996843

    @cbrammer1219 ,

    Asking the question again, WHAT are you using to do the imports and from where?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply