August 9, 2002 at 6:29 am
Running SQL SERVER SvcPak 2 on Win2000 SvcPak3. Has 3 18G drives and 1.5G memory.
I have a server with 1.5G of memory and it is constantly getting full. What's happening is that the memory gets full and instead of reallocating space when a job, report, etc. finishes it holds the space and what eventually happens is I have reports crashing because there is not enough memory left to support the report. I am 100% positive that there is not enough activity on this server to take up almost the full 1.5G that is currently on it.
My problem: The only way I can get memory to release is by stopping and starting SQL Server. Is there a better way? Is this normal? Please let me know. Also, ask any questions that you need answered to make a more informed decision.
Thanks for your support.
August 9, 2002 at 8:16 am
It's normal and expected for SQL to gradually acquire all the RAM alloted/available to it. It uses this to cache data and query plans. Stopping the service means it has to start all over. If a query needs more memory than is available, data and/or query plans get aged out of the cache and worst case it will use the swap file.
Is the report a stored proc? Have you looked at the query plan for it?
Andy
August 9, 2002 at 9:59 am
I'd suspect you have an application problem. SQL Server should take up most everything except 128MB or so which is left for the OS. I had about 1GB of databases on a server with 2GB of RAM and after a day or so of running, SQL would use 1.7GB of RAM. Never experienced any issues with memory except when queries were extremely poorly written (cross joins or something similar).
I'd agree with Andy, you need to check and see what's wrong with your reports and how they might be rewritten.
Steve Jones
August 9, 2002 at 11:21 am
I'm using crystal reports 8.x and there is really nothing special about the reports. It does some grouping and summing, but nothing more. Its all handled in the report so I have not written any stored procedures for it. Ideas?
It's normal and expected for SQL to gradually acquire all the RAM alloted/available to it. It uses this to cache data and query plans. Stopping the service means it has to start all over. If a query needs more memory than is available, data and/or query plans get aged out of the cache and worst case it will use the swap file.
Is the report a stored proc? Have you looked at the query plan for it?
Andy
http://www.sqlservercentral.com/columnists/awarren/
[/quote]
August 9, 2002 at 12:26 pm
Profile while the report runs, see what its doing.
Andy
August 12, 2002 at 8:05 am
I've seen some "state of the art SQL" regarding crystal reports and strongly advice sql-tuning.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 13, 2002 at 12:22 am
We do face a similar problem. In our case its the DTS which creates the problem.
We have a DTS which transferrs all tables and stored procedures from the Production Server to Backup Server. The DTS is configured and scheduled in the Backup Server. As the DTS starts running the SQL Server memory in the Production Server gets used up. We have 2 GB of memory in the Server and by the time the DTS finishes it will reach something around 1.6.
August 15, 2002 at 12:18 am
if your application has fulltext search, that is the problem. you must fix the momory to a limit and leave enough memory for mssearch. please refer the book online.
we do have the problem before, but we fixed it now.
george zhao
george zhao
August 15, 2002 at 10:10 am
TYGUN,
Have you tried reducing the memory allocation in the SQL Server Properties/Memory Tab?
We run Terminal Services off of some of our SQL Servers and we routinely slide the max memory back by 256+MB. This really helps...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply