After DTS... huge RAM problems

We run a very huge DTS (about 60.000 records) between 2 windows 2000 servers (they run both SQL 2000 latest service pack). On the last server (production one) after this DTS (more and less 10 minutes) the amount of RAM increase (which i s normal BTW)....

What is strange that the RAM memory keeps locked (we see it in task manager) and we can't reset it without stopping/starting SQL...

The DTS is a daily job, so if we don't stop it, any time i t will tehn add RAM without setting down by itself.

So the feel is that something doens't close and keep opening, but the DTS works as SQL services ( I mean we use the default DTS enviroment)... ANy option to be set, any tips, any suggestion?

>>What are memory settings on the server, if so min and max values?
>>OS and memory?

OS is windows 2000 server (standard editon), not sure about you question... the pre-production server (from which we lunch the DTS+Replication) has 1,5 GB RAM/ and after a Replication job it adds about 300 MB, every time we run DTS+ Replication.
We have noticed so far that the replication adds more ram then DTS itself. So for example, if we start at 300MB RAM used, then we see 600 MB locked (task manager)...
Thnaks
C.

So that I am clear on this, every time you run DTS, more memory is used, but is never released. So if you run DTS each day, and don't stop the service,then more and more RAM is used and never released? If this is the case, then obviously, this is not supposed to happen. But you already know that.

I have not seen this before, so I don't have any specific answers. If I were you, one of the things I would do is get a Profiler trace of the DTS job to see if anything obvious is going on. Also, check to see that the connection(s) created for the DTD job disconnect after the job is over. Also, I would check to see if you have the same MDAC level on both servers. Sometimes older versions of MDAC cause memory leak problems. And if none of these worked, by last option would be to delete the current DTS package and recreate it from scratch. And if that didn't fix it, I would call Microsoft, as it most likely would be bug-related.

&gt;So that I am clear on this, every time you run DTS, more memory is &gt;used, but is never released. So if you run DTS each day, and don't &gt;stop the service,then more and more RAM is used and never released? &gt;If this is the case, then obviously, this is not supposed to &gt;happen. But you already know that<br /><br />First of all: thanks! <br />Yes this is the case <img src='/community/emoticons/emotion-5.gif' alt=';-)' /> BTW the problems is related to the Replication built-in Sql 2000 process. So my guess is the replication itself. We have noticed that on the PRO server (which acts as subscriber) there also have a RAM problem which is not released after the replication is over...<br />But again the huge incrase happen on the PRE-production serve during the replication process.<br /><br />So i have to isolate the problem which is more on the replication side.... but it uses a Microsoft default process...<br /><br /><br /><br />Thanks!<br />Christian (italy)

Replicaton is a memory hog, especially if you have it setup for transactional, and there is no way to get around this, except to maybe consider a different option like log shipping. I am currently experimenting with how log shipping will work in a heavy hit transaction based SQL database with a 15 second log shipping interval. Plenty of resources on this web site about log shipping, you can look them up and get a very good feel for how it works.

Rovy thank for your help and answer: but during all the process the RAM is not the problem... the problem comes after, cause the replication seems to stay on (even if we have all our logging which says that everithing is finished and over) and to keep running on RAM, which actualy doen't release the RAM used during the process.
See? We need to understand why replication doesn't release the RAM used in the process...

Also, I would check to see if you have the same MDAC level on both servers. Sometimes older versions of MDAC cause memory leak problems. And if none of these worked, by last option would be to delete the current DTS package and recreate it from scratch. And if that didn't fix it, I would call Microsoft, as it most likely would be bug-related.

Brad how can i Know if both SQL have the same MDAC version? Sorry for stupid question...
Christian

MDAC information can be found at:http://www.microsoft.com/data/. Try downloading the Component Checker to find out very detailed info on your MDAC. Also, the latest version of MDAC is available at this website.

If RAM is not being used by SQL Server, it should be released, no matter what part of SQL Server has used the RAM. That is why if you are positive that SQL Server is not releasing RAM, that there may be a bug that you have run into, and MDAC bugs often cause memory leak type problems. Of course, this may not be your problem, but it is something I would look into.

Hi, I work with Christian (chfats) and I just registered to this Forum. So, first, I'd like to say hi to everyone and thanks for your very kindly support.

I have just found one thing that maybe it's related to our problem: into "jobs" there are 2 scheduled events that maybe are self-created from MSSQL to release memory. Names are "Distribution clean up: distribution" and "Agent history clean up: distribution". I tried to run the SPs that these jobs execute.

The scheduled events you are seeing are normal, having been created by SQL Server when you setup replication. When they run, the only clean up data, not memory. Memory is automatically released by SQL Server, you can't do this manually, unless you restart the server. Have you checked your MDAC yet?

Memory related counters can be captured or get information from PERFMON which is a powerful tool. If the memory settings are dynamic then the memory will be released whenever required. Another thing is make sure you're not running any other application which will use memory in terms of few select queries with join. Most of the production systems will help if you specify the memory settings.

If you#%92re using merge replication on a machine, turn off dynamic memory management. Dynamic memory management consumes resources that replication wants and drags down performance. Using static memory management can net you a 5 percent to 25 percent performance increase.

The last thing you want is a database to resize during a merge operation. If you forget to resize your database and it runs out of space, the entire system stops. Dynamic sizing is your safety net. But you need to manually manage the size of your replicated databases to ensure that you aren't taking a performance hit while a replication operation is running.

You need to periodically reindex the MSmerge_contents and MSmerge_genhistory tables. Reindexing will remove the index fragmentation that occurs and will improve performance.

It depends how you've configured the jobs in replication, i.e, simply restarting the agent, the last step in the job should be to return to step one if it fails.

While @@version and XP_MSVER... offer some useful information, they don't offer complete MDAC information. Only by using the MDAC component checker can you know for sure if the MDACS are the most recent ones. Also, assuming you can down the servers, you might consider installing the latest MDACs from www.microsoft.com/data anyway, that way will know for sure that you have the latest components, or you can wait for SQL Server 2000 SP3, which will be out soon, which will also update your MDAC. I wish I could help more, but the problem you are describing is not normal, and I don't know of any other options.