SQL Agent Error : The buffer manager cannot write 8 bytes of file. There was insufficient disk space or quota

kkran 681 Reputation points

Hi Team - I have a SQL Agent job that calls an SSIS package. The job has been running well for a couple of years and never had an issue. But today it threw the below error. The SSIS package has 8 child packages and the 5th child package throw the error.

And now the job picked the schedule and it passed the step that it failed at 5th package.

But i want to understand why the job failed with this error ? Could you please help me undersand.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,756 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,071 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ronen Ariely 14,641 Reputation points MVP


    (1) We cannot copy and search for information about content of an image. You should always add the exact full error message as text in any discussion and question. There is no reason that people will waste their time in typing what you can simply copy paste from the original error.

    (2) It seems in first glance like your issue is not related to JOB which mean that we need to focus on the relevant part which is the SSIS

    Please confirm that this is the case: Execute the SSIS package manually (Not by using the JOB) and please inform us that the issue continue and is not related to the SQL Server Agent Job (or if it is)

    Optional solution: Configure the BufferTempStoragePath parameter to use a specific location (by default it is using the location you see in the error message)

    Some more theory time :-)

    BLOBTempStoragePath and BufferTempStoragePath properties on Data Flow Task defines where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to the location configured in the BLOBTempStoragePath for example.

    When a memory allocation fails or when Windows signals the low memory resource notification event for any reason, then all other buffer data will be swapped to the location configured in the BufferTempStoragePath parameter.

    When BLOBTempStoragePath and BufferTempStoragePath are not set, then the paths as defined by the system variables TEMP and TMP will be used, which by default is at %USERPROFILE%\AppData\Local\Temp. THIS IS THE LOCATION WHICH YOU SEE IN THE ERROR (probably) and that location does not fit your data.

    You can configure the Data Flow Task’s BLOBTempStoragePath and BufferTempStoragePath property to use a specific location in a separate/different drive (preferred fast drives). Doing so to anywhere other than the OS drive will improve SSIS Data Flow Task execution performance and can solve issues like mentioned in this thread.

    When SSIS execution is scheduled via SQL Agent Job, then you can persist Property Overrides on the Advanced tab. This is done in the configuration of the step in the Job that execute the SSIS: under the option "Configuration" select "Advanced" -> Click on "Add" button to add properties -> In the properties path write "\Package\<Data Flow Task Name>.Properties[BLOBTempStoragePath]" -> set the value to the File System Path you wish to use for BLObs transfer.

    Do the same regarding the BufferTempStoragePath parameter

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. ZoeHui-MSFT 21,986 Reputation points

    Hi @kkran

    the buffer manager cannot write 8bytes to file

    From the error message, the issue maybe related with memory.

    BLOBTempStoragePath and BufferTempStoragePath are two properties on Data Flow Task. They define where on the hard disk(s) to page buffer data to. BLOBs (text, next, image) are written to BLOBTempStoragePath. All other buffer data will be swapped to BufferTempStoragePath.

    For better performance, we recommend both BLOBTempStoragePath and BufferTempStoragePath point to fast drives.

    Refence: sql-server-integration-services-ssis-performance-best-practices



    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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 No comments

  2. Erland Sommarskog 77,661 Reputation points MVP

    Throwing my hat in the ring, despite I know nada about SSIS. To me this seems like a Windows error. That is, disk is full, or there is a quota in force and you filled up your quota. Maybe time to clean up some files?

    0 comments No comments