.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SQL 2008 memory allocation

Posted By:      Posted Date: September 04, 2010    Points: 0   Category :Sql Server
Hey everyone, I have a new server running SQL 2008 with 2.5 GB RAM and noticed all the available memory was being allocated.  In task manager SQL was not taking up much memory, but once the service was stopped, my available memory nearly tripled.  I was not aware there is a max memory setting in SQL configuration.  This is probably the cause of my memory leak?  What do you suggest I change this max setting to?

View Complete Post

More Related Resource Links

Visual Studio 2008 Memory problems


My memory in task manager reaches about 900,000K  and I don't know why it does this. Definately slows everything down, especially when I rebuild my tableAdapters, takes about 30 seconds sometimes to rebuild the project.

Visual C++ 6.0: Don't Let Memory Allocation Failures Crash Your Legacy STL Application


Most C++ developers make extensive use of the Standard Template Library (STL) in their code. If you are one of them and are using STL and Visual C++ 6.0 directly out of the box, your application is at high risk of crashing under low memory conditions. The problem arises because checking for failure of operator new is such an uncommon practice. To make things worse, when new does fail, the response is not standard. Some language compilers return NULL while others throw an exception.In addition, if you are using STL in an MFC project, be aware that MFC has its own set of rules. This article discusses these problems, explains how the default behavior has changed in Visual C++ .NET 2003, and outlines the changes you must make if you're using Visual C++ 6.0 so that you can safely use the STL when operator new fails.

James Hebben

MSDN Magazine September 2003

WmiPrvSE.exe causes a huge memory leak in SQL server 2008 R2, why?

My OS is windows server 2008 r2 data center 64 bit and my SQL server is server 2008 R2. The problem is when I open sql server, the WmiPrvSE.exe process consumes 20 - 60kbs every second. Several hours later, it eats up at least 200mbs memorry and this process seems like it will never stop. Can any experts give me some ideas to fit this mess? PS: My mainboard is Asus striker II formular, cpu is intel quad-core. Also, all my software are updated to latest version.

AdomdDataAdapter.Fill returns memory error when querying an SSAS Cube(2008)

For certain SQL's I will get the following error when calling the Fill method on an AdomdDataAdapter object. "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."   Snippet of Code: DataSet ds1 = new DataSet(); AdomdCommand acmd1 = cn1.CreateCommand(); acmd1.CommandText = "SELECT ..."; AdomdDataAdapter ad1 = new AdomdDataAdapter(acmd1); ad1.Fill(ds1);   Now when we run this query in Ms Sql Studio we don't get any errors but the query returns 1 row with over 40,000 columns. I'm guessing the # of columns might be an issue but not sure.   When doing a Google search it appears that this problem was found in the 2005 and fixed in that version. Have any of you come across this problem?  

SSAS 2008 - Memory Error

During our unit and load testing of the SSAS database using VSTST 2010 Ultimate; we are getting a memory error with some of our MDX queries (below).  Futher analysis showed: The adomdDataAdapter "Fill" command is our error point The error is occuring when the result data set has getter than 16,384 Columns; actual returned column count is 41,339 columns - business requirement does not let us change this query at this time. The MDX query works fine in MS Excel 2007 The MDX Query works fine in MS SQL Studio 2008 How can we resolve the unit test data set with such a large column count? Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed. In reference to thread: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/4220adbb-438c-42e7-9612-56aebf8df435/ Thank you, Craig  

Not Releasing memory in sql server 2008 R2

HI I have a server with 8 Gb Ram I installed Sql server 2008 R2 Enterprise Edition on 64 bit platform Enterprise edition,in that for Sql server I allocated 6 GB of Ram I enable the AWE oprtion as well but in processing time it taking above 6 Gb ram like 7.gb or more after query completing its not release the memory how can I resolve this issue. Actually when we running quries it taking the memory and memory increasing gradually after executing the query its not releasing the memory per example I set maximum 6 GB per Sql server then I run the query memory will increase gradually like 3.4 gb,3.7 gb, 4.5gb,and so on it will reach 7.56gb then after it steadily over there not come to down I am using X64 bit windows server 2008 sql server 2008 R2. My system configuration is sql server 2008R2(10.0.3798) windows server 2008 X64 Ram is 8GB

Max Memory w/ SQL 2005 and SQL 2008 on same box

We found we have a small need for a SQL 2005 instance and so it was placed on the same server as SQL 2008.  Any suggestions on how max memory settings should be configured with both versions on the same server?Lossless Audio Addict

Memory Allocation

hi friends, i want to ask about memory allocation in visual studio.i confused about like this format(1) for(int i=1;i<10;i++) { var source = dataclass.tableTest.SingleOrDefault(p => p.ID == i) } format(2) var source = dataclass.tableTest.SingleOrDefault(p => p.ID == 0) for(int i=1;i<10;i++) { source = null; source = dataclass.tableTest.SingleOrDefault(p => p.ID == i) } (1)is there any difference between above those two format? (2)variable source will allocate each time on memory in using format(1)? (2)variable source will allocate only once time on memory in using format(2)? i think i should use format(2),is it correct?please guide me. regards Aung

Memory consuming very hign after installing the SQL Server 2008 R2 64 bit

Hi all I have a  Server with  SQLServer 2005 64 bit and I installed SQL Server 2008 R2 64bit and I moved the Databases and logins to SQL Server 2008 R2.After that (to night) I ran Integrity check + rebuild index + update statistics jobs. after that I found SQL Server consumed more memory and not released.what was the reason behind and how to clean that memory. Please guide with query or with an idea, your help is greatefull for me.   Thanks in Advance    SNIVAS

SQL Server 2008 R2 on Window Server 2008 Physical Memory keep growing


We are running SQL Server 2008 R2 on on Window Server 2008 (x64).

Once in every 2 weeks we end up restarting the SQL Server, due to  Physical Memory keep growing.

Is there a good way to pinpoint the problem what cause this Physical Memory to grow?

Thank you

Armine Bell

What are the memory limits of SQL Server 2008 Workgroup edition (32 bit/non-R2) running on Windows S


We have a situation where one of our customers is running Windows 2003 Enterprise Edition 32bit with 8 GBs of physical RAM. Upgrading to Windows Server 2008 64bit is not an immediate option (although I wish it were). All the documentation about SQL Server 2008 Workgroup edition indicates that the new version will address 4 GBs of memory however it does not distinguish between 32bit vs 64bit.

Previously we have run SQL Workgroup 2005 under Windows 2003 and have applied the /3gb switch in boot.ini to get SQL to recognize the maximum amount of memory possible. Now that we are using SQL 2008 with the /pae switch, do we need to still specify the /3gb switch or can SQL 2008 32bit actually see more than the 3GBs and address 4? I haven't been able to find any information on this configuration.




SQL Server 2008 Memory Usage


We are using SQL Server 2008 x64 SP1 CU8 EE, and we have 32 GB of RAM on the server. We currently have the instance restricted to 14 GB of RAM, the "Lock Pages in Memory" security setting on the service account, and AWE enabled in SQL Server. This server is part of a 3 node active/active/passive cluster. My question is:


How do I know how much memory sql server is actually using, or if the server is under memory pressure? 

John M. Couch

SSAS 2008 taking all server memory


We just upgraded to SSAS 2008 64 bit (and windows 2008 R2) a couple of weeks ago and now we have an issue that I can't seem to figure out.  Our cubes have not changed at all.  Previous to the upgrade we were running server 2003 and SSAS 2005 54 bit.

The issue is over the past week the SSAS servers have decided to take up almost all the memory (about 90%) on the server and render itself useless.  It is the SSAS service that is taking up almost all the memory and if I stop other services to free up some it just takes that up too.  So here is a break down of the issue and what I have tried.  The issue seems to be happening faster and faster now so I am at a loss currently.

Setup that has issue:

  • SSAS 2008 SP1 64 bit on SQL 2008 R2
  • Default configs as that worked previously in 2005
  • not structure changes
  • machine has 2 quad core procs and 16 GB mem
  • accessed via web calls and linked server on SQL server on different machine

What I have tried

  • install CU10 (due to other issue)
  • rebuild aggregations
  • reduce memory limits to 40% for low and 50% for max respectively

Results of what I have tried so far: memory still hits the 90% within 24 hours so that tells me the changes have done nothing.  I don't know what to do. <

Memory error: Allocation failure : Not enough storage is available to process this command. .



I get the error message above. Cube has 3 measure groups, 1 main fact and 2 many to many facts. Main fact has 450k rows, other two have 165 rows and 2200k rows. It also has 4 dimensions. Dimensions have max 10000 rows.

I get the errors when I add a "calculated" fact to the the main fact group, which is a distinct count on a key. When I remove that fact, the error is gone.

I am using sql 2005 enterprise with the latest sp's and patche on a windows 2003 server with 4GB 32 bit. Any ideas ?


SSRS 2008 log file - maximum memory limit



In my SSRS 2008 log file, in few starting lines I see these lines;

INFO: Reporting Services starting SKU: Enterprise
resourceutilities!ReportServer_0-3!1874!10/05/2010-13:32:29:: i INFO: Maximum memory limit is 2097152Mb

What does it mean by maximum memory  limit is 2097152Mb?

I have set the maximum memory limit for SSRS to 5 GB.



SQL Server 2008 - High Memory Usage


Hi ,

  We are facing problem with the high memory usage with SQL Server 2008. 64 bit , standard edition sp2 . 8 GB RAM , 8 processors .

Memory usage keep on growing ..sometimes it will reach to 7.5 GB also .... and its not coming down ..untill we restart the service...

These are the details of DBCC MEMORYSTATUS :: Please suggest how to overcome this problem

Memory Manager                           KB
---------------------------------------- -----------
VM Reserved                              8484792
VM Committed                             4220368
Locked Pages Allocated                   0
Reserved Memory                          1024
Reserved Memory In Use      &n

SQL Server 2008 R2 on Windows Server 2008 R2 Memory Usage issues and not giving memory back


Okay so i have a physical server i am trying to replace with a virtual server. Its doing a large load process every night, about 190GB on one disk.

The VM is:

On vSphere 4.1 ESXi

O/S is Windows Server 2008 R2 Enterprise

SQL version is SQL Server 2008 R2 Enterprise.

4 vCpus

Now 20GB of memory

Disks assigned to this virtual are all seperate LUNs except for the O/S which is on a shared LUN which is not busy. So 1 LUN/Datastore per 'disk drive on this SQL virtual. Data/Log/TempDB/Backup are on their own LUNs.

All the recommended exclusions are in place for McAfee on a SQL Server.

ESXi 4.1 Enterprise host is not overcommitted. Has 4 x 6core 2.6Ghz AMD processors. BL685c G6 and 128GB of memory. Almost nothing else is running at the time i am testing this load and host is not overcommited or stressed or anywhere near it.

SQLServer process shows 127,236K Working Set and 130,040K Peak working set and memory Private working set 88,828k and Commit Size of 302,792k.

It seems when i check the memory usage in task manager it doesn't show much. In Windows 2003 X64 in would show the GB being used, like 8GB right in task manager. But i don't see that in Windows 2008 R2 task manager. I downloaded SysInternals and it shows the memory on the Windows 2008 r2 box as 15Gb of AWE memory. How can this be? The case is th

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend