SSIS BufferTempStoragePath: The buffer manager cannot write 8 bytes to file

Hi there!

This morning I received this nice little error during ETL:

"The buffer manager cannot write 8 bytes to file C:\....There was insufficient disk space or quota."

ssis-BufferTempStoragePath-buffer-manager-cannot-write-8-bytes-to-file-error

First thing Google told me was:

Check if you are experiencing any memory problems and errors ..


Okay…you’re right again Google, we do have a memory shortage (for heavy SSIS).
Then I found this blog post by BI Monkey explaining a few things:
http://www.bimonkey.com/2008/04/blobtempstoragepath-and-buffertempstoragepath/

Two innocuous properties on every Data Flow in SSIS. You’ve likely never noticed them. But they can easily kill your jobs if not set properly.

They tell SSIS where to write to on disk under given circumstances. BufferTempStoragePath is where on disk it will write buffer contents in the event that there is no available memory. BLOBTempStoragePath is where on disk it will write any BLOB data in the buffer.


One of the tips from MSSQLTips about this:
http://www.mssqltips.com/sqlservertip/1867/sql-server-integration-services-ssis-performance-best-practices/

Best Practice #8 – BufferTempStoragePath and BLOBTempStoragePath

If there is a lack of memory resource i.e. Windows triggers a low memory notification event, memory overflow or memory pressure, the incoming records, except BLOBs, will be spooled to the file system by SSIS. The file system location is set by the BufferTempStoragePath of the data flow task. By default its value is blank, in that case the location will be based on the of value of the TEMP/TMP system variable.

Likewise SSIS may choose to write the BLOB data to the file system before sending it to the destination because BLOB data is typically large and cannot be stored in the SSIS buffer. Once again the file system location for the spooling BLOB data is set by the BLOBTempStoragePath property of the data flow task. By default its value is blank. In that case the location will be the value of the TEMP/TMP system variable. As I said, if you don’t specify the values for these properties, the values of TEMP and TMP system variables will be considered as locations for spooling. The same information is recorded in the log if you enable logging of the PipelineInitialization event of the data flow task as shown below.

User:PipelineInitialization,ARSHADALI-LAP,FAREAST\arali,Data Flow Task,{C80814F8-51A4-4149-8141-D840C9A81EE7},{D1496B27-9FC7-4760-821E-80285C33E74D},10/11/2009 1:38:10 AM,10/11/2009 1:38:10 AM,0,0x,No temporary BLOB data storage locations were provided. The buffer manager will consider the directories in the TEMP and TMP environment variables.

So far so good. What is important here is to change this default values of the BufferTempStoragePath/BLOBTempStoragePath properties and specify locations where the user executing the package (if the package is being executed by SQL Server Job, then SQL Server Agent service account) has access to these locations. Preferably both locations should refer to separate fast drives (with separate spindles) to maximize I/O throughput and improve performance.


In the Dataflow properties:
ssis-BufferTempStoragePath-default-dataflow-properties


He advices to setup two package variables in every (template) SSIS package to configure these during deployment (which makes sense) or at least change the default C-drive (which is in our case the smallest disk) to a different one.

Conclusion
Put this one on your SQL Server / SSIS checklist!
Overwrite the default paths for BLOBTempStoragePath and BufferTempStoragePath if your C-drive is too little or too slow (SSD perhaps?).







Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s