Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Unanswered: Not enough storage is available to complete this operation

    Hi,

    I am having trouble trying to import a big file (aprox 250Mb is size) into an SQL Server database and I keep getting the message:

    "Not enough storage is available to complete this operation".

    The application tries to import the file by executing a stored procedure:

    CREATE PROCEDURE sp_updateMaterialBlob
    @MaterialId Int,
    @BLOB image
    AS
    BEGIN
    Update Material SET blob = @blob where id = @materialId
    END

    The application uses an ADO connection. I've tried increasing the memory of the client machine but that didn't work. Whenever I do run the import, nearly all the memory on the machine is used up but every time after several hours I get the same error message. What is the cause of the problem and how do I resolve it? Ideally I want to use my application to do the import rather than anything bespoke.

  2. #2
    Join Date
    Oct 2003
    Posts
    706
    It may be that you are building a humongous transaction, just in case you want to roll-back 250 megabytes.

    Try splitting the large file into 25 pieces, each 10 megabytes long, or otherwise splitting the job up into smaller pieces. This will be far more "digestible." And it will be restartable; you'll know that once each piece has been completed and committed that piece is done.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check available disk space
    Check the current allocated database size
    Check the current allocated log size
    Check UOM for database and transaction log growth and change both from Percent to MB.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by sundialsvcs
    It may be that you are building a humongous transaction, just in case you want to roll-back 250 megabytes.

    Try splitting the large file into 25 pieces, each 10 megabytes long, or otherwise splitting the job up into smaller pieces. This will be far more "digestible." And it will be restartable; you'll know that once each piece has been completed and committed that piece is done.
    I don't think jignatiu can split a binary file into chunks, but you can do the same thing programmatically from the front-end which will allow you to specify a chunk size.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Maybe he needs to reset his textsize parameter for the connection? In order to see what it is, run
    Code:
    select @@textsize
    By default, it is only around 64Kb.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    TEXTSIZE, DBTEXTSIZE, and DBTEXTLIMIT settings affect ONLY the size of returned text or image values, not how those values are accepted by the server at the time of storing them into the database.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Aug 2004
    Posts
    2
    Thanks for this suggestion. I will try it.

  8. #8
    Join Date
    Oct 2003
    Posts
    706
    I don't know the nature of the file that's being imported nor the exact procedure being used to present it to the system, but it could make an astonishing difference to the speed of the process if you break it up into more manageable chunks.

    It really is an axiom, provable in a lot of different situations with computers, that "when a computer process starts to run out of resources, performance does not degrade linearly: it smashes into the wall, Wile E. Coyote-style. The performance curve has this big "elbow" in it; a right-angle turn leading straight to perdition. So you really, really want to arrange things, whenever you humanly can, so that those "thrash points" are never reached to begin with. Go out of your way to redesign the process. It's worth the trouble.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •