Memory leak in SSIS package

Question

Post reply

Memory leak in SSIS package

TangoVictor

SSChampion

Points: 10975
More actions
August 12, 2010 at 5:23 pm

#222580

This post was originally started in the SQL 2005 Administration forum under high CPU usage. http://www.sqlservercentral.com/Forums/Topic954961-146-1.aspx
Server: Windows 2003 Single core 2.33GHz 4GB running SQL 2005 Std and Integration Services
The problem:
Committed bytes (page file usage) will rise over the course of a couple days until it gets critical then drops and begins to rise again. this drop is preceded by high CPU usage to the point where nothing runs, I assume it's reached a thrashing stage with page faults. usually we shut down the jobs and have rebooted.
We have an SSIS package that runs all the time. It uses a for loop basically with a condition of 1=1.
Inside the loop we use Konesans "File Watcher Task" to monitor a directory for incoming text files.
The package that stays running all the time uses a a couple file system tasks, few script components a data flow and a few exec sql tasks. This package also calls 3 other packages which perform some basics functions then end.
The File Watcher currently has a timeout of 1 minute. So if no files show up in a minute it loops back and waits again. The other packages are only called when there is a file to process.
We have another SSIS package that runs continuously for the last few years and has never caused a problem.
Looking for ideas on what and/or how memory will get chewed up and not released, or possibly some type of memory cleanup I should be doing.
The data that we are processing needs to be delivered to the end user within a minute, that is why it's not running in batches.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Hi Tom,

Have you seen this report ?

Also check this post and the comment below by skreekc.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Thanks for the info. This package uses a for each within a for loop. I think I'll remove the for each and just use a script component.

TangoVictor SSChampion Points: 10975 More actions · Answer 3

We are working with Microsoft on this issue. I sent them the link to the connect bug (thanks again Cozy Roc). I'll update with any useful info or hopefully a resolution.

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 4

I have almost same type of SSIS packages like yours. I have super high memory usage instead of CPU over time. But I think it's all because of the memory leak caused by the for loop.

Have you found any solutions from Microsoft other than rewrite for loop with a script?

Tom Van Harpen (8/12/2010)
This post was originally started in the SQL 2005 Administration forum under high CPU usage. http://www.sqlservercentral.com/Forums/Topic954961-146-1.aspx
Server: Windows 2003 Single core 2.33GHz 4GB running SQL 2005 Std and Integration Services
The problem:
Committed bytes (page file usage) will rise over the course of a couple days until it gets critical then drops and begins to rise again. this drop is preceded by high CPU usage to the point where nothing runs, I assume it's reached a thrashing stage with page faults. usually we shut down the jobs and have rebooted.
We have an SSIS package that runs all the time. It uses a for loop basically with a condition of 1=1.
Inside the loop we use Konesans "File Watcher Task" to monitor a directory for incoming text files.
The package that stays running all the time uses a a couple file system tasks, few script components a data flow and a few exec sql tasks. This package also calls 3 other packages which perform some basics functions then end.
The File Watcher currently has a timeout of 1 minute. So if no files show up in a minute it loops back and waits again. The other packages are only called when there is a file to process.
We have another SSIS package that runs continuously for the last few years and has never caused a problem.
Looking for ideas on what and/or how memory will get chewed up and not released, or possibly some type of memory cleanup I should be doing.
The data that we are processing needs to be delivered to the end user within a minute, that is why it's not running in batches.

TangoVictor SSChampion Points: 10975 More actions · Answer 5

We are still waiting on MS for an answer. My understanding is that this is caused by a for each loop which we have inside the for loop. I have simply set this up on a schedule and that took care of it. I will update when I have an answer.

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 6

Thanks for the update, Tom. That would be my plan as well to do the file check loop through SQL job if no quick fix can be found for SSIS. I'm very curious what MS has to say, though.

Tom Van Harpen (8/27/2010)
We are still waiting on MS for an answer. My understanding is that this is caused by a for each loop which we have inside the for loop. I have simply set this up on a schedule and that took care of it. I will update when I have an answer.