Limitation on a file size import

Question

Limitation on a file size import

cbrammer1219

SSCarpal Tunnel

Points: 4476
More actions
July 6, 2020 at 6:34 pm

#3767797

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

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

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

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?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

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.

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

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.

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

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

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