Solved

SQL Server 2005 - Memory Leak

Posted on 2006-11-15
17
6,397 Views
Last Modified: 2008-03-17
I have installed SQL Server 2005 on our brand new Windows Server 2003 R2 Standard Edition server using all the default settings. I have WSUS installed and ISA 2004 which both use the SQL Server to store data.

I have noticed that 2 instances of sqlservr.exe are running when the system reboots, one as Administrator and one as SYSTEM. The SYSTEM instance is eating all the server memory resulting in Virtual Memory Minimum Too Low warnings. This results in the server failing and needing to reboot.

The system has 8GB of physical memory and I have set swap files of 4096 on C and 4096 on D Drives.

What do I need to do to control or limit this memory issue?

Many thanks in advance, NewAS.
0
Comment
Question by:NewAS
  • 5
  • 5
  • 2
  • +1
17 Comments
 
LVL 29

Expert Comment

by:Nightman
ID: 17950384
You can configure each instance of SQL to limit the amount of memory that it will use. In Management Studio, right click the server instance and click properties. You will find the memory limitation (among other things, such as processor affinity) in there.

I would also suggest that you run with 12GB swap file (1.5 x physical ram is best practice).
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17950389
by default, a SQL Server instance takes as much memory as it can take. depending on the edition of the sql server 2005 it would be limited.

to limit the memory used by sql server, start the management studio, connect to the instance, open the properties of the server node (it's the top node), go to the memory tab, and set the maximum memory to a value of say 2 or 3GB. This way, it has still lot's of RAM, but will leave enough for all the other players on the box.
0
 

Author Comment

by:NewAS
ID: 17950504
The memory settings are as follows:

Use AWE to allocate memory is not ticked
Minimum server memory in MB is 0
Maximum server memory in MB is 2147483647

Other Memory Options
Index creation memory (in KB, 0 = dynamic memory) is 0

Minimum memory per query (in KB) is 1024

Do I change the Index creation memory, and if so what to?

I also have Exchange 2003 running I'm sure that will have a lot to do with what memory is assigned.

I also noticed that because I removed the /3GB /Userva=3030 from the boot.ini and used the /PWE switch instead I could increase the page file from 4096 on both C and D to 12288 on C alone. I'm sure this will help. Also If I turned on PWE can I use AWE and is it advisable?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17950608
if you have now already 2GB configured, AWE would allow you to use more, which is the contrary of what you really want to do.
as you also have exchange server running (which is not very good layout, as both EXCH and SQL are database products, and both are IO, memory, CPU and network intensive applications), you should really get sql server a bit lower, so 1.5 GB for example.

now, with that in mind:
>resulting in Virtual Memory Minimum Too Low warnings. This results in the server failing and needing to reboot.
you might want to revise the configuration of the server, and the configuration of the other software, especially exchange (in terms of the IO and RAM config) to ensure you get a livable environment.
0
 
LVL 29

Assisted Solution

by:Nightman
Nightman earned 250 total points
ID: 17950667
Change Maximum sever memory to 2GB (2048 MB). I wouldn't worry with AWE or PWE yet as you are trying to limit it, not increase it (and AWE comes with it's own overhead).

I definitely agree with angelIII about the IO config - both SQL and exchange can be heavy on the IO - IDEALLY you want these running with distinctly seperate RAID containers, but that may not be practical right now (and as angelIII mentioned, you shouldn't really have Exchange and SQL on the same box - best practice is to seperate them).

You are goiing to need to play around with the various configurations until you find the right resource/performance balance for each service (the right balance is one that suits your application requirements).
0
 

Author Comment

by:NewAS
ID: 17950689
I have just started using Windows 2003 Server R2 Standard Edition and I have installed the OS on a brand new HP ProLiant DL380 G5 which has dual Xeon processors and 8GB of physical RAM, 8 x 72GB SCSI Drives which a RAID Array, 2 Mounted Drives.

I have installed Domain Controller, DNS, DHCP, File Server, Application Server, WSUS, Exchange 2003, ISA 2004 and SQL Server 2005.

The site has about 35 users, not massive and I am going to transfer some of the services to another slightly lesser specked HP ProLiant DL380 G5 which this new server is replacing. I am going to flatten the old server and install Windows 2003 Server R2 Standard Edition.

Could you please tell me in the meantime what setting I need to set the SQL Server 2005 Memory and what to?

Many thanks in advance, I can feel 500 points heading your way.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17950750
Change the Maximum server memory (for each SQL instance) to 2048 MB. This gives you 4GB dedicated to SQL Server - you can then play around with the remaining 4GB with the other applications (but boy, have you consilidated - bet you can't wait to build the other box!).

the 8x72 SCSI - is this a single RAID container, or have you built multiple? Also, RAID what? I always recommend RAID1 (RAID1+0) for SQL Server if you can - striping and mirroring give you the best combination of performance and redundancy.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:NewAS
ID: 17950978
I have a third box to play with once I have finished with these 2, but softly, softly, catchy monkey. I have the drives configured as so:

Smart Array P400 in Slot 1
SAS Array A

72 GB SAS Drive at Port 2I : Box 1 : Bay 1
72 GB SAS Drive at Port 2I : Box 1 : Bay 2
72 GB SAS Drive at Port 2I : Box 1 : Bay 3
72 GB SAS Drive at Port 2I : Box 1 : Bay 4
Logical Drive 1 (139949 MB, RAID 6 (ADG) )

SAS Array B

72 GB SAS Drive at Port 1I : Box 1 : Bay 5
72 GB SAS Drive at Port 1I : Box 1 : Bay 6
72 GB SAS Drive at Port 1I : Box 1 : Bay 7
72 GB SAS Drive at Port 1I : Box 1 : Bay 8
Logical Drive 2 (139947 MB, RAID 6 (ADG) )

Would you recommend changing this, if so can it be done easily without reinstalling the OS, cheers.
   
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17951036
Don't know much about RAID6 except that it can be a significant processing overhead due to the increased calculations on the parity check (improved redundancy from RAID5, but can be slower throughput unless the controller chip actually handles the processing). Would still go for RAID1 if you can, but if you have software installed on these drives, you would have to re-install. And if that software is the OS, that becomes a little more painful.

Cheers
Night
0
 

Author Comment

by:NewAS
ID: 17951149
I think I will have to stick with RAID 6 then in that case, but thanks for the suggestion.

Right back to the other matter, I have modified my SQL Server 2005 memory settings as follows:

The memory settings are as follows:

Use AWE to allocate memory is not ticked
Minimum server memory in MB is 0
Maximum server memory in MB is 1610612736

Other Memory Options
Index creation memory (in KB, 0 = dynamic memory) is 0

Minimum memory per query (in KB) is 1024

My boot.ini reads as follows:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Standard" /noexecute=optout /fastdetect /PAE

My swap file on C is 12288.

Are any of these settings wrong, if so could you please be specific which ones and what they need to be.

I am monitoring the server using taskmgr and the I have 80 processes running, CPU Useage dancing between 0 and 7 percent with nobody but me logged in and the Commit Charge is 7288M/16200M and climbing steadily.

The main 2 memory hoggers are both instances of sqlservr.exe, the Administrative instance is at 145,836K but the SYSTEM instance is 375,000K and climbing, will the SYSTEM instance stop at any point or is the server going to fall over. I have had the system running now for 45 minutes now.

Again, many thanks in advance, NewAS.
0
 

Author Comment

by:NewAS
ID: 17951826
Ok the server has been up for 2 and half hours now and I have seen both sqlservr.exe instances grow and shrink over time which I hope to mean that the previous issue of the SYSTEM sqlservr.exe instance growing to the point of crashing has now fixed.

However as time has gone on I noticed that the Commit Charge is still increasing irrespective and now is 10503M/16200M.

I feel despite the previous attempt to fix the SQL Server 2005 memory issue which may have resolved something is continuing to consume server memory and that the server may still crash, just taking a little longer as I have increased the page file.

Can someone, please help?

Many thanks in advance, yet again, NewAS.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17953069
>>I have installed Domain Controller, DNS, DHCP, File Server, Application Server, WSUS, Exchange 2003, ISA 2004 and SQL Server 2005.<<
Are you saying that these are all on the same box? Please say no.
0
 
LVL 29

Expert Comment

by:Nightman
ID: 17954071
NewAs

As metioned earlier (and made even clearer by acperkins), your first step (now that you have stabilised the server) is to split the other application services off to another server. Exchange is a prime candidate, as would be ISA, Domain Controller, Application Server and DNS.

Running all these services on the same machine is a recipe for disaster. And they will significantly affect the stability and performance of SQL Server.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18357272
I recommend splitting points between Nightman and angelIII
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now