SQL Server Performance Forum – Threads Archive

After DTS… huge RAM problems

Hi everybody, 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? Thnaks for any help Christian Thanks!
Ch

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

>>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. Thanks!
Ch

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.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

>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 First of all: thanks! 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… But again the huge incrase happen on the PRE-production serve during the replication process. So i have to isolate the problem which is more on the replication side…. but it uses a Microsoft default process… Thanks! 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.
"How do you expect to beat me when I am forever?"

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… Christian Thanks!
Ch

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 Thanks!
Ch

Using @@Version command from QA and also XP_MSVER which displays the required information. HTH Satya SKJ

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.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

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. Results following: ============================
1) EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72 results:
Removed 0 replicated transactions consisting of 0 statements in 0 seconds (0 rows/sec). ================================== 2) EXEC dbo.sp_MShistory_cleanup @history_retention = 48 results:
Removed 0 history records from MSmerge_history.
Removed 0 history records from MSsnapshot_history.
Removed 0 history records from MSlogreader_history.
Removed 0 history records from MSdistribution_history.
Removed 0 history records from MSqreader_history.
Removed 0 history records from MSrepl_errors.
Removed 0 history records from sysreplicationalerts.
Removed 0 replication history records in 0 seconds (0 row/secs). ============================== Could it be something related to this (nothing removed = no RAM released) ?? Thank you once again.
Filippo

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?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

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. Satya SKJ

Thanks Brad and Satya. Yes, I checked the MDAC using @@version and XP_MSVER… Hereare the results. They’re quite the same, except from the language setting: SERVER #1 Microsoft SQL Server 2000 – 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) 1ProductNameNULLMicrosoft SQL Server
2ProductVersion5242888.00.534
3Language1040Italiano (Italia)
4PlatformNULLNT INTEL X86
5CommentsNULLNT INTEL X86
6CompanyNameNULLMicrosoft Corporation
7FileDescriptionNULLSQL Server Windows NT
8FileVersionNULL2000.080.0534.00
9InternalNameNULLSQLSERVR
10LegalCopyrightNULLÂ© 1988-2001 Microsoft Corp. Tutti i diritti riservati.
11LegalTrademarksNULLMicrosoftÂ® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12OriginalFilenameNULLSQLSERVR.EXE
13PrivateBuildNULLNULL
14SpecialBuild34996224NULL
15WindowsVersion1438515255.0 (2195)
16ProcessorCount22
17ProcessorActiveMask300000003
18ProcessorType586PROCESSOR_INTEL_PENTIUM
19PhysicalMemory10231023 (1073168384)
20Product IDNULLNULL ============================================ SERVER #2 Microsoft SQL Server 2000 – 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) 1ProductNameNULLMicrosoft SQL Server
2ProductVersion5242888.00.534
3Language1033English (United States)
4PlatformNULLNT INTEL X86
5CommentsNULLNT INTEL X86
6CompanyNameNULLMicrosoft Corporation
7FileDescriptionNULLSQL Server Windows NT
8FileVersionNULL2000.080.0534.00
9InternalNameNULLSQLSERVR
10LegalCopyrightNULLÂ© 1988-2001 Microsoft Corp. All rights reserved.
11LegalTrademarksNULLMicrosoftÂ® is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12OriginalFilenameNULLSQLSERVR.EXE
13PrivateBuildNULLNULL
14SpecialBuild34996224NULL
15WindowsVersion1438515255.0 (2195)
16ProcessorCount22
17ProcessorActiveMask300000003
18ProcessorType586PROCESSOR_INTEL_PENTIUM
19PhysicalMemory15351535 (1610043392)
20Product IDNULLNULL ========================== Filippo

Nothing to suspect from the information provided, BTW, few tips on replication performance in case this helps: 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. HTH Satya SKJ

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.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com

]]>