Merge Agent Hangs (reporting no errors)

Hi,
In my merge replication scenario I use Push subscriptions. The
distributor is SQL server 2000 Sp3a, and subscribers are MSDE SP3a.
Everything works fine but recently merge agents started to "hang"
reporting no errors at random phases of replication session. They stop
sending or receiving any data between distributor and subscriber
(there is no network traffic). SQL Profiles show no activity on a
subscriber server. Last message generated by agent is "The process is
running and is waiting for a response from one of the backend
connections".
If there is a problem with communication between servers one would
expect returnig an error code and retrying operation. In my case the
merge agent "hangs" and waits for nothing. After 1 or 2 hours it
reports a communication link failure (waits for a random period of
time).
To continue a Merge process I have to Stop and Start the merge agent
manually.
Since my replication sends a lot of data from subscribers
(MSMerge_genhistory has approx 50000 entries when retention period is
set to 2 days.) after uploading data changes to the publisher (the
procedure sp_MScheckexistsgeneration is executed for every one of
50000 generations - am I right about this?) it takes a long time (40
minutes) to complete. If merge agent hangs, the process must be run
from the beginning. In this situation it is almost impossible to
complete the replication session.
What can be the cause of such behavior of merge agents?
Last lines of output (level 3) generated by agent are as follows:
{call sp_MScheckexistsgeneration (?, ?) }
{call sp_MScheckexistsgeneration (?, ?) }
Percent Complete: 0
The process is running and is waiting for a response from one of the
backend connections.
Repl Agent Status: 3
Tomek
Tomek,
it's difficult to say, but in some sense this looks like a connectivity
issue. However, when I've seen such a situation before, the error was
'General network error'. You might like to verify is this is/is not the
case, by using a Network Monitor tool:
http://support.microsoft.com/default...48942&sd=tech.
Alternatively you could open a window in QA on the publisher, with the
window connected to the subscriber SQL Server. In the window do a simple
select command in a loop and return the date. This should tell you if/when
the connectivity had problems.
Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
if there is any blocking involved. If the problem is not connectivity then
this would be my next test.
I'd also like to know what happens when you restart the agent - does it work
quickly or still take a long time but is successful.
No doubt you have considered this, but if possible can you synchronize more
often to shorten the batchsize?
You might also optimize performance by running
sp_mergemetadataretentioncleanup manually and optimize
he -DownloadGenerationsPerBatch parameter.
HTH,
Paul Ibison
|||Paul,
Thank you for your response. I hope my answers to your questions will
help us to solve the problem.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#EzzqyDQEHA.1312@.TK2MSFTNGP12.phx.gbl>...
> it's difficult to say, but in some sense this looks like a connectivity
> issue. However, when I've seen such a situation before, the error was
> 'General network error'. You might like to verify is this is/is not the
> case, by using a Network Monitor tool:
> http://support.microsoft.com/default...48942&sd=tech.
It can be related to connectivity because merge agents connect to
subscribers that not always use reliable network connection. But the
problem is that instead of reporting General network error and
retrying operation agent waits (hangs) doing nothing.

> Alternatively you could open a window in QA on the publisher, with the
> window connected to the subscriber SQL Server. In the window do a simple
> select command in a loop and return the date. This should tell you if/when
> the connectivity had problems.
QA in loop worked fine (no connectivity problems), but in the same
time agent assigned to the same subscriber hanged.

> Can you check in current activity or sp_who2/sp_lock/sp_blocker to identify
> if there is any blocking involved. If the problem is not connectivity then
> this would be my next test.
I've checked this ne too. Blocking is not the reason.

> I'd also like to know what happens when you restart the agent - does it work
> quickly or still take a long time but is successful.
I will describe how my agent works. Please tell me is it correct
behavior.
1) After connecting to subscriber agent uploads all the data changes
made by subscriber to the publisher. This works very fast and i do not
see any problem here (agent has never stopped at this phase).
2) Merge agent executes sp_MScheckexistsgeneration for each of 50000
generations stored in msmerge_genhistory at subscriber side. If there
is some data to download, it is downloaded to subscriber, and then
merge agent checks next generation.
Now let's assume that merge agent "hanged" ot generation 39000. After
restarting the agent it starts the process from the beginning checking
each of 39000 generations that where checked and merged in previus
session.
The merge agent is succesfull only if it is able to go through 50000
generations without loosing connection to subscriber. That is wy it is
so difficult to complete merge session.
IMHO the merge agent should start from generation 39000 making
completing session possible even on not reliable connections. Is it
"by design" behaviour?

> No doubt you have considered this, but if possible can you synchronize more
> often to shorten the batchsize?
It is not possible. subscribers can not synchronize on saturday and
sunday. So 2 days retention perod is minumum.

> You might also optimize performance by running
> sp_mergemetadataretentioncleanup manually and optimize
> he -DownloadGenerationsPerBatch parameter.
I think the problem is not metadata at publisher but metadata at
subscribers. It is not deleted after succesfull replication session (i
think it must work this way).
DownloadGenerationsPerBatch does not help because most of the time
merge agent executes sp_MScheckexistsgeneration without downloading
any data changes. Or maybe I am misunderstanding this parameter?.
Tomek
|||Tomek,
Slow and unreliable connections can result in more retries by the Merge
Agent, none of which you've seen. Your QA test also indicates the same
thing, so we can cross this off the list.
Answers inline...

> IMHO the merge agent should start from generation 39000 making
> completing session possible even on not reliable connections. Is it
> "by design" behaviour?
>
I guess it is not processed this way to try to avoid potential
non-convergence as inserts and child records could be in separate batches
(http://support.microsoft.com/default...b;enus;Q308266)

>
Having MetadataRetentionCleanup 1 should sort this out, but you could run
manually sp_mergemetadataretentioncleanup on the publisher and subscriber to
see if things are improved. It won't remove the 50000 entries, but what I am
hoping is that the search for new generations will be noticeably quicker
after old ones are removed.

> DownloadGenerationsPerBatch does not help because most of the time
> merge agent executes sp_MScheckexistsgeneration without downloading
> any data changes. Or maybe I am misunderstanding this parameter?.
Increasing the -DownloadGenerationsPerBatch Merge Agent parameters (even to
its maximum value of 2000) is a recommendation where a Subscriber has not
merged data with the Publisher for an extended period time, during which the
Publisher or other Subscribers have made numerous changes
(http://www.microsoft.com/technet/pro...n/mergperf.msp
x). So, I'd definitley test altering this parameter's value.
(Powodzenia)
Paul

Memory Problems in SQL2000 SP3A

Hi,
We get memory problems every so often on our Production SQL2000 SP3A server.
They are described in KB http://support.microsoft.com/kb/903002/en-us We
have made a number of our systems reduce the packet size as the article
suggests. Normally the WARNING: Failed to reserve contiguous memory message
leads to further problems and only a reboot of the server fixes the problem.
Is there something that could be done to fix the situation without shutting
down SQL and rebooting the server? Can we clear some of the buffer cache
with a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
Thanks
ChrisChris,
That KB only talks about one of the many reasons why you can get that error.
This is what was called MemToLeave area of memory and is used for many
things that require contiguous memory space. This memory can get fragmented
from poor behaving things such as XP's and such. One example was earlier
version of SQL LiteSpeed would slowly fragment the MemToLeave area by
leaving blocks allocated and eventually there would not be enough contiguous
space for normal operation. You need to find what the culprit is of coarse
but these kbs might get you started. Look for things like heavy linked
server access, sp_oacreate, xps etc. You can also increase the size of
MemToLeave with the -g startup param which will give you more time between
restarts but wont solve the issue.
http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
http://support.microsoft.com/kb/316749/
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We get memory problems every so often on our Production SQL2000 SP3A
> server. They are described in KB
> http://support.microsoft.com/kb/903002/en-us We have made a number of our
> systems reduce the packet size as the article suggests. Normally the
> WARNING: Failed to reserve contiguous memory message leads to further
> problems and only a reboot of the server fixes the problem. Is there
> something that could be done to fix the situation without shutting down
> SQL and rebooting the server? Can we clear some of the buffer cache with a
> DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
> Thanks
> Chris
>|||Andrew,
Thanks for the info. From what I had read the MemToLeave -g switch appears
as the only solution I had seen. We will be moving from SQL2000 to SQL2005
once SP2 appears so we may not have this situation too much longer. What I
would like confirmed is that a reboot is the only solution once the messages
start to appear or is there anything else that can be done, even tell
everyone to signoff for a few minutes. Would this allow the memory to be
de-allocated so the fragmentation problem would disappear?
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Chris,
> That KB only talks about one of the many reasons why you can get that
> error. This is what was called MemToLeave area of memory and is used for
> many things that require contiguous memory space. This memory can get
> fragmented from poor behaving things such as XP's and such. One example
> was earlier version of SQL LiteSpeed would slowly fragment the MemToLeave
> area by leaving blocks allocated and eventually there would not be enough
> contiguous space for normal operation. You need to find what the culprit
> is of coarse but these kbs might get you started. Look for things like
> heavy linked server access, sp_oacreate, xps etc. You can also increase
> the size of MemToLeave with the -g startup param which will give you more
> time between restarts but wont solve the issue.
>
> http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
> http://support.microsoft.com/kb/316749/
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We get memory problems every so often on our Production SQL2000 SP3A
>> server. They are described in KB
>> http://support.microsoft.com/kb/903002/en-us We have made a number of our
>> systems reduce the packet size as the article suggests. Normally the
>> WARNING: Failed to reserve contiguous memory message leads to further
>> problems and only a reboot of the server fixes the problem. Is there
>> something that could be done to fix the situation without shutting down
>> SQL and rebooting the server? Can we clear some of the buffer cache with
>> a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
>> Thanks
>> Chris
>|||I guess it depends on what is causing the fragmentation but most likely no.
You need to find out what is causing it and fix that.
--
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Thanks for the info. From what I had read the MemToLeave -g switch appears
> as the only solution I had seen. We will be moving from SQL2000 to SQL2005
> once SP2 appears so we may not have this situation too much longer. What I
> would like confirmed is that a reboot is the only solution once the
> messages start to appear or is there anything else that can be done, even
> tell everyone to signoff for a few minutes. Would this allow the memory to
> be de-allocated so the fragmentation problem would disappear?
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Chris,
>> That KB only talks about one of the many reasons why you can get that
>> error. This is what was called MemToLeave area of memory and is used for
>> many things that require contiguous memory space. This memory can get
>> fragmented from poor behaving things such as XP's and such. One example
>> was earlier version of SQL LiteSpeed would slowly fragment the MemToLeave
>> area by leaving blocks allocated and eventually there would not be enough
>> contiguous space for normal operation. You need to find what the culprit
>> is of coarse but these kbs might get you started. Look for things like
>> heavy linked server access, sp_oacreate, xps etc. You can also increase
>> the size of MemToLeave with the -g startup param which will give you more
>> time between restarts but wont solve the issue.
>>
>> http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
>> http://support.microsoft.com/kb/316749/
>> --
>> Andrew J. Kelly SQL MVP
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We get memory problems every so often on our Production SQL2000 SP3A
>> server. They are described in KB
>> http://support.microsoft.com/kb/903002/en-us We have made a number of
>> our systems reduce the packet size as the article suggests. Normally the
>> WARNING: Failed to reserve contiguous memory message leads to further
>> problems and only a reboot of the server fixes the problem. Is there
>> something that could be done to fix the situation without shutting down
>> SQL and rebooting the server? Can we clear some of the buffer cache with
>> a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
>> Thanks
>> Chris
>>
>|||Thanks Andrew.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:emNMuO$OHHA.404@.TK2MSFTNGP02.phx.gbl...
>I guess it depends on what is causing the fragmentation but most likely no.
>You need to find out what is causing it and fix that.
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
>> Andrew,
>> Thanks for the info. From what I had read the MemToLeave -g switch
>> appears as the only solution I had seen. We will be moving from SQL2000
>> to SQL2005 once SP2 appears so we may not have this situation too much
>> longer. What I would like confirmed is that a reboot is the only solution
>> once the messages start to appear or is there anything else that can be
>> done, even tell everyone to signoff for a few minutes. Would this allow
>> the memory to be de-allocated so the fragmentation problem would
>> disappear?
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
>> Chris,
>> That KB only talks about one of the many reasons why you can get that
>> error. This is what was called MemToLeave area of memory and is used for
>> many things that require contiguous memory space. This memory can get
>> fragmented from poor behaving things such as XP's and such. One example
>> was earlier version of SQL LiteSpeed would slowly fragment the
>> MemToLeave area by leaving blocks allocated and eventually there would
>> not be enough contiguous space for normal operation. You need to find
>> what the culprit is of coarse but these kbs might get you started. Look
>> for things like heavy linked server access, sp_oacreate, xps etc. You
>> can also increase the size of MemToLeave with the -g startup param which
>> will give you more time between restarts but wont solve the issue.
>>
>> http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
>> http://support.microsoft.com/kb/316749/
>> --
>> Andrew J. Kelly SQL MVP
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We get memory problems every so often on our Production SQL2000 SP3A
>> server. They are described in KB
>> http://support.microsoft.com/kb/903002/en-us We have made a number of
>> our systems reduce the packet size as the article suggests. Normally
>> the WARNING: Failed to reserve contiguous memory message leads to
>> further problems and only a reboot of the server fixes the problem. Is
>> there something that could be done to fix the situation without
>> shutting down SQL and rebooting the server? Can we clear some of the
>> buffer cache with a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
>> Thanks
>> Chris
>>
>>
>

Memory Problems in SQL2000 SP3A

Hi,
We get memory problems every so often on our Production SQL2000 SP3A server.
They are described in KB http://support.microsoft.com/kb/903002/en-us We
have made a number of our systems reduce the packet size as the article
suggests. Normally the WARNING: Failed to reserve contiguous memory message
leads to further problems and only a reboot of the server fixes the problem.
Is there something that could be done to fix the situation without shutting
down SQL and rebooting the server? Can we clear some of the buffer cache
with a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
Thanks
Chris
Chris,
That KB only talks about one of the many reasons why you can get that error.
This is what was called MemToLeave area of memory and is used for many
things that require contiguous memory space. This memory can get fragmented
from poor behaving things such as XP's and such. One example was earlier
version of SQL LiteSpeed would slowly fragment the MemToLeave area by
leaving blocks allocated and eventually there would not be enough contiguous
space for normal operation. You need to find what the culprit is of coarse
but these kbs might get you started. Look for things like heavy linked
server access, sp_oacreate, xps etc. You can also increase the size of
MemToLeave with the -g startup param which will give you more time between
restarts but wont solve the issue.
http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
http://support.microsoft.com/kb/316749/
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We get memory problems every so often on our Production SQL2000 SP3A
> server. They are described in KB
> http://support.microsoft.com/kb/903002/en-us We have made a number of our
> systems reduce the packet size as the article suggests. Normally the
> WARNING: Failed to reserve contiguous memory message leads to further
> problems and only a reboot of the server fixes the problem. Is there
> something that could be done to fix the situation without shutting down
> SQL and rebooting the server? Can we clear some of the buffer cache with a
> DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
> Thanks
> Chris
>
|||Andrew,
Thanks for the info. From what I had read the MemToLeave -g switch appears
as the only solution I had seen. We will be moving from SQL2000 to SQL2005
once SP2 appears so we may not have this situation too much longer. What I
would like confirmed is that a reboot is the only solution once the messages
start to appear or is there anything else that can be done, even tell
everyone to signoff for a few minutes. Would this allow the memory to be
de-allocated so the fragmentation problem would disappear?
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Chris,
> That KB only talks about one of the many reasons why you can get that
> error. This is what was called MemToLeave area of memory and is used for
> many things that require contiguous memory space. This memory can get
> fragmented from poor behaving things such as XP's and such. One example
> was earlier version of SQL LiteSpeed would slowly fragment the MemToLeave
> area by leaving blocks allocated and eventually there would not be enough
> contiguous space for normal operation. You need to find what the culprit
> is of coarse but these kbs might get you started. Look for things like
> heavy linked server access, sp_oacreate, xps etc. You can also increase
> the size of MemToLeave with the -g startup param which will give you more
> time between restarts but wont solve the issue.
>
> http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
> http://support.microsoft.com/kb/316749/
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
>
|||I guess it depends on what is causing the fragmentation but most likely no.
You need to find out what is causing it and fix that.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Thanks for the info. From what I had read the MemToLeave -g switch appears
> as the only solution I had seen. We will be moving from SQL2000 to SQL2005
> once SP2 appears so we may not have this situation too much longer. What I
> would like confirmed is that a reboot is the only solution once the
> messages start to appear or is there anything else that can be done, even
> tell everyone to signoff for a few minutes. Would this allow the memory to
> be de-allocated so the fragmentation problem would disappear?
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks Andrew.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:emNMuO$OHHA.404@.TK2MSFTNGP02.phx.gbl...
>I guess it depends on what is causing the fragmentation but most likely no.
>You need to find out what is causing it and fix that.
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
>

Memory Problems in SQL2000 SP3A

Hi,
We get memory problems every so often on our Production SQL2000 SP3A server.
They are described in KB http://support.microsoft.com/kb/903002/en-us We
have made a number of our systems reduce the packet size as the article
suggests. Normally the WARNING: Failed to reserve contiguous memory message
leads to further problems and only a reboot of the server fixes the problem.
Is there something that could be done to fix the situation without shutting
down SQL and rebooting the server? Can we clear some of the buffer cache
with a DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
Thanks
ChrisChris,
That KB only talks about one of the many reasons why you can get that error.
This is what was called MemToLeave area of memory and is used for many
things that require contiguous memory space. This memory can get fragmented
from poor behaving things such as XP's and such. One example was earlier
version of SQL LiteSpeed would slowly fragment the MemToLeave area by
leaving blocks allocated and eventually there would not be enough contiguous
space for normal operation. You need to find what the culprit is of coarse
but these kbs might get you started. Look for things like heavy linked
server access, sp_oacreate, xps etc. You can also increase the size of
MemToLeave with the -g startup param which will give you more time between
restarts but wont solve the issue.
http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
http://support.microsoft.com/kb/316749/
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We get memory problems every so often on our Production SQL2000 SP3A
> server. They are described in KB
> http://support.microsoft.com/kb/903002/en-us We have made a number of our
> systems reduce the packet size as the article suggests. Normally the
> WARNING: Failed to reserve contiguous memory message leads to further
> problems and only a reboot of the server fixes the problem. Is there
> something that could be done to fix the situation without shutting down
> SQL and rebooting the server? Can we clear some of the buffer cache with a
> DBCC FREEPROCCACHE or DBCC DROPCLEANBUFFERS?
> Thanks
> Chris
>|||Andrew,
Thanks for the info. From what I had read the MemToLeave -g switch appears
as the only solution I had seen. We will be moving from SQL2000 to SQL2005
once SP2 appears so we may not have this situation too much longer. What I
would like confirmed is that a reboot is the only solution once the messages
start to appear or is there anything else that can be done, even tell
everyone to signoff for a few minutes. Would this allow the memory to be
de-allocated so the fragmentation problem would disappear?
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
> Chris,
> That KB only talks about one of the many reasons why you can get that
> error. This is what was called MemToLeave area of memory and is used for
> many things that require contiguous memory space. This memory can get
> fragmented from poor behaving things such as XP's and such. One example
> was earlier version of SQL LiteSpeed would slowly fragment the MemToLeave
> area by leaving blocks allocated and eventually there would not be enough
> contiguous space for normal operation. You need to find what the culprit
> is of coarse but these kbs might get you started. Look for things like
> heavy linked server access, sp_oacreate, xps etc. You can also increase
> the size of MemToLeave with the -g startup param which will give you more
> time between restarts but wont solve the issue.
>
> http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx
> http://support.microsoft.com/kb/316749/
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:uBdKFLyOHHA.4104@.TK2MSFTNGP06.phx.gbl...
>|||I guess it depends on what is causing the fragmentation but most likely no.
You need to find out what is causing it and fix that.
Andrew J. Kelly SQL MVP
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
> Andrew,
> Thanks for the info. From what I had read the MemToLeave -g switch appears
> as the only solution I had seen. We will be moving from SQL2000 to SQL2005
> once SP2 appears so we may not have this situation too much longer. What I
> would like confirmed is that a reboot is the only solution once the
> messages start to appear or is there anything else that can be done, even
> tell everyone to signoff for a few minutes. Would this allow the memory to
> be de-allocated so the fragmentation problem would disappear?
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ecB78a3OHHA.1252@.TK2MSFTNGP02.phx.gbl...
>|||Thanks Andrew.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:emNMuO$OHHA.404@.TK2MSFTNGP02.phx.gbl...
>I guess it depends on what is causing the fragmentation but most likely no.
>You need to find out what is causing it and fix that.
> --
> Andrew J. Kelly SQL MVP
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:ec47k$9OHHA.4368@.TK2MSFTNGP03.phx.gbl...
>

Memory Leak. SQL Server sp3a, VB and MDAC 2.8

Can anyone give me some sugguestions here.
Connection is declared at the start of the application
Set rsFZReport.ActiveConnection = conn

Then a function repeatly opens recordsets like this -

rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText
..

..
reporting code etc
...

If rsFZReport.State > 0 Then
rsFZReport.Close
Set rsFZReport= Nothing
endif

Using VB Watch debugger, the close and set nothing do NOT release any
memory. It just keeps increasing. The application connects to multiple
databases and many tables, so its uses about 10Mb/minute.
(I don't think its of relavance but Form.show uses memory but form.unload
doesn't release any back)
I've installed SP3a and MDAC 2.8 to no avail. Anyone have any ideas or know
what I can do?

msado27.tlb 2.80.1022.0
MSSQL Server SP3a (3 has a memory leak apparently)
VB6, SP6
Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4
server/workstation (they are both and the same) to this
Sqlsrv32.dll 2000.85.1022.0
doesn't help.
Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;User
ID=uid;Password=thepwd;OPTION=3;connect timeout=240;pete (pete@.madpete.freeserve.co.uk) writes:
> Can anyone give me some sugguestions here.
> Connection is declared at the start of the application
> Set rsFZReport.ActiveConnection = conn
> Then a function repeatly opens recordsets like this -
> rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText
> .
> .
> reporting code etc
> ..
> If rsFZReport.State > 0 Then
> rsFZReport.Close
> Set rsFZReport= Nothing
> endif
>
> Using VB Watch debugger, the close and set nothing do NOT release any
> memory. It just keeps increasing. The application connects to multiple
> databases and many tables, so its uses about 10Mb/minute.

I don't have any experience of tracing memory leaks in Visual Basic,
but I suspect that there is a wee bit too little of information to say
anything.

> MSSQL Server SP3a (3 has a memory leak apparently)

Yes, there was a memory leak in ODBC in SP3, but you are using SQLOLEDB,
so that you should not bother you.

> VB6, SP6
> Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4
> server/workstation (they are both and the same) to this
> Sqlsrv32.dll 2000.85.1022.0
> doesn't help.

Sqlsrv32.dll is ODBC, so again it should not bother you.

> Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;User
> ID=uid;Password=thepwd;OPTION=3;connect timeout=240;

OPTION=3 strikes me as unusual. What does it do?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||">
> OPTION=3 strikes me as unusual. What does it do?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

It was recommended by someone to overcome a problem with varchars not
returning variable length fields. The records are large and the were being
cut off., (only the first few thousand characters returned for a field)
fields were not being returned complete. I havn't actually found any
documentation on this, but it seemed to fix the problem|||pete (pete@.madpete.freeserve.co.uk) writes:
>> OPTION=3 strikes me as unusual. What does it do?
>>
> It was recommended by someone to overcome a problem with varchars not
> returning variable length fields. The records are large and the were being
> cut off., (only the first few thousand characters returned for a field)
> fields were not being returned complete. I havn't actually found any
> documentation on this, but it seemed to fix the problem

I can't find this in the docs either. And I have never heard any problems
with varchar(8000) being truncated.

What happens if you take it out with regards to the memory leak?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95368D79BCD9AYazorman@.127.0.0.1...
> pete (pete@.madpete.freeserve.co.uk) writes:
> >> OPTION=3 strikes me as unusual. What does it do?
> >>
> > It was recommended by someone to overcome a problem with varchars not
> > returning variable length fields. The records are large and the were
being
> > cut off., (only the first few thousand characters returned for a field)
> > fields were not being returned complete. I havn't actually found any
> > documentation on this, but it seemed to fix the problem
> I can't find this in the docs either. And I have never heard any problems
> with varchar(8000) being truncated.
> What happens if you take it out with regards to the memory leak

I didn't make a differnce, but this was the problem

Global rsFZReport as new ADODB.recordset
Which is dumb.
And so is OPTION=3 . I can't find the explanation for it now. I've searched
the MSDN and the web. It might simply be wrong, its used for MySQL though.|||pete (pete@.madpete.freeserve.co.uk) writes:
> I didn't make a differnce, but this was the problem
> Global rsFZReport as new ADODB.recordset
> Which is dumb.

We all do dumb things from time to time.

> And so is OPTION=3 . I can't find the explanation for it now. I've
> searched the MSDN and the web. It might simply be wrong, its used for
> MySQL though.

So I would conclude that Option is specific to the MySQL provider.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp