Last post Mar 08, 2018 06:05 PM by MauricioBastos
Apr 02, 2015 12:23 PM|kmcnet|LINK
Hello everyone and thanks for your help in advance. I have three SSIS packages that run nightly to import flat 3 flat files dropped into a folder by a legacy system. The packages have been working perfectly for approximately one year. However, in the
last week, the three packages have started to fail nightly with Event ID 6517 and the log message:
SSISDB.dbo[runtime].14 Could not load file or assembly 'System, Version=22.214.171.124, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)
After researching the error, I found comments regarding possible CLR inconsistencies based on updates or patches. So I restarted the server, which resolved the problem for five days, but am now getting the failures again. I'm not sure where to go from
here. Any help would be appreciated.
Apr 02, 2015 03:18 PM|AZMatt|LINK
It looks like you are running out of disk space on the server.
Apr 02, 2015 03:25 PM|kmcnet|LINK
Thanks for the response. Not likely. 4.5 TB free only 912 GB used. So I don't see how it is a disk space error.
Apr 02, 2015 04:04 PM|limno|LINK
BlobTempStoragePath and BufferTempStoragePath Properties of your SSIS Dataflow
Check these links:
Apr 02, 2015 04:46 PM|kmcnet|LINK
Thanks for the response. I'm not sure I'm completely following. On the first import task, neither BLOBTempStorgae nor BufferTempStorage have anything set in them. None of the disks on this server are remotely full, so this might be overflowing the allocated
SQL Server memory in RAM. Is this correct? Is this also why the reboot casued things to wrk again for a period? Why does it not affect other SSIS packages on this server that seem to be still running correctly?
Thanks again for the help.
Apr 02, 2015 04:49 PM|limno|LINK
Have yo checked the files' size in question?
My guess is that these files are significantly large compared to other files in other SSIS packages.
Apr 02, 2015 05:00 PM|kmcnet|LINK
4 MB. Not much for modern machines. The working SSIS packages are backup and export functions.
Apr 02, 2015 05:16 PM|limno|LINK
If I were you, I would follow the suggestions from the links I posted early and assign value to BufferTempStoragePath a drive that is large enough and test the package to see whether this is the right path to solve the problem.
Apr 02, 2015 09:46 PM|kmcnet|LINK
Again thanks for the response. I made changes to the BufferTempStoragePath to point to a folder with plenty of space. The package now executes in Visual Studio, but when I go to deploy the SSIS package, the deployment wizard fails with the same not enough
space error. I'm not sure what to do next. I also checked in the task manager looking for any clues and see that SQL Server is using 3,587 MB of memory which seems extremely high. I'm not sure where to go next.
Apr 03, 2015 05:19 AM|Edwin Guru Singh|LINK
The package now executes in Visual Studio, but when I go to deploy the SSIS package, the deployment wizard fails with the same not enough space error. I'm not sure what to do next. I also checked in the task manager looking for any clues and see that SQL
Server is using 3,587 MB of memory which seems extremely high. I'm not sure where to go next.
Apr 06, 2015 08:39 AM|kmcnet|LINK
OK. I made the changes to the package, which ran for two nights, but is now failing again.
Mar 08, 2018 06:05 PM|MauricioBastos|LINK
I know this is an old issue, but it came to me this week. The amount of data on one of the DataFlows increased absurdly.
The SQL SSIS Server has only 4 MB RAM memory. It has enough disk space. I tried decreasing the Data Flow buffer size, buffer max rows, disabling Lookup Caches and nothing worked.
Finally I decreased Engine Threads and: Gotcha ! It worked perfectly on the server.
Hope it helps someone.