Hey all, first posting here. Did some checking around on Google for
this issue before asking here but so far am coming up with no clear
idea.

Have W2K3 Server SP2 (4GB RAM) with SQL Server 2000 SP4 and am
noticing sqlserver.exe gobbling up 1.7GB+ RAM. Two days ago I rebooted
the server for general updates and sqlserver.exe was only using 65MB.
Two days later, 1.7GB+!? Does that seem even remotely right? In doing
more research I came upon KB916287 but have yet to apply it. No
mention of it fixing any memory leaks either. I can't find anything
that elaborates on the included fixes other than what's individually
mentioned in KB894905. Anyone experience the same stuff I am? Any tips
would be great.

- Thee Chicago Wolf

- Thee Chicago Wolf

The Chicago Wolf,

SQL Server will use any available memory that it can to optimize its own
performance, so 65 MB is just what it needs when it first gets started. SQL
Server makes heavy use of memory caches, so that is why additional memory is
being used taking you up to 1.7GB. Any data or procedures cached in memory
already reduce I/Os , which usually improves performance.

However, if there is pressure for memory from other applications, SQL Server
can automatically start giving up some of that memory to the other
applications. You can also set the SQL Server to use a MAX amount of memory
if that is necessary. This is most commonly done if a machine is running
more than one instance of SQL Server or there are other major processes
which need to have memory saved for them.

So, in general, everything is just fine. You have to decide if it is
causing you any real problems and what to do about that. For most users,
letting SQL Server dynamically manage memory is the best choice.

RLF


"Thee Chicago Wolf" <.@.> wrote in message
news:lisb14hpojp2ron0ihr8deenbl0m6jpj1n@4ax.com...
> Hey all, first posting here. Did some checking around on Google for
> this issue before asking here but so far am coming up with no clear
> idea.
>
> Have W2K3 Server SP2 (4GB RAM) with SQL Server 2000 SP4 and am
> noticing sqlserver.exe gobbling up 1.7GB+ RAM. Two days ago I rebooted
> the server for general updates and sqlserver.exe was only using 65MB.
> Two days later, 1.7GB+!? Does that seem even remotely right? In doing
> more research I came upon KB916287 but have yet to apply it. No
> mention of it fixing any memory leaks either. I can't find anything
> that elaborates on the included fixes other than what's individually
> mentioned in KB894905. Anyone experience the same stuff I am? Any tips
> would be great.
>
> - Thee Chicago Wolf
>
> - Thee Chicago Wolf

>SQL Server will use any available memory that it can to optimize its own
>performance, so 65 MB is just what it needs when it first gets started. SQL
>Server makes heavy use of memory caches, so that is why additional memory is
>being used taking you up to 1.7GB. Any data or procedures cached in memory
>already reduce I/Os , which usually improves performance.
>
>However, if there is pressure for memory from other applications, SQL Server
>can automatically start giving up some of that memory to the other
>applications. You can also set the SQL Server to use a MAX amount of memory
>if that is necessary. This is most commonly done if a machine is running
>more than one instance of SQL Server or there are other major processes
>which need to have memory saved for them.
>
>So, in general, everything is just fine. You have to decide if it is
>causing you any real problems and what to do about that. For most users,
>letting SQL Server dynamically manage memory is the best choice.
>
>RLF

Russell,

Thanks for your response. I'm not an SQL person at all but I've been
trying to figure out if this is normal or not and now that I know it
is, it opens up more questions. Would it be a waste of time to even
install the post SP4 roll-up from KB916287?

Being that it's a server with 4GB RAM and we're not making use of the
/3GB switch in the boot.ini, would it be worth it to do it in case
other apps are starving and SQL Server is not giving up RAM to other
apps?

I was starting to think it was the size of the database file (2.32 GB)
that was causing it to bloat up so quick. As I said before, I am not
an SQL person and the individual who originally set this up on the
server is long gone so we're trying to figure things out and what was
set. This database file has info from 2006 to current and we are also
trying to figure out how to pull out all the 2006 and 2007 data, save
them to individual database files, and leave only what been done for
2008 intact. Any for a clueless newbie there? ;-) Thanks much for
the above info.

- Thee Chicago Wolf

> Russell,
>
> Thanks for your response. I'm not an SQL person at all but I've been
> trying to figure out if this is normal or not and now that I know it
> is, it opens up more questions. Would it be a waste of time to even
> install the post SP4 roll-up from KB916287?
>
> Being that it's a server with 4GB RAM and we're not making use of the
> /3GB switch in the boot.ini, would it be worth it to do it in case
> other apps are starving and SQL Server is not giving up RAM to other
> apps?
>
> I was starting to think it was the size of the database file (2.32 GB)
> that was causing it to bloat up so quick. As I said before, I am not
> an SQL person and the individual who originally set this up on the
> server is long gone so we're trying to figure things out and what was
> set. This database file has info from 2006 to current and we are also
> trying to figure out how to pull out all the 2006 and 2007 data, save
> them to individual database files, and leave only what been done for
> 2008 intact. Any for a clueless newbie there? ;-) Thanks much for
> the above info.
>
> - Thee Chicago Wolf

Thee Chicago Wolf,

I don't know if KB916287 has fixes included that you want, but it will not
fundamentally change anything about the memory allocation. Since you are
not using /3GB you are leaving Windows with 2GB and have 2GB available to
SQL Server. Before doing that, determine if you need it and be sure that you
understand the consequences.
http://support.microsoft.com/kb/274750/en-us

The database size is not causing any memory bloat. As I said before, SQL
Server would not consider 1.7 GB to be bloat, but to be efficient use of
cache. I would not move the data out if I am still making regular use of
it, unless it is causing me performance problems for my main line of
activity.

However, moving data to another database on the server is possible by a
number of techniques. (Likewise, you can move the data to another server.)
You need to fully understand the consequences of moving data of course,
since there may be internal references that are affected.

Still, if you can do it without harm, one easy way to move data between
databases on the same server is:

INSERT INTO HistoryDB.dbo.HistoryTransactions
SELECT column list FROM LiveDb.dbo.Transactions
WHERE TransactionDate < '2008/01/01'

Then, once you are sure that everything is OK.

DELETE LiveDb.dbo.Transactions
WHERE TransactionDate < '2008/01/01'

However, if you still use that data on your server because of queries
against HistoryDB it will still play a part in using the cache space. Also,
DTS is a good tool for automating data movement, especially between servers,
but it is another tool to learn. (If your needs are simple, it is easy to
use.)

FWIW,
RLF