sqlservr.exe doesn't relase the memory

Microsoft SQL Server

Hello,

We are using MSDE for our application. The service MSSQL$App will start
automatically or when the application is started. While using the
application, the memory usage by sqlservr.exe will be increasing and when the
application is closed, the sqlservr.exe is not releasing the memory or
atleast the memory usage is not coming down. Can any one help on this? Is
this a MSDE bug or our application should manually release the memory used by
sqlservr.exe
That is 'normal' behaviour of SQL, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;321363
Hi Gijs,

Thanks for the response. If this is the case that SqlServr.exe uses the
memory and is a normal behaviour, then how can i minimize it "on exit of the
application". I have already saw the solution saying to set min and max
memory. But, can't I minimize on exit? Pls help me.

-Sreedhar
Hi Sreedhar,

If your application is the only application taht requires the instance
of MSDE to be running, then you can modify the application to issue a
shutdown command on exit. But beware: there are several pitfalls:

1. If some other application unexpectedly also relies on the SQL Server
service being up and running, shutting it down won't make you many new
friends :-)

2. The SHUTDOWN command can only be issued by members of the sysadmin or
serveradmin fixed server roles. Having your application running with
that level of authority is a tremendous risk.

I'd say, as Andrea also writes, that it's much better to just leave the
server running. If you're left with too little memory for your other
applications, get some extra memory or buy an extra machine to serve as
a dedicated database server. Hardware is cheap nowadays.

Or you can just terminate the SQL Server service manually if you need to
free the memory occasionally.

Best, Hugo
You could check the number of users, if you're the only active user,
shutdown it down and start it again. That way, SQL willl start again at
'minimun' memory usage.
Hi Gijs,

There are a few reasons why I would definitely NOT do it this way:

- The number of active users (by which I presume you mean: connections)
is a dangerous indication of (lack of) activity, to say the least. Many
client programs are progrmmed to drop a connection when it's no longer
used, reconnecting when they need to send data or execute a query.

- If you MUST shutdown MSDE to reclaim the memory (and as I already
stated in my previous message, that would NOT be my first choice - nor
my second or third), then don't do it when you start the application,
but when you terminate it. Doing it at start means that the time to
start the application will rise (shutdown and startup of MSDE cost quite
some time). Plus all data that might have bene in cache wil be flushed
from memory, all memory will have to be allocated again, etc etc. Don't
release the resources just before you're gooing to use it - release them
when you're done using them.

Best, Hugo
<snap


I absolutely agree, it would be sort of a last resort... More memory in
the machine would be my first and second choice, followed by a more
dedicated server (running less other applications).
Hello Experts,

Your information was very useful. But, still i have some problem. My
application runs on MSDE and not the SQLServer. And it is the only
application that runs on this instance (MSSQL$APPNAME). I checked out an
article at http://support.microsoft.com/default.aspx?scid=kb;en-us;321363 .
This article applies to SQL 7.0 and SQL 2000. It does not mention MSDE. Is
this applicable to MSDE also? If so, where can I find some thing more
specific to MSDE. Please help me.

-Sreedhar
hi Sreedhar,
MSDE "IS" SQL Server... it's one of the available editions, with some
limitations and without standard SQL Server Client Tools...
hi Sreedhar,
you cant... SQL Server is not dependent on your application behavior... it's
a separate application/service running on it's own, eventually serving your
application as well, among others, if any...
what is the problem of SQL Server not releasing it's memory?