HELP: SQL Server Won't Release Memory

[Tod1d]

Hi,

I am having an issue with SQL server that slows everything to a crawl,
and makes almost any query impossible to complete.

Here are the symptoms:
I stop SQL, I start SQL. The task manager shows SQL starting up and
quickly allocates 50 MB of memory. I then open SQL Manager and I can
open up the instance of SQL Server and I expand to see all of the
databases, memory rises to about 70MB allocated. I then expand a
database and view a list of tables. The memory allocation quickly
rockets to 860+ MB and any attempt to query they database results in
huge hang times.

I have MacAfee anti-virus in place and it is constantly scanning for
viruses, so I don't think it is a virus. The log files do not appear
to be large. I have done everything I know to do to resolve this. Any
help would be much appreciated.

Does anyone have any ideas?

Thank you for taking the time to read this.

Tod

[Simon Hayes]

Re: SQL Server Won't Release Memory


"Tod1d" <tod124@icqmail .com> wrote in message
news:d3ce916b.0 309160556.72dbc 548@posting.goo gle.com...[color=blue]
> Hi,
>
> I am having an issue with SQL server that slows everything to a crawl,
> and makes almost any query impossible to complete.
>
> Here are the symptoms:
> I stop SQL, I start SQL. The task manager shows SQL starting up and
> quickly allocates 50 MB of memory. I then open SQL Manager and I can
> open up the instance of SQL Server and I expand to see all of the
> databases, memory rises to about 70MB allocated. I then expand a
> database and view a list of tables. The memory allocation quickly
> rockets to 860+ MB and any attempt to query they database results in
> huge hang times.
>
> I have MacAfee anti-virus in place and it is constantly scanning for
> viruses, so I don't think it is a virus. The log files do not appear
> to be large. I have done everything I know to do to resolve this. Any
> help would be much appreciated.
>
> Does anyone have any ideas?
>
> Thank you for taking the time to read this.
>
> Tod[/color]

Have you tried excluding your MSSQL data folders (all the .mdf/.ndf/.ldf
files) from the virus scan? If the antivirus software is scanning a database
file when MSSQL starts up, then there could easily be a problem of some
kind.

http://support.microsoft.com/default.aspx?scid=kb;en-us;309422

Simon

[Erland Sommarskog]

Re: HELP: SQL Server Won't Release Memory

[posted and mailed, please reply in news]

Tod1d (tod124@icqmail .com) writes:[color=blue]
> I am having an issue with SQL server that slows everything to a crawl,
> and makes almost any query impossible to complete.
>
> Here are the symptoms:
> I stop SQL, I start SQL. The task manager shows SQL starting up and
> quickly allocates 50 MB of memory. I then open SQL Manager and I can
> open up the instance of SQL Server and I expand to see all of the
> databases, memory rises to about 70MB allocated. I then expand a
> database and view a list of tables. The memory allocation quickly
> rockets to 860+ MB and any attempt to query they database results in
> huge hang times.[/color]

McAfee could indeed be a culprit, as Simon suggested. You should
also check if you have your databases set to autoclose, and you should
also check that you don't have ODBC tracing on. (Check this from the
ODBC applet in the Control Panel.)

As for the memory, this behaviour is by design. SQL Server grasps as
much memory it can, because it thinks that memory is good for cache.
If there are other applications on the same machine, SQL Server will
yeild if they need memory, but it may not yield fast enough. You can
control how much memory SQL Server may use from the Memory tab in
Enterprise Manager.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp