We have a sql server which runs sql 2005. Yesterday and today our WhatsUp monitor tool frequently sent alert email that
Disk Space - C: (WMI) [10GB] is Down at least 10 min
Disk Space - D: (WMI) [10GB] is Down at least 10 min
Disk Space - E: (WMI) [10GB] is Down at least 10 min
which later on when we check each drive the space is not under 10GB.
and also at this time there is no connection to the sql server, it says timed out (probably due to low memory).
we couldn't figure out why this happens. what we know is that we were running at low memory (<50%) but don't know what caused this. From Sql server log, i see a pattern that database log backup, database backup and database differential backup followed by the error that “A significant part of sql server process memory has been paged out.…”.
we don't know whether the memory problem is related to the backup.
After a while we would receive email that
Disk Space - C: (WMI) [10GB] is Up at least 5 min
Disk Space - D: (WMI) [10GB] is Up at least 5 min
Disk Space - E: (WMI) [10GB] is Up at least 5 min
without doing anything.
Can anybody shed some light on this? Thank you! If you need more detail pls let me know.
As a default SQL Server uses all RAM, physical and virtual. This is bad.
A general rule is to limit the SQL Server to Physical RAM - 2gb (for OS and other processes). Depending on what else is running on the server, 2gb may not be enough. You will have to look at your individual situation to determine if that is enough.
Thanks for the reply! Should we change the setting? Our DBA said "the default setting is to let SQL use all the memory it needs."
Supposedly, the idea is that it will let go of the memory and give it up to other processes. But based on what we experienced looks like that isn't the case.
If SQL Server is the only thing running (that is, no IIS/Reporting Services/SSIS/SSAS/Other apps), then I'd recommend leaving the value. SQL Server will do as much as it can to avoid paging to disk - it will shrink its cache as possible (but it can only go so far), and it can only go so fast, so a fast growing app can quickly overwhelm free memory, and SQL Server gets paged out before it can react to the low memory condition.
It sounds likely that you've had a misbehaving process require more memory than what is available. The next step would be to identify which process was taking up too much memory, and how much it required. If it is likely to require this amount again, then it may be worth changing the maximum amount of memory SQL Server will use. However, if it only requires this once per day, you may be giving up GBs of RAM that could be better used by SQL Server during the other 23 hours.
Ideally, identify what the offending process is, and establish whether it is likely to do it again. If it will, see if you can host it on another server. Native SQL Server Backups should not cause this sort of problem, but 3rd party backup tools may.
Thanks very much for the answer! There is no IIS, RS, IS, AS or other apps running as far as I know. During the several hours window when the problem occured, the database dump files were winzipped to a folder on D drive and they totally have 49GB in size. Could this have caused the memory issue?
WinZipping very large files will definitely cause some external memory pressure that could cause SQL Server 2005 to reduce it's working set like you saw.
Please tell us the full results of SELECT @@VERSION (so we can see whether you are on Enterprise Edition of SQL Server and whether you are running 64-bit). Also, please tell us how much RAM is installed on the box.
I need to know this so I can tell you whether you will be able to use "Lock Pages In Memory", and what I think your Max Memory setting should be.
I have to disagree with you DBA about the Max Memory setting. Even on a dedicated SQL Server box, you should set the Max Memory setting to something besides the default. For example, on a 16GB box, I would try 14500 (14.5GB) as a starting point.
You are also on Build 3054, which is pretty old. There have been a lot of memory related fixes in newer CU builds since then. I would consider upgrading to CU7 (Build 3239), although this is not your main problem.
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
i think 16GB RAM is installed on the box, originally it's 8gb and then another 8gb is added.
Yes I'll mark answers which helped, how to mark multiple answers? Thanks!
Great! You have 64-bit Enterprise Edition. You should definitely grant the "Lock Pages in Memory" right to the SQL Server Service account, and I think you should try setting Max Memory to 14500 (14.5GB). The blog posts below give some more detail about this.
When you get a chance, I would get CU7 installed.
Tom Phillips wrote: SQL server never releases RAM once it has allocated it.
This isn't true in SQL Server 2005. From Books Online (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2f3c7e4e-3270-4c11-839b-32cc0f40eef7.htm)
Books Online 2005 wrote:
When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.
The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.
As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
Although it does say that and they tried to make it work in 2005, in reality SQL Server only releases RAM if it detects "significant" memory restrictions, less than 10mb available. If you have less than 10mbs available, you are already in trouble.
Also, please see one of the many, many memory related threads on this forum.
I think it comes down to exactly how much pressure you're under. If the system is allocating memory quicker than SQL Server can free it, then you'll go to the page file. I've analysed one system that happily works its way between 800 and 1600 MB depending on memory pressure. It basically comes down to knowing how much memory to allocate for each individual system, and using every option possible to keep SQL Server out of the page file.
This is a commone problem with systems under heavy load / have memory pressure. You read this article to gain a better understanding of this error, its possible cause, and how to solve the issue.
- Proposed as answer by Gunner999 Sunday, November 01, 2009 2:10 AM
Couple of points to add to everyones input. If your SQL is on a Virtual Machine, then (depending which vendor) the balloon driver could be fooling SQL to releasing lots of memory. You should ideally raise your minimum SQL memory and perhaps have a memory reservation set at the VM level too.
Secondly using lock pages in memory has been suggested. This is definately something you should consider but again if we are talking about within a VM, you should avoid doing this because should the nasty balloon driver kick in in that scenario, an even worse scenario could occur where the OS releases memory and you will then get heavy OS level paging.
IMO you should always set a maximum memory setting to prevent SQL from consuming everything. I think some of the levels mentioned are about correct.
Regards, Mark Broadbent. www.twitter.com/retracement http://tenbulls.co.uk