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

Question

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.

Answer 1

HI,

(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

Answer 2

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

Regards，

Zoe

---

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.

Answer 3

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?