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