Space issues with SSIS temp files

The page came at a reasonable hour. All of the overnight ETL and processing jobs were just winding up before the business started in earnest for the day. One of the final jobs of the morning failed, a job that called an SSIS package that transferred some email logging to the data warehouse.

I jumped online and saw that one of the teams was in doing an early morning deploy. I scanned the errors from the job and saw the server was the one that the deploy team were working on and saw this message:

“The column status returned was: “DBSTATUS_UNAVAILABLE”.”

I decided that it was most likely deploy related and I’d rerun the job once the team was finished.

I did – and it failed again. I went back to the error output and after scanning through a lot of misleading or confusing SSIS error output I found this gem:

   Description: The buffer manager cannot extend the file “C:\Users\SQLSER~1\AppData\Local\Temp\DTB{3AA45D52-FFD2-479F-AB52-0F0F82402850}.tmp” to length 3670016 bytes.  There was insufficient disk space.

I ran the job again and sure enough the temp folder filled up, and the job failed.

I was dealing with a local physical drive here – so expansion was not an option. I needed to relocate the temp files or folders.

My first idea was to edit the environment variables.

  1. Log on to the server using the account the runs SSIS.
  2. Open control panel>system or type sysdm.cpl into a run prompt.
  3. Go to advanced>environment variables.
  4. Edit the TMP and TEMP variables (after making a copy of the original values)
  5. Restart the SSIS service.

I wasn’t keen to restart the service because a couple of jobs were running so idea number two was to create a symbolic link from the temp folder to another, roomier drive.

  1. Log on to the server
  2. Rename the current temp folder to temp_old
  3. Run the following in a command prompt. mklink /D [old temp location] [new temp location]
  4. Copy any contents from the temp_old folder to the new location
  5. Run the job
  6. Delete the shortcut created in the old location by the symbolic link.
  7. Rename temp_old back to temp.
  8. Copy back any files.

Luckily the SSIS jobs that were running were not making use of the temp folder so this all worked well.

Interestingly enough out of three failures (2 job runs and a step retry) the disk space error only appeared once. That error then flowed on to cause a whole bunch of other SSIS errors that were ultimately misleading.

I did manage to find a way to blame the whole thing on the deploy team – so all was not lost. It turned out that the changes they had made caused a lot more data than usual to be piped through SSIS – which meant the temp files took up more space 🙂

 

Advertisements
This entry was posted in SQL Server, SSIS and tagged . Bookmark the permalink.

5 Responses to Space issues with SSIS temp files

  1. BevanJS says:

    “I did manage to find a way to blame the whole thing on the deploy team” Isn’t that step 1? 😉

  2. Rob says:

    No – Step one would be making sure it’s not your own fault. Step two is proving it was someone else.

    Nice post Matt, couple of nice how-to’s in there, but good to see your troubleshooting process in action.

  3. John Mihalko says:

    What component in the SSIS package is using this space and does this relate at all to the temp buffer storage location? I thought by setting the TempBufferStoragePath property on a data flow task you avoided using the default temp directory on the C drive. In my SSIS package, should I be setting the TempBufferStoragePath property to a fast directory not on the C drive and create a symbolic link for the temp folder on the C drive?

  4. matt.bowler says:

    Good pickup John. This was in one of the data flows, and I could have altered the BufferTempStoragePath property to achieve a similar end. However by this stage I had realised that the root cause of the extra data was from the morning deploy and this was a one off issue. I needed to get this load of data through and then things would return to normal. I chose not to deploy a changed SSIS package and instead to use a temporary workaround.

  5. mihalko says:

    I got a paged at 5:20 AM this morning that actually made me laugh. We have a disk monitor job agent that runs a PowerShell script ever 10 minutes and pages if a drive goes below 5% free space (emails at 10%). This morning I was paged because a C drive was at 3% (jumped from 12% to 3% so never got the 10% email). Less than ten minutes later it was back up to 12%. I inherited the ETL’s running on that machine and I know their BufferTempStoragePath is set to the default, and because your blog is still fresh in my mind, I am pretty sure I know what is going on and how to fix it thanks to you. It was the timing that made me laugh.

    Thank you for the timely blog. It is very much appreciated.

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