?
Solved

SQL 2000 Memory Leak?

Posted on 2003-03-25
10
Medium Priority
?
2,999 Views
Last Modified: 2007-12-19
I'm running a copy of SQL Server 2000 SP3 on Windows NT4 SP6a on a dual 800MHz with 1GB RAM.

I'm running a busy web application which uses ASP to talk to a VB COM object which in turn connects to SQL.  It handles relatively large amounts of data and can be quite processor intensive.  The main problem I have seems to be due to a memory leak.  When the application is running some of its larger methods you can watch the memory go up in the task manager on the server.  When the method finshes the memory doesn't seem to be released.  This can consume 300 to 400 MB of memory during just one call to the COM object.  I've triple checked the ASP and COM object for non-destroyed objects and am 100% sure that everything is being cleaned up correctly.

To test SQL I have written a DTS package in VBScript to transfer data from one database to another on the same server.  I have intentionally made the row size quite big to simulate the functionality in the web app.  The field datatypes are:

int (4)
uniqueidentifier (16)
int(4)
nvarchar (1024)
nvarchar (1024)
nvarchar (1024)
nvarchar (128)
nvarchar (1024)
nvarchar (1024)
bit (1)

The table has well over 100000 records so I've set the DTS to batch 1000 at a time to test the memory.
The DTS uses an ActiveX Script containing 4 ADO objects (2 connections and 2 recordsets).  These are created and opened at the start and closed and destroyed at the end.

Each batch of 1000 records transferred takes about 13MB more memory!?  This memory never seems to be released from SQL so it eventually runs out and paging starts.

Does anyone know how I can force this memory to deallocate or what I might be doing wrong.

Looking forward to your replies!

0
Comment
Question by:BNBJ
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 3

Expert Comment

by:jSkipB
ID: 8205715
Perhaps this may be worthwhile:

You may notice that, after the very first couple of iterations, the problem doesn't seem to be there, but rather occurs after, say, the first 3-5K records have been processed.

If it doesn't cause integrity problems, after each 1000 rows, commit any pending transactions, then give SQL a chance to "calm down" (you may even have to close and reopen your data connection). This applies especially if implicit transactions are turned on.
0
 
LVL 4

Accepted Solution

by:
TheSpirit earned 2000 total points
ID: 8206595
OK this is expected behaviour from SQL Server. Under default setrings when SQL starts up it will take a low amount of memory, it will then take more memory up to it's maximum setting (or as much as it can !) It will release the memory when the OS comes under pressure for memory (down to 5%). Now you can limit the maximum memory SQL will take, and also how much it will release, my setting the maximum and minimum settings. My understanding is that on Win98 it works a bit different and will release but check Books on Line.
So bottom line is that what you describe is expected behaviour from SQL, it will take as much memory as it need and not release it, unless it really has to.
0
 
LVL 34

Expert Comment

by:arbert
ID: 8207502
TheSpirit is right with his post.  I assume you're not using AWE and you have SQL Server configured to dynamically allocate memory?

Brett
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 2

Expert Comment

by:SylvainPouliot
ID: 8207661
I have a question for you...

What if I want to let MSSQL use as much memory as it wants, but I would like to get it back when the peak is done...

I have the same behavior than BNBJ and I ocasionnaly turn off and on my server to free up some memory.

By the way, I had a server on a computer with 256 megs RAM and MSSQL was always taking as much as it could...when we upgraded to a computer with 1.8gig of RAM, MSSQL did the same thing with nearly 1.7gig of memory for itself...

0
 

Author Comment

by:BNBJ
ID: 8209433
Thanks TheSpirit.  Not the answer I wanted but if that's the way it is then fair enough.

If I want to isolate the web app's database with no detrimental effect on the other databases on the webserver, would I be best setting up a 2nd instance of SQL and putting the web app database in there?  I could then tell each instance how much memory it can have.

The reason I say this is as the server slows down and memory increases I tend to get a problem with ASP and SQL.  I either get an 'HTTP 1.1' message or '[DBNETLIB] SQL Server does not exist or access denied'.  This then means I need to reboot.

Would controlling the memory useage within SQL help with this and reduce the problem?

Also, whats the SP for limiting memory?

Thanks everyone.
0
 

Author Comment

by:BNBJ
ID: 8209642
I've just read in Books Online the following paragraph...

"There is a short delay between the start of a new application and the time SQL Server releases memory. Using max server memory prevents this delay and may give better performance to the other application. Only set min server memory if the start time of new applications sharing the same server as SQL Server shows up as a problem. It is better to let SQL Server use all of the available memory."

Do you reckon by setting the max server memory this would then prevent the '[DBNETLIB] SQL Server does not exist or access denied' message as the remaining memory could be accessed quicker by IIS?
0
 
LVL 4

Expert Comment

by:TheSpirit
ID: 8210423
Setting the max memory would probably help you since there would be no delay waiting for SQL to give up the memory it took.
0
 

Author Comment

by:BNBJ
ID: 8210540
What about setting up 2 instances of SQL?

Any benefit or would it just be an increased overhead?
0
 
LVL 34

Expert Comment

by:arbert
ID: 8210862
Setting up a second instance takes more resources on the server--you're escentially running 2sql server process now....I've used them before, but I haven't really seen anything good from them (you're suppose to not affect the other instance, but I've had an instance lock up the whole server and all you could do is stop and start ALL sql servers)....

Brett
0
 

Author Comment

by:BNBJ
ID: 8211244
Thanks everyone for your comments.

TheSpirit,

Such a simple solution in the end.  I've set the max server memory and kept just the one instance and so far evrything seems fine.

Thanks
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question