Memory leaks - Custom Tasks in SQL 2008r2
I have an SSIS package that executes with DTSExec that is scheduled to run every 2 hours and as time goes on (2 runs or so) the SQL Server's memoey is maxed and record process stops. I am running a custom C# task that runs under BIDS just fine. What is a good place to start to resolve this issue?
April 25th, 2011 3:07pm

It is a rather large topic. Questions: What isinvolved in your package? E.g. any lookups, scripts, etc? Do you get any specific error messages? If you run the package alongside the SQL Server instance this alone is enough sometimes to choke the package, I would then recommend limiting how much memory the SQL can grab (in SQL Server settings). Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 25th, 2011 3:13pm

First of all the custom task was written in C# and perform a multitude of data reads, data validation and then calculations before re-writing the data back to database tables. AS far as error, there isn't any error, the DTSExec job just keeps on running. As far as SQL server memory limits we do have one that is set for 50% SQL and the system gets the rest. The server is SQL(2008r2) only running on Windows 2008 with 8 Process and 16gigs of ram.
April 25th, 2011 3:32pm

If possible create a new package that will be a completely decomposed equivalent to what you have in Prod now, run it on the same server incrementally adding features each time in order to pinpoint the offending piece(s). In general you can use a Memory Profiler to see where the allocation is going to now and/or you can set performance counters, but IMHO it is a longer route.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 25th, 2011 3:47pm

This task can not be decomposed, I didn't write it, just inherited, and for the memory I see on the server that the memory is getting assigned to SqlServer.exe.
April 25th, 2011 3:51pm

You may then use say the Process Explorer to see what is in use by what, and hopefully narrow down to what is wrong. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
April 25th, 2011 3:59pm

I loaded on the server and I see all the process, and the only thing that sticks out is the SQLSERVER.EXE process Private Bytes and Working Set memory is increasing on every screen refresh. No realy usefull information there unless I can see what commands are executing with in the SQLServer.Exe.
April 25th, 2011 5:02pm

What do you mean by "record process stops"? What errors do you get, and from where? The information you've given is that SQLServer.EXE (the SQL Server service) increases its memory consumption to a certain point, and then your processing "stops". It is completely expected that SQL Server will continually increase its consumption of memory up to the limit you've defined. As a server process, it's been designed to request memory - up to the limit you defined - when it requires it, and never release it. As in, it will not release that memory. Ever. That's by design in order for it to not thrash memory releases and requests. It's by design because it's a server process that's intended to be run on a single-purpose machine (or a mostly single-purpose machine). Please provide more information as to how your processing fails, and where. Talk to me now on
Free Windows Admin Tool Kit Click here and download it now
April 25th, 2011 8:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics