MSSQL 2000 SP4 Memory Leak

Greetings all!

On one of our intranet SQL servers running under Windows 2000 SP 3,
MSsql 2000 SP4 seems to gradually "eat" away all available memory (with no obvious reason for it) until a certain limit is reached, forcing the server to slow down substantially since the OS has to SWAP continuously.

I would appreciate any suggestions at this point on how to tackle this problem :)

Thank you!
VincentJSThat is the nature of SQL Server. By default, it will continue to claim available memory that is not being used by applications. It's not a memory leak. Most of this memory is used for data cache, which greatly improves performance. You want SQL Server to have plenty of memory. But you can limit this amout if you view the server properties in enterprise manager.

Bill|||if you can afford it, you should have your web and database servers on different machines. I believe you can limit the amount memory sql server consums with the max server memory option in sp_configure but I have never used it

MsDtsSrvr.exe memory leak?

In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.

The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.

Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?

More information about the possible memory leak.

The packages are run in in a CLR process with calls Package.Execute(), rather than being executed via dtexec or dtexecui or the designer. The reason for this approach is the ability to write a custom event handler with access to the package pointer, which other log handlers cannot do. The process runs Package.Execute() and exits, so there are 10,000 short-lived processes, each of which call Package.Execute() once.

To be slightly more specific about what the packages are doing, each of the 10,000 packages generally correlates to 1 source file, loading in dummy (aka inferred) dimension records, and then populating a fact table via a surrogate key pipeline.