Merge Agent retrying forever

We have a Merge Replication with Publisher and Distributor on SQL Server 2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing through VPN phone lines.
From time to time one or more subscribers can't reconnect to the Publisher. By inspecting their Merge Agents (through the Replication Monitor) in the Publisher Server we see that they are marked as retrying forever. In the distribution database MSmerge_hi
story table these agents have the "runstatus" column marked with 5 (retry).
We are not able to stop the agents. We have shut down the Publisher server but still the agents show the same status. We have noticed that in order to solve the problem we must synchronize those subscribers through UTP fast cables.
Any help will be appreciated
Righth click on the merge agent and click on Agent Properties. Click on the
Steps tab and double click on a step. On the Edit Step window, click on
the Advanced tab and change the number of retry attempts to a number other
than the default (usually 10 for the run Agent step and 0 for the other
two). I put 2 for all of my steps. You should also change the retry
interval, I put 1 for mine. Click apply and next to set the next step.
Hope this helps.
"Tony2000" <anonymous@.discussions.microsoft.com> wrote in message
news:F71724A8-457A-4B2F-BB8D-B92B0EC3ADD2@.microsoft.com...
> We have a Merge Replication with Publisher and Distributor on SQL Server
2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing
through VPN phone lines.
> From time to time one or more subscribers can't reconnect to the
Publisher. By inspecting their Merge Agents (through the Replication
Monitor) in the Publisher Server we see that they are marked as retrying
forever. In the distribution database MSmerge_history table these agents
have the "runstatus" column marked with 5 (retry).
> We are not able to stop the agents. We have shut down the Publisher server
but still the agents show the same status. We have noticed that in order to
solve the problem we must synchronize those subscribers through UTP fast
cables.
> Any help will be appreciated
sql

Merge agent error

We have a client on SQL Server 2000 sp2 whose merge replication had the
following error:
The merge process could not update the last received generation.
Does anyone have an idea as to the possible causes?
thanks
It turned out o be query timout invlolving sp_msdelgenzero
"Peter Feakins" wrote:

> We have a client on SQL Server 2000 sp2 whose merge replication had the
> following error:
> The merge process could not update the last received generation.
> Does anyone have an idea as to the possible causes?
> thanks
>

MemToLeave and ODBC

We are running SQL Server 2005, SP2, Standard Edition, 64bit.
We are considering an ODBC solution for client reporting, and have been
reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
I was wondering if the queries sent via this ODBC connection, also utilize
MemToLeave, or if those queries use the Buffer Pool?
And then a separate question, not involving ODBC...does an adhoc query
executed from Management Studio utilize Buffer Pool memory, if it needs less
than 8K contiguous memory, or do all adhoc queries in general go directly
against MemToLeave?
As a side note, I have read some from Ken Hendersons blog, threads in this
discussion group, and other posts on the web. I cannot seem to find the
answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still seeking for answers. Please help.
cbrichards wrote:
>We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>We are considering an ODBC solution for client reporting, and have been
>reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>I was wondering if the queries sent via this ODBC connection, also utilize
>MemToLeave, or if those queries use the Buffer Pool?
>And then a separate question, not involving ODBC...does an adhoc query
>executed from Management Studio utilize Buffer Pool memory, if it needs less
>than 8K contiguous memory, or do all adhoc queries in general go directly
>against MemToLeave?
>As a side note, I have read some from Ken Hendersons blog, threads in this
>discussion group, and other posts on the web. I cannot seem to find the
>answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||On a 64bit SQL instance, you should have little to worry about MemToLeave
because you have a huge virtual address space to go about. I might be wrong,
but my understanding is that whether MemToLeave is used depends on factors
such as memory allocation size, not on what database API is being used on the
client side.
Linchi
"cbrichards via SQLMonster.com" wrote:
> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> We are considering an ODBC solution for client reporting, and have been
> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
> I was wondering if the queries sent via this ODBC connection, also utilize
> MemToLeave, or if those queries use the Buffer Pool?
> And then a separate question, not involving ODBC...does an adhoc query
> executed from Management Studio utilize Buffer Pool memory, if it needs less
> than 8K contiguous memory, or do all adhoc queries in general go directly
> against MemToLeave?
> As a side note, I have read some from Ken Hendersons blog, threads in this
> discussion group, and other posts on the web. I cannot seem to find the
> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
RAM (14GB set as Max Server Memory), are you saying that just because I am
running 64bit, that I have a huge virtual address space? Where does this huge
virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
Linchi Shea wrote:
>On a 64bit SQL instance, you should have little to worry about MemToLeave
>because you have a huge virtual address space to go about. I might be wrong,
>but my understanding is that whether MemToLeave is used depends on factors
>such as memory allocation size, not on what database API is being used on the
>client side.
>Linchi
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>[quoted text clipped - 11 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via http://www.sqlmonster.com|||Yes, SQL Server isn't even aware of what API is used by the client application.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:76F3D546-8C00-4E8A-81A6-CDA22E1E0193@.microsoft.com...
> On a 64bit SQL instance, you should have little to worry about MemToLeave
> because you have a huge virtual address space to go about. I might be wrong,
> but my understanding is that whether MemToLeave is used depends on factors
> such as memory allocation size, not on what database API is being used on the
> client side.
> Linchi
> "cbrichards via SQLMonster.com" wrote:
>> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
>> We are considering an ODBC solution for client reporting, and have been
>> reading that the ODBC driver uses MemToLeave memory, up to 7MB per connection.
>> I was wondering if the queries sent via this ODBC connection, also utilize
>> MemToLeave, or if those queries use the Buffer Pool?
>> And then a separate question, not involving ODBC...does an adhoc query
>> executed from Management Studio utilize Buffer Pool memory, if it needs less
>> than 8K contiguous memory, or do all adhoc queries in general go directly
>> against MemToLeave?
>> As a side note, I have read some from Ken Hendersons blog, threads in this
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>>|||MemToLeave is a virtual memory address space concept. It has nothing to do
with how mch physical memory you have.
Let's forget about performance and physical memory for now. When you write a
program in a high-level programming language, you may feel like you can just
keep allocating data structures. But eventually you'll run into out of memory
error because the compiler/OS knows that there is a limit to the number of
unique virtual memory addresses for a process. If those addresses are all
used up in the process, you are not allowed to allocate any more and it's the
OS' job to keep track of the usage of the virtual memeory addresses.
On a 32-bit system, the number of virtual memory addresses is limited.
Basically there are 2GB for a program to allocate its data structures (the
other 2GB are taken by the kernel). Since the SQL process' virtual address
space may get fragmented after it has been allocating data structures for a
while and fragmentation can get so bad so that it may not be able to find
contiguious virtual addrsses for larger data structures (i.e. larger than
8K), the strategy that SQL Server uses is to pre-allocate a chunk of virutal
addresses when it starts so that this chunk is available regardless of it
snormal allocation activities. This pre-allocated chunk of virtual memory
addresses is MemToLeave.
Now with a 64-bit system, the virutal address space is so large that there
is little to no danger that a SQL instance (primarily its buffer pool) will
ever use up so much of virtual addresses that its process will have no
contiguious virtual addresses left for larger data structures.
Again, this is all related virtual memory addresses, and has nothing to do
with how much physical memory you may have on a particular system. In
reality, of course if you don't have enough physical memory, you risk
swapping things to paging files and your performance will suffer.
Linchi
"cbrichards via SQLMonster.com" wrote:
> So if I am running SQL Server 2005, SP2, Standard Edition, 64bit, with 16GB
> RAM (14GB set as Max Server Memory), are you saying that just because I am
> running 64bit, that I have a huge virtual address space? Where does this huge
> virtual address space come from if 14Gb is dedicated to SQL Server? The disks?
>
> Linchi Shea wrote:
> >On a 64bit SQL instance, you should have little to worry about MemToLeave
> >because you have a huge virtual address space to go about. I might be wrong,
> >but my understanding is that whether MemToLeave is used depends on factors
> >such as memory allocation size, not on what database API is being used on the
> >client side.
> >
> >Linchi
> >
> >> We are running SQL Server 2005, SP2, Standard Edition, 64bit.
> >>
> >[quoted text clipped - 11 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via http://www.sqlmonster.com
>|||I am really perplexed at this whole virtual address space with 64bit and I do
not seem to be getting a straight answer. It is probably the way I am
phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
my disks, then if the 14GB is being used by SQL Server, and then another
query comes along, and the 14GB is consumed, is the virtual address space the
2GB left for the operating system, plus the 100GB carved out on the SAN?
Tibor Karaszi wrote:
>Yes, SQL Server isn't even aware of what API is used by the client application.
>> On a 64bit SQL instance, you should have little to worry about MemToLeave
>> because you have a huge virtual address space to go about. I might be wrong,
>[quoted text clipped - 19 lines]
>> discussion group, and other posts on the web. I cannot seem to find the
>> answer to the above questions. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||First of all, virtual address space of a process has nothing to do with how
much physial memory or disk storage you may have on a system. On 32-bit
Windows, the user-mode virtual address space is 2GB (or 3GB with /3GB switch
in the boot.ini). On 64-bit Windows, the virtual address space of a process
is 8TB. These numbers don't change regardless how much RAM or disk storage
you have.
Secondly, the size of the virtual address space is a function of the width
of the address or the number of unique pointer values. So in theory, on
32-bit system, the virtual address space is 2^32, and if each address points
to a byte, that's 4GB. Also in theory, on a 64-bit system, the virtual
address space is 2^64, and if each points to a byte, that's more than 16
exabytes. But actual implementations often impose additional restriction. For
instance, 32-bit Windows gives 2GB to the kernel and leave 2GB to your app to
use. And since 16 exabytes are excessive, currently beyond reach anyway, and
costly to support, only 43 bits are actually used for user-mode virtual
addresses on current x64, and that's ~8TB.
Now, if you really end using this much virtual address space, they (i.e.
virtual memory allocated to represent your data structures) have to be
backed/supported with real stores, which can be physical memory or paging
files.
Linchi
"cbrichards via SQLMonster.com" wrote:
> I am really perplexed at this whole virtual address space with 64bit and I do
> not seem to be getting a straight answer. It is probably the way I am
> phrasing my questions. If so I apologize. If I have 16GB RAM, of which 14GB
> is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
> my disks, then if the 14GB is being used by SQL Server, and then another
> query comes along, and the 14GB is consumed, is the virtual address space the
> 2GB left for the operating system, plus the 100GB carved out on the SAN?
> Tibor Karaszi wrote:
> >Yes, SQL Server isn't even aware of what API is used by the client application.
> >
> >> On a 64bit SQL instance, you should have little to worry about MemToLeave
> >> because you have a huge virtual address space to go about. I might be wrong,
> >[quoted text clipped - 19 lines]
> >> discussion group, and other posts on the web. I cannot seem to find the
> >> answer to the above questions. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||On Thu, 04 Oct 2007 01:49:25 GMT, "cbrichards via SQLMonster.com"
<u3288@.uwe> wrote:
>If I have 16GB RAM, of which 14GB
>is configured as MaxServerMemory, and I have 100GB carved out on the SAN for
>my disks, then if the 14GB is being used by SQL Server, and then another
>query comes along, and the 14GB is consumed, is the virtual address space the
>2GB left for the operating system, plus the 100GB carved out on the SAN?
There are, or so I heard at some time way in the past, computers with
the architecture where memory and disk are mapped with a common
address space, as if each was an extension of the other. That sounds
like what you are asking about. Microsoft SQL Server does not run on
any such architecture.
To oversimplify a bit... Most of SQL Server's memory is used for
caching database pages. If SQL Server needs a page that is already in
cache it is just referenced from that memory location. If it needs a
page that is not in cache it is read into cache. When there is no
room in memory for a new page a page that hasn't been used recently is
overwritten. When a page is updated it will be written to disk -
eventually. (Logs are handled a bit differently and always written
immediately.)
Sorry if I have misunderstood your statement.
Roy Harvey
Beacon Falls, CT

Memory usage - max setting

I am using SQL 8 Personal edition with sp2 applied. I set the max
server memory to 32MB and leave the min server memory at 0. When my
application starts hitting the database hard the memory usage reported
through task manager peaks between 41-42MB. I've stopped and restarted
the MSSQLserver service and checked that the running values are what I
set them to be. Does anybody have any ideas as to why the sqlservr.exe
would be utilizing more memory than the configured value?

This message was posted some years ago but nobody answered. Now I have
the same problem! Does anybody have an explanation?

Thanks a lot[posted and mailed, please reply in news]

Frank Esser (essfra@.gmx.de) writes:
> I am using SQL 8 Personal edition with sp2 applied. I set the max
> server memory to 32MB and leave the min server memory at 0. When my
> application starts hitting the database hard the memory usage reported
> through task manager peaks between 41-42MB. I've stopped and restarted
> the MSSQLserver service and checked that the running values are what I
> set them to be. Does anybody have any ideas as to why the sqlservr.exe
> would be utilizing more memory than the configured value?
> This message was posted some years ago but nobody answered. Now I have
> the same problem! Does anybody have an explanation?

Since SP3 is the latest service pack, you may want to start there and
see if it helps.

As far as I know, this should not occur, so you may have a memory leak.
Hm, are you using any extented stored procedures or calling any OLE
objects in process? I don't if these could take a tool.

I'll ping the people in our private MVP forum, to see if anyone has an
idea, but in the end the only way to resolve may be open a case with
Microsoft.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote:
> [posted and mailed, please reply in news]
> Frank Esser (essfra@.gmx.de) writes:
> > I am using SQL 8 Personal edition with sp2 applied. I set the max
> > server memory to 32MB and leave the min server memory at 0. When my
> > application starts hitting the database hard the memory usage reported
> > through task manager peaks between 41-42MB.

<snip
BOL refers to these settings controlling the buffer pool and doesn't mention
the full VM size reported by task manager. I'm not a SQL Server internals
expert, but if SQL Server is bumping against your max memory setting, I
would expect your VM size in Task Manager to report a higher number (thread
stacks, other memory allocators, etc, etc).

Craig|||essfra@.gmx.de (Frank Esser) wrote in message news:<18d2e86d.0402100734.27861260@.posting.google.com>...
> I am using SQL 8 Personal edition with sp2 applied. I set the max
> server memory to 32MB and leave the min server memory at 0. When my
> application starts hitting the database hard the memory usage reported
> through task manager peaks between 41-42MB. I've stopped and restarted
> the MSSQLserver service and checked that the running values are what I
> set them to be. Does anybody have any ideas as to why the sqlservr.exe
> would be utilizing more memory than the configured value?
> This message was posted some years ago but nobody answered. Now I have
> the same problem! Does anybody have an explanation?
> Thanks a lot

The buffer pool is sized on the lower of the amount of physical
memory, or user mode address space (2GB by default)-MemToLeave which
is (again by default) around 384MB. If max server memory is set then
it becomes the limit if it is lower still. So if a server has 1GB and
no limit the BPool will be 1 GB (or more accuratley 1GB of reserved
space - it's not committed until used). On a server with 1GB for
example and max server memory set to 100MB then the BPool is sized at
100MB as this is lower than the physical memory.This in no way
constrains the SQL process from consuming more than 100MB, it merely
limits the address space reserved for the BPool pages (and by
definition the amount of memory that can be committed for thos BPool
pages). To confirm the sizing of the BPool you can use Buffer
Manager\TargetPages and convert from 8k pages into MB.When the server
starts it works out the limit of the BPool, reserves the MemToLeave
area,reserves the BPool and then releases the MemToLeave area. Having
said that, MemToLeave can obviously be greater than 384 MB as on a
server with 1GB, there will be ~1GB of free space in the user mode
address space. So (I am getting to a point!) the max server memory
limits the maximum size of the buffer pool but it should be in no way
taken as a limit to how much memory SQL can commit from its process
address space (2GB by default). Clear as mud :-)

For more on SQL's memory architecture check out
http://msdn.microsoft.com/data/defa...ev_01262004.asp|||Thanks a lot for your answers but indeed I found an answer to my
problem:
SQL server does not stop at the max memory but uses a maximum of 1.5
times of the set max value. When I set the max server memory to 16MB
it will actually use exactly 24MB as maximum, shown in Windows Task
Manager.

It is explained in the online books:

Quote:
************
Note If you have installed and are running the Full-Text Search
support (Microsoft Search service, also known as MSSearch), then you
must set the max server memory option manually to leave enough memory
for the MSSearch service to run. The max server memory setting must be
adjusted in conjunction with the Windows NT 4.0 virtual memory size
such that the virtual memory remaining for Full-Text Search is 1.5
times the physical memory (excluding the virtual memory requirements
of the other services on the computer). Configure the SQL Server max
server memory option so that there is sufficient virtual memory left
to satisfy this Full-Text Search memory requirement. Total virtual
memory - (SQL Server maximum virtual memory + virtual memory
requirements of other services) >= 1.5 times the physical memory.
************

"Craig Kelly" <cnkelly@.worldnet.att.net> wrote in message news:<8ZAWb.8783$hR.245921@.bgtnsc05-news.ops.worldnet.att.net>...
> "Erland Sommarskog" <sommar@.algonet.se> wrote:
> > [posted and mailed, please reply in news]
> > Frank Esser (essfra@.gmx.de) writes:
> > > I am using SQL 8 Personal edition with sp2 applied. I set the max
> > > server memory to 32MB and leave the min server memory at 0. When my
> > > application starts hitting the database hard the memory usage reported
> > > through task manager peaks between 41-42MB.
> <snip>
> BOL refers to these settings controlling the buffer pool and doesn't mention
> the full VM size reported by task manager. I'm not a SQL Server internals
> expert, but if SQL Server is bumping against your max memory setting, I
> would expect your VM size in Task Manager to report a higher number (thread
> stacks, other memory allocators, etc, etc).
> Craig

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creating execution plans are
expensive operations. This is why SQL Server caches plans and pages. How would SQL Server know when
to release something? SQL Server will release memory when the OS becomes pressured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.developersdex.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creating execution plans are
expensive operations. This is why SQL Server caches plans and pages. How would SQL Server know when
to release something? SQL Server will release memory when the OS becomes pressured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max server memory).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
>> For example, thought the day SQL needs more memory and at
>> the end of the day has 1 gig RAM used - if everyone logs out of our
>> application so nothing is hitting SQL, will the memory be released? If
>> not, why not?
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
>> maximum set in the Management Studio. DOes it ever release and free up
>> the memory. For example, thought the day SQL needs more memory and at
>> the end of the day has 1 gig RAM used - if everyone logs out of our
>> application so nothing is hitting SQL, will the memory be released? If
>> not, why not?
>> Darin
>> *** Sent via Developersdex http://www.developersdex.com ***
>

memory on 2005

Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
maximum set in the Management Studio. DOes it ever release and free up
the memory. For example, thought the day SQL needs more memory and at
the end of the day has 1 gig RAM used - if everyone logs out of our
application so nothing is hitting SQL, will the memory be released? If
not, why not?
Darin
*** Sent via Developersdex http://www.codecomments.com ***> For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
No, not because people "logs out". Because reading pages from disk and creat
ing execution plans are
expensive operations. This is why SQL Server caches plans and pages. How wou
ld SQL Server know when
to release something? SQL Server will release memory when the OS becomes pre
ssured for memory, but
not until then. You can set a cap on memory, of course (sp_configure and max
server memory).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Darin" <darin_nospam@.nospamever> wrote in message news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.
gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||SQL Server won't release memory unless it detects memory pressure. The bulk
of SQL memory is used for buffer cache, which is needed to reduce I/O and
improve performance. Even if everyone logs out now, cached data is still
valid and can be used again later for subsequent requests.
If you have other memory-intensive applications running on a box with
limited memory resources, you can set SQL Server max memory to prevent
thrashing.
Hope this helps.
Dan Guzman
SQL Server MVP
"Darin" <darin_nospam@.nospamever> wrote in message
news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
> Running SQL 2005 SP2, the memory (RAM) that SQL is using can have a
> maximum set in the Management Studio. DOes it ever release and free up
> the memory. For example, thought the day SQL needs more memory and at
> the end of the day has 1 gig RAM used - if everyone logs out of our
> application so nothing is hitting SQL, will the memory be released? If
> not, why not?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***|||If the OS doesn't signal that it needs more memory for some other process or
sql server doesn't interally determine that some other sql server
functionality needs more memory, whatever RAM is acquired for the various
buffers will simply stay there indefinitely.
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OLJOsWmiHHA.688@.TK2MSFTNGP03.phx.gbl...
> No, not because people "logs out". Because reading pages from disk and
> creating execution plans are expensive operations. This is why SQL Server
> caches plans and pages. How would SQL Server know when to release
> something? SQL Server will release memory when the OS becomes pressured
> for memory, but not until then. You can set a cap on memory, of course
> (sp_configure and max server memory).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Darin" <darin_nospam@.nospamever> wrote in message
> news:u7f%23mEmiHHA.1624@.TK2MSFTNGP06.phx.gbl...
>