merge agent error

Hi

We are using HTTPS merge replication.

One of my subscribers is getting this error:

Error messages:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)

This is a bit surprising - it has been working fine - and also there were no changes at the publisher (it only has one article in this publication - a stored proc)

Why would this have happened ? The retention period is 45 days and they synchronised successfully only a few days prior.

thanks

1. How often (frequently) does your app do merge between the publisher and this subscriber? If you had other subscribers and have done merge sync, did the same issue raise?

2. What if you select last_sync_date and metadatacleanuptime from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Thanks for replying.

1. It syncs up every day

2. last_sync_date = 2006-04-28 21:28:58.803
metadatacleanuptime = 2006-05-02 17:37:25.420

We have a number of clients for whom we setup merge replication. For each database we create two publications

1) the first publication has all the data and procs - it is setup with the syncType set to None. Therefore when we initialise the subscription the subscriber already has the data and schema.

2) the second publication only has one proc initially - it is setup with the syncType set to Automatic - we use this publication if we need to add any tables or procs to the database - it obviously has a much smaller snapshot

We have noticed that it is the second publication which is often getting this error - even though the clients are regularly synchronising....

Regards
Bruce

|||

Hi

Any ideas on this ?

Thanks
Bruce

|||

Hi Bruce, it looks like an existing bug in SQL 2005 which is slated to be fixed in SP2. Can you confirm that "select use_partition_groups from sysmergepublications" has value of 0 for both your publications?

Also, how many articles are in the other publication?

|||

Thanks for the response - I'm away for the next 5 days so can't get the answer on use_partition_groups yet...

In the other publication - there are about 600 I s'pose - 100 old tables and 500 procs or more.

Is there any way to stop this happening until sp2 ? We are rolling this out at the moment...

Thanks

|||One last question - you mentioned the subscribers sync daily, yet there's four days difference between last sync time and the metadata cleanup time. Do you know what happened between 4/28 and 5/2? Did the agent not sync?|||

Hi

It syncs every day if the pc is on ! This client turns it off on the weekend.

Yes - use_partition_groups is set to 0 for both publications

In case it matters the subscriptions are setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

As there is only one proc in the second publication at the moment (and it is a trivial proc) I can reinitialise it and I guess this will be a workaround for now - but as soon as I need to add tables to this for future releases I will not want to be doing this. Is it a matter of hanging out until SP2 for a fix for this ? I guess that will be 3/4 months away or worse.

One more question about reinitialising subscriptions. When marking the publication for reinitialization from the publisher, I right-click on the publication name and have to select 'reinitialize all subscriptions' (I'm guessing that because the subscriber type is anonymous I can't select the individual subscriber if there are multiple subscribers to this publication), I get a message box asking me to confirm and whether to use the current snapshot or to create a new one. What is most disconcerting is that it doesn't specify which publication it is doing this for. Just for my own sanity, can you confirm it is only doing this for the publication you are selecting, and not all of them !!!! Paranoid I know but it's always better to ask I think..

Thanks
Bruce

|||

hi Greg

Any further update on a fix for this ?

Thanks
Bruce

|||

Bruce,

You can wait for Service pack 2 for the fix.

As a workaround, what you can do is increase the retention period of the publications. That way, the possibility of hitting the bug is lesser.

As per your question of reintializing the publication, all subscriptions to the publication you right clicked (and chose reinitialize) will be renitialized. No other publication will be affected.

|||

FYI,

We are having the same issue and are eagerly awaiting SP2 or a hotfix.

|||

We are having a similar issue but it is the other way round.

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).

This has started to happen only after we have set-up a second publication. We have set the retention period to 5 days on both the publications. We replicate data every 3 minutes and hence 5 days is a long time for data not to replicate.

Any ideas as to why we are getting this problem? Is this a known issue?

|||

I posted a visual guide to addressing this issue at: http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/08/13/128.aspx.

:{> Andy

|||We have also run into this error, and have used the work around of extending our expiration out to 999 days.

I am wondering if we will be running into performance issues soon because the metadata will not be cleaned up often enough. Will the tables get extrememly large? Is there a way to do the cleanup manually if performance suffers enough?

Is SP2 close to being released?

thanks,
jg

|||

Do subscribers and publishers both need to be upgraded to SP2a for this to be fixed

We upgraded the publisher, and this is still happening.

I'll see if it happens to any of the subscribers after we upgrade them to sp2a.

Bruce

merge agent error

Hi

We are using HTTPS merge replication.

One of my subscribers is getting this error:

Error messages:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)

This is a bit surprising - it has been working fine - and also there were no changes at the publisher (it only has one article in this publication - a stored proc)

Why would this have happened ? The retention period is 45 days and they synchronised successfully only a few days prior.

thanks

1. How often (frequently) does your app do merge between the publisher and this subscriber? If you had other subscribers and have done merge sync, did the same issue raise?

2. What if you select last_sync_date and metadatacleanuptime from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Thanks for replying.

1. It syncs up every day

2. last_sync_date = 2006-04-28 21:28:58.803
metadatacleanuptime = 2006-05-02 17:37:25.420

We have a number of clients for whom we setup merge replication. For each database we create two publications

1) the first publication has all the data and procs - it is setup with the syncType set to None. Therefore when we initialise the subscription the subscriber already has the data and schema.

2) the second publication only has one proc initially - it is setup with the syncType set to Automatic - we use this publication if we need to add any tables or procs to the database - it obviously has a much smaller snapshot

We have noticed that it is the second publication which is often getting this error - even though the clients are regularly synchronising....

Regards
Bruce

|||

Hi

Any ideas on this ?

Thanks
Bruce

|||

Hi Bruce, it looks like an existing bug in SQL 2005 which is slated to be fixed in SP2. Can you confirm that "select use_partition_groups from sysmergepublications" has value of 0 for both your publications?

Also, how many articles are in the other publication?

|||

Thanks for the response - I'm away for the next 5 days so can't get the answer on use_partition_groups yet...

In the other publication - there are about 600 I s'pose - 100 old tables and 500 procs or more.

Is there any way to stop this happening until sp2 ? We are rolling this out at the moment...

Thanks

|||One last question - you mentioned the subscribers sync daily, yet there's four days difference between last sync time and the metadata cleanup time. Do you know what happened between 4/28 and 5/2? Did the agent not sync?|||

Hi

It syncs every day if the pc is on ! This client turns it off on the weekend.

Yes - use_partition_groups is set to 0 for both publications

In case it matters the subscriptions are setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

As there is only one proc in the second publication at the moment (and it is a trivial proc) I can reinitialise it and I guess this will be a workaround for now - but as soon as I need to add tables to this for future releases I will not want to be doing this. Is it a matter of hanging out until SP2 for a fix for this ? I guess that will be 3/4 months away or worse.

One more question about reinitialising subscriptions. When marking the publication for reinitialization from the publisher, I right-click on the publication name and have to select 'reinitialize all subscriptions' (I'm guessing that because the subscriber type is anonymous I can't select the individual subscriber if there are multiple subscribers to this publication), I get a message box asking me to confirm and whether to use the current snapshot or to create a new one. What is most disconcerting is that it doesn't specify which publication it is doing this for. Just for my own sanity, can you confirm it is only doing this for the publication you are selecting, and not all of them !!!! Paranoid I know but it's always better to ask I think..

Thanks
Bruce

|||

hi Greg

Any further update on a fix for this ?

Thanks
Bruce

|||

Bruce,

You can wait for Service pack 2 for the fix.

As a workaround, what you can do is increase the retention period of the publications. That way, the possibility of hitting the bug is lesser.

As per your question of reintializing the publication, all subscriptions to the publication you right clicked (and chose reinitialize) will be renitialized. No other publication will be affected.

|||

FYI,

We are having the same issue and are eagerly awaiting SP2 or a hotfix.

|||

We are having a similar issue but it is the other way round.

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).

This has started to happen only after we have set-up a second publication. We have set the retention period to 5 days on both the publications. We replicate data every 3 minutes and hence 5 days is a long time for data not to replicate.

Any ideas as to why we are getting this problem? Is this a known issue?

|||

I posted a visual guide to addressing this issue at: http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/08/13/128.aspx.

:{> Andy

|||We have also run into this error, and have used the work around of extending our expiration out to 999 days.

I am wondering if we will be running into performance issues soon because the metadata will not be cleaned up often enough. Will the tables get extrememly large? Is there a way to do the cleanup manually if performance suffers enough?

Is SP2 close to being released?

thanks,
jg

|||

Do subscribers and publishers both need to be upgraded to SP2a for this to be fixed

We upgraded the publisher, and this is still happening.

I'll see if it happens to any of the subscribers after we upgrade them to sp2a.

Bruce

merge agent error

Hi

We are using HTTPS merge replication.

One of my subscribers is getting this error:

Error messages:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)

This is a bit surprising - it has been working fine - and also there were no changes at the publisher (it only has one article in this publication - a stored proc)

Why would this have happened ? The retention period is 45 days and they synchronised successfully only a few days prior.

thanks

1. How often (frequently) does your app do merge between the publisher and this subscriber? If you had other subscribers and have done merge sync, did the same issue raise?

2. What if you select last_sync_date and metadatacleanuptime from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Thanks for replying.

1. It syncs up every day

2. last_sync_date = 2006-04-28 21:28:58.803
metadatacleanuptime = 2006-05-02 17:37:25.420

We have a number of clients for whom we setup merge replication. For each database we create two publications

1) the first publication has all the data and procs - it is setup with the syncType set to None. Therefore when we initialise the subscription the subscriber already has the data and schema.

2) the second publication only has one proc initially - it is setup with the syncType set to Automatic - we use this publication if we need to add any tables or procs to the database - it obviously has a much smaller snapshot

We have noticed that it is the second publication which is often getting this error - even though the clients are regularly synchronising....

Regards
Bruce

|||

Hi

Any ideas on this ?

Thanks
Bruce

|||

Hi Bruce, it looks like an existing bug in SQL 2005 which is slated to be fixed in SP2. Can you confirm that "select use_partition_groups from sysmergepublications" has value of 0 for both your publications?

Also, how many articles are in the other publication?

|||

Thanks for the response - I'm away for the next 5 days so can't get the answer on use_partition_groups yet...

In the other publication - there are about 600 I s'pose - 100 old tables and 500 procs or more.

Is there any way to stop this happening until sp2 ? We are rolling this out at the moment...

Thanks

|||One last question - you mentioned the subscribers sync daily, yet there's four days difference between last sync time and the metadata cleanup time. Do you know what happened between 4/28 and 5/2? Did the agent not sync?|||

Hi

It syncs every day if the pc is on ! This client turns it off on the weekend.

Yes - use_partition_groups is set to 0 for both publications

In case it matters the subscriptions are setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

As there is only one proc in the second publication at the moment (and it is a trivial proc) I can reinitialise it and I guess this will be a workaround for now - but as soon as I need to add tables to this for future releases I will not want to be doing this. Is it a matter of hanging out until SP2 for a fix for this ? I guess that will be 3/4 months away or worse.

One more question about reinitialising subscriptions. When marking the publication for reinitialization from the publisher, I right-click on the publication name and have to select 'reinitialize all subscriptions' (I'm guessing that because the subscriber type is anonymous I can't select the individual subscriber if there are multiple subscribers to this publication), I get a message box asking me to confirm and whether to use the current snapshot or to create a new one. What is most disconcerting is that it doesn't specify which publication it is doing this for. Just for my own sanity, can you confirm it is only doing this for the publication you are selecting, and not all of them !!!! Paranoid I know but it's always better to ask I think..

Thanks
Bruce

|||

hi Greg

Any further update on a fix for this ?

Thanks
Bruce

|||

Bruce,

You can wait for Service pack 2 for the fix.

As a workaround, what you can do is increase the retention period of the publications. That way, the possibility of hitting the bug is lesser.

As per your question of reintializing the publication, all subscriptions to the publication you right clicked (and chose reinitialize) will be renitialized. No other publication will be affected.

|||

FYI,

We are having the same issue and are eagerly awaiting SP2 or a hotfix.

|||

We are having a similar issue but it is the other way round.

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).

This has started to happen only after we have set-up a second publication. We have set the retention period to 5 days on both the publications. We replicate data every 3 minutes and hence 5 days is a long time for data not to replicate.

Any ideas as to why we are getting this problem? Is this a known issue?

|||

I posted a visual guide to addressing this issue at: http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/08/13/128.aspx.

:{> Andy

|||We have also run into this error, and have used the work around of extending our expiration out to 999 days.

I am wondering if we will be running into performance issues soon because the metadata will not be cleaned up often enough. Will the tables get extrememly large? Is there a way to do the cleanup manually if performance suffers enough?

Is SP2 close to being released?

thanks,
jg

|||

Do subscribers and publishers both need to be upgraded to SP2a for this to be fixed

We upgraded the publisher, and this is still happening.

I'll see if it happens to any of the subscribers after we upgrade them to sp2a.

Bruce

merge agent error

Hi

We are using HTTPS merge replication.

One of my subscribers is getting this error:

Error messages:

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Publisher for changes not yet sent to the Subscriber. You must reinitialize the subscription (without upload). (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199402)

This is a bit surprising - it has been working fine - and also there were no changes at the publisher (it only has one article in this publication - a stored proc)

Why would this have happened ? The retention period is 45 days and they synchronised successfully only a few days prior.

thanks

1. How often (frequently) does your app do merge between the publisher and this subscriber? If you had other subscribers and have done merge sync, did the same issue raise?

2. What if you select last_sync_date and metadatacleanuptime from sysmergesubscriptions on the publication database?

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights

|||

Thanks for replying.

1. It syncs up every day

2. last_sync_date = 2006-04-28 21:28:58.803
metadatacleanuptime = 2006-05-02 17:37:25.420

We have a number of clients for whom we setup merge replication. For each database we create two publications

1) the first publication has all the data and procs - it is setup with the syncType set to None. Therefore when we initialise the subscription the subscriber already has the data and schema.

2) the second publication only has one proc initially - it is setup with the syncType set to Automatic - we use this publication if we need to add any tables or procs to the database - it obviously has a much smaller snapshot

We have noticed that it is the second publication which is often getting this error - even though the clients are regularly synchronising....

Regards
Bruce

|||

Hi

Any ideas on this ?

Thanks
Bruce

|||

Hi Bruce, it looks like an existing bug in SQL 2005 which is slated to be fixed in SP2. Can you confirm that "select use_partition_groups from sysmergepublications" has value of 0 for both your publications?

Also, how many articles are in the other publication?

|||

Thanks for the response - I'm away for the next 5 days so can't get the answer on use_partition_groups yet...

In the other publication - there are about 600 I s'pose - 100 old tables and 500 procs or more.

Is there any way to stop this happening until sp2 ? We are rolling this out at the moment...

Thanks

|||One last question - you mentioned the subscribers sync daily, yet there's four days difference between last sync time and the metadata cleanup time. Do you know what happened between 4/28 and 5/2? Did the agent not sync?|||

Hi

It syncs every day if the pc is on ! This client turns it off on the weekend.

Yes - use_partition_groups is set to 0 for both publications

In case it matters the subscriptions are setup as follows:

subscription.CreateSyncAgentByDefault = False -- I wrote a windows service to synchronise
subscription.UseWebSynchronization = True
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous

As there is only one proc in the second publication at the moment (and it is a trivial proc) I can reinitialise it and I guess this will be a workaround for now - but as soon as I need to add tables to this for future releases I will not want to be doing this. Is it a matter of hanging out until SP2 for a fix for this ? I guess that will be 3/4 months away or worse.

One more question about reinitialising subscriptions. When marking the publication for reinitialization from the publisher, I right-click on the publication name and have to select 'reinitialize all subscriptions' (I'm guessing that because the subscriber type is anonymous I can't select the individual subscriber if there are multiple subscribers to this publication), I get a message box asking me to confirm and whether to use the current snapshot or to create a new one. What is most disconcerting is that it doesn't specify which publication it is doing this for. Just for my own sanity, can you confirm it is only doing this for the publication you are selecting, and not all of them !!!! Paranoid I know but it's always better to ask I think..

Thanks
Bruce

|||

hi Greg

Any further update on a fix for this ?

Thanks
Bruce

|||

Bruce,

You can wait for Service pack 2 for the fix.

As a workaround, what you can do is increase the retention period of the publications. That way, the possibility of hitting the bug is lesser.

As per your question of reintializing the publication, all subscriptions to the publication you right clicked (and chose reinitialize) will be renitialized. No other publication will be affected.

|||

FYI,

We are having the same issue and are eagerly awaiting SP2 or a hotfix.

|||

We are having a similar issue but it is the other way round.

The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).

This has started to happen only after we have set-up a second publication. We have set the retention period to 5 days on both the publications. We replicate data every 3 minutes and hence 5 days is a long time for data not to replicate.

Any ideas as to why we are getting this problem? Is this a known issue?

|||

I posted a visual guide to addressing this issue at: http://www.vsteamsystemcentral.com/cs/blogs/applied_team_system/archive/2006/08/13/128.aspx.

:{> Andy

|||We have also run into this error, and have used the work around of extending our expiration out to 999 days.

I am wondering if we will be running into performance issues soon because the metadata will not be cleaned up often enough. Will the tables get extrememly large? Is there a way to do the cleanup manually if performance suffers enough?

Is SP2 close to being released?

thanks,
jg

|||

Do subscribers and publishers both need to be upgraded to SP2a for this to be fixed

We upgraded the publisher, and this is still happening.

I'll see if it happens to any of the subscribers after we upgrade them to sp2a.

Bruce

MemToLeave and Thirdy Pary DLLs

Hi
I've been running into problems with a server that indicate that space in the
MemToLeave area is exhausted. The messages in the error log are along the
lines of 'Failed to reserve contigous memory', 'Clearing procedure cache to
free contigous memory' et cetera.
We do have a third party DLL in use and I've seen in a previous post a
recommendation to move third party DLLs out of the production server's
address space. The question is how to do this? My NT administrators don't
know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I can
do once a problem has occurred, but is there a way of ensuring the DLL is
always out of the server's address space?
If I unload the DLL what is to stop it meing reloaded, as it is used by
database monitoring software that runs every two minutes?
I'm also a little confused by the -g startup setting.
One post I've seen says to set it to 384 to increase the space available to
the MEmToLeave. Another post states that you need 128M for the worker threads
and using the default -g will add another 256M, bringing the default to 384M.
BOL is woolly on the subject, as normal.
Thanks
Mark
Message posted via http://www.droptable.com
Hi Mark
Some .dll's are designed to be run either in-process or ex-process & some
are simply designed to run in-process, so some can be forced to run
ex-process & some can't.
The type that typically *can* be run ex-process from SQL Server are COM type
..dlls, typically developed in VB6.0 (but can also be developed in Delphi,
C++ or J++0). you know you're dealing with one of these if the calling code
uses the sp_OACreate syntax. To make these guys run ex-process, you
configure them to run within COM+ or (or MTS on older WinNT) context, which
requires registering them in COM+.
The type that typcially *cannot* be run ex-process from SQL Server are
Extended Stored procedures, which are typically written in C and simply
aren't designed to be run ex-process. So if you're talking about a .dll
which is registered as an Extended Stored Proc, you probably won't be able
to make it run ex-process.
The post about the -g switch is correct. SQL Server's allocation of memory
during startup is best documented in Ken Henderson's Internals Guru's Guide
book. He's also written articles on SQL Memory Management, one of which is
here:
http://msdn.microsoft.com/data/defau...v_01262004.asp
But I dono't think this one specifically covers the -g switch in the detail
you're after. Try to get hold of the book, as it'd definitely covered in
detail in its Memory chapter.
HTH
Regards,
Greg Linwood
SQL Server MVP
"mpenfold via droptable.com" <u16092@.uwe> wrote in message
news:58090a9e28698@.uwe...
> Hi
> I've been running into problems with a server that indicate that space in
> the
> MemToLeave area is exhausted. The messages in the error log are along the
> lines of 'Failed to reserve contigous memory', 'Clearing procedure cache
> to
> free contigous memory' et cetera.
> We do have a third party DLL in use and I've seen in a previous post a
> recommendation to move third party DLLs out of the production server's
> address space. The question is how to do this? My NT administrators don't
> know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I
> can
> do once a problem has occurred, but is there a way of ensuring the DLL is
> always out of the server's address space?
> If I unload the DLL what is to stop it meing reloaded, as it is used by
> database monitoring software that runs every two minutes?
> I'm also a little confused by the -g startup setting.
> One post I've seen says to set it to 384 to increase the space available
> to
> the MEmToLeave. Another post states that you need 128M for the worker
> threads
> and using the default -g will add another 256M, bringing the default to
> 384M.
> BOL is woolly on the subject, as normal.
> Thanks
> Mark
> --
> Message posted via http://www.droptable.com

MemToLeave and Thirdy Pary DLLs

Hi
I've been running into problems with a server that indicate that space in the
MemToLeave area is exhausted. The messages in the error log are along the
lines of 'Failed to reserve contigous memory', 'Clearing procedure cache to
free contigous memory' et cetera.
We do have a third party DLL in use and I've seen in a previous post a
recommendation to move third party DLLs out of the production server's
address space. The question is how to do this? My NT administrators don't
know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I can
do once a problem has occurred, but is there a way of ensuring the DLL is
always out of the server's address space?
If I unload the DLL what is to stop it meing reloaded, as it is used by
database monitoring software that runs every two minutes?
I'm also a little confused by the -g startup setting.
One post I've seen says to set it to 384 to increase the space available to
the MEmToLeave. Another post states that you need 128M for the worker threads
and using the default -g will add another 256M, bringing the default to 384M.
BOL is woolly on the subject, as normal.
Thanks
Mark
--
Message posted via http://www.sqlmonster.comHi Mark
Some .dll's are designed to be run either in-process or ex-process & some
are simply designed to run in-process, so some can be forced to run
ex-process & some can't.
The type that typically *can* be run ex-process from SQL Server are COM type
.dlls, typically developed in VB6.0 (but can also be developed in Delphi,
C++ or J++0). you know you're dealing with one of these if the calling code
uses the sp_OACreate syntax. To make these guys run ex-process, you
configure them to run within COM+ or (or MTS on older WinNT) context, which
requires registering them in COM+.
The type that typcially *cannot* be run ex-process from SQL Server are
Extended Stored procedures, which are typically written in C and simply
aren't designed to be run ex-process. So if you're talking about a .dll
which is registered as an Extended Stored Proc, you probably won't be able
to make it run ex-process.
The post about the -g switch is correct. SQL Server's allocation of memory
during startup is best documented in Ken Henderson's Internals Guru's Guide
book. He's also written articles on SQL Memory Management, one of which is
here:
http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
But I dono't think this one specifically covers the -g switch in the detail
you're after. Try to get hold of the book, as it'd definitely covered in
detail in its Memory chapter.
HTH
Regards,
Greg Linwood
SQL Server MVP
"mpenfold via SQLMonster.com" <u16092@.uwe> wrote in message
news:58090a9e28698@.uwe...
> Hi
> I've been running into problems with a server that indicate that space in
> the
> MemToLeave area is exhausted. The messages in the error log are along the
> lines of 'Failed to reserve contigous memory', 'Clearing procedure cache
> to
> free contigous memory' et cetera.
> We do have a third party DLL in use and I've seen in a previous post a
> recommendation to move third party DLLs out of the production server's
> address space. The question is how to do this? My NT administrators don't
> know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I
> can
> do once a problem has occurred, but is there a way of ensuring the DLL is
> always out of the server's address space?
> If I unload the DLL what is to stop it meing reloaded, as it is used by
> database monitoring software that runs every two minutes?
> I'm also a little confused by the -g startup setting.
> One post I've seen says to set it to 384 to increase the space available
> to
> the MEmToLeave. Another post states that you need 128M for the worker
> threads
> and using the default -g will add another 256M, bringing the default to
> 384M.
> BOL is woolly on the subject, as normal.
> Thanks
> Mark
> --
> Message posted via http://www.sqlmonster.comsql

MemToLeave and Thirdy Pary DLLs

Hi
I've been running into problems with a server that indicate that space in th
e
MemToLeave area is exhausted. The messages in the error log are along the
lines of 'Failed to reserve contigous memory', 'Clearing procedure cache to
free contigous memory' et cetera.
We do have a third party DLL in use and I've seen in a previous post a
recommendation to move third party DLLs out of the production server's
address space. The question is how to do this? My NT administrators don't
know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I can
do once a problem has occurred, but is there a way of ensuring the DLL is
always out of the server's address space?
If I unload the DLL what is to stop it meing reloaded, as it is used by
database monitoring software that runs every two minutes?
I'm also a little confused by the -g startup setting.
One post I've seen says to set it to 384 to increase the space available to
the MEmToLeave. Another post states that you need 128M for the worker thread
s
and using the default -g will add another 256M, bringing the default to 384M
.
BOL is woolly on the subject, as normal.
Thanks
Mark
Message posted via http://www.droptable.comHi Mark
Some .dll's are designed to be run either in-process or ex-process & some
are simply designed to run in-process, so some can be forced to run
ex-process & some can't.
The type that typically *can* be run ex-process from SQL Server are COM type
.dlls, typically developed in VB6.0 (but can also be developed in Delphi,
C++ or J++0). you know you're dealing with one of these if the calling code
uses the sp_OACreate syntax. To make these guys run ex-process, you
configure them to run within COM+ or (or MTS on older WinNT) context, which
requires registering them in COM+.
The type that typcially *cannot* be run ex-process from SQL Server are
Extended Stored procedures, which are typically written in C and simply
aren't designed to be run ex-process. So if you're talking about a .dll
which is registered as an Extended Stored Proc, you probably won't be able
to make it run ex-process.
The post about the -g switch is correct. SQL Server's allocation of memory
during startup is best documented in Ken Henderson's Internals Guru's Guide
book. He's also written articles on SQL Memory Management, one of which is
here:
http://msdn.microsoft.com/data/defa...ev_01262004.asp
But I dono't think this one specifically covers the -g switch in the detail
you're after. Try to get hold of the book, as it'd definitely covered in
detail in its Memory chapter.
HTH
Regards,
Greg Linwood
SQL Server MVP
"mpenfold via droptable.com" <u16092@.uwe> wrote in message
news:58090a9e28698@.uwe...
> Hi
> I've been running into problems with a server that indicate that space in
> the
> MemToLeave area is exhausted. The messages in the error log are along the
> lines of 'Failed to reserve contigous memory', 'Clearing procedure cache
> to
> free contigous memory' et cetera.
> We do have a third party DLL in use and I've seen in a previous post a
> recommendation to move third party DLLs out of the production server's
> address space. The question is how to do this? My NT administrators don't
> know. BOL tells me how to unload the DLL (DBCC dllname (FREE)), which I
> can
> do once a problem has occurred, but is there a way of ensuring the DLL is
> always out of the server's address space?
> If I unload the DLL what is to stop it meing reloaded, as it is used by
> database monitoring software that runs every two minutes?
> I'm also a little confused by the -g startup setting.
> One post I've seen says to set it to 384 to increase the space available
> to
> the MEmToLeave. Another post states that you need 128M for the worker
> threads
> and using the default -g will add another 256M, bringing the default to
> 384M.
> BOL is woolly on the subject, as normal.
> Thanks
> Mark
> --
> Message posted via http://www.droptable.com

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/de...v_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.
Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:

> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/de...v_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsof t SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:
> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>

Memory Pressure in MemtoLeave

Starting in early 2004 our active\active SQL Server cluster started
experiencing intermittent slow downs, mini-dumps, and error messages in
the client and SQL logs (see below). These symptoms would build until
the system became non-responsive. The final symptom was login failures
for connections using Windows authentication. Based on the error
messages and that a restart\failover brought the server back online we
were sure the problem was memory pressure.
The first few times the memory pressure happened we applied standard
troubleshooting. All the latest patches were applied, called PSS, code
was reviewed, questionable processes and troublesome jobs were reviewed
and cleaned up. Three or four times we were confident that we found
the likely cause. A few weeks later the problem would re-occur.
After six months and out of answers we bought Essential Support from
Microsoft and opened a case The level of support and knowledge is much
higher than PSS. Working with Essential Support they confirmed our
in-house diagnosis of memory pressure in the MemToLeave area of the SQL
Server memory space. For more information on MemToLeave find Ken
Henderson's article on MSDN.
It took memory dumps and diagnostics after three incidents before
Microsoft found the problem. There is memory leak in SQL Server and
how it handles disconnects in remote queries across linked servers.
The scenario in my shop is like this: an app server starts a request
on behalf of a remote client. The request starts a remote query on
SQL Server A that reaches across to SQL Server B. Our app server has
aggressive retry logic (long story) so it terminates the original
process if it has waited too long. SQL Server A handles the disconnect
fine but SQL Server B doesn't. It has no place to send the data it
has gathered so that memory allocation on Server B leaks. It is not
de-allocated until the next time SQL Server restarts.
MemToLeave and the processes that run there want large chunks of
contiguous memory. These leaked sections of memory eventually caused
MemToLeave to become fragmented; leading to the infamous unable to
reserve memory message in my SQL Server log.
I'm guessing that someone will post a reply saying SQL Server
doesn't leak; it is our app server etc. Well, SQL Server does have a
leak; a hotfix and KB article will be coming to a SQL Server near you
soon.
Here is what I would recommend for troubleshooting this process if you
get any of the errors or symptoms I've listed:
1. Read Ken Henderson's article on MemToLeave so you understand this
poorly documented part of SQL Server.
http://msdn.microsoft.com/library/d...ev_01262004.asp
2. Set the -G switch in your startup parameter to 384 megs. This
will increase MemtoLeave but reduce the amount of memory available to
the buffer pool.
3. Look for any memory leaks in your own code. Review all use of
Sp_OA, third party or in-house extended stored procedures etc.
Remember every byte counts!
4. Try to reduce your linked server traffic or identify bottlenecks in
your remote queries.
5. Bypass PSS and spend the money for Essential Support.
6. If you suspect your problem is like mine changing this registry
setting. It is the workaround that we are using until the hotfix is
released.
& #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL Server\your
instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
Error messages related to problem:
***Unable to get thread context for spid 56
2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
dbghelp.dll - Not enough storage is available to process this command.
2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
administrators may connect at this time.
2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2004-08-12 14:57:04.98 server SQL Server could not spawn
process_loginread thread.
THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
INFORMATION AND ADVICE AT YOUR OWN RISK.Thanks for the informative post, it might save us some pain.
Did you consider setting the linked server "allow in-process" switch to
false so that the allocations are made out-of-process which would mean I
guess in the buffer pool instead of the MemToLeave?
It wouldn't solve a memory leak, but if the queries use up the memory in the
MemToLeave area, wouldn't this cause the same problems you have.
Did they advise that out-of-process would be much slower.
thanks,
"dosberg@.yahoo.com" wrote:

> Starting in early 2004 our active\active SQL Server cluster started
> experiencing intermittent slow downs, mini-dumps, and error messages in
> the client and SQL logs (see below). These symptoms would build until
> the system became non-responsive. The final symptom was login failures
> for connections using Windows authentication. Based on the error
> messages and that a restart\failover brought the server back online we
> were sure the problem was memory pressure.
> The first few times the memory pressure happened we applied standard
> troubleshooting. All the latest patches were applied, called PSS, code
> was reviewed, questionable processes and troublesome jobs were reviewed
> and cleaned up. Three or four times we were confident that we found
> the likely cause. A few weeks later the problem would re-occur.
> After six months and out of answers we bought Essential Support from
> Microsoft and opened a case The level of support and knowledge is much
> higher than PSS. Working with Essential Support they confirmed our
> in-house diagnosis of memory pressure in the MemToLeave area of the SQL
> Server memory space. For more information on MemToLeave find Ken
> Henderson's article on MSDN.
> It took memory dumps and diagnostics after three incidents before
> Microsoft found the problem. There is memory leak in SQL Server and
> how it handles disconnects in remote queries across linked servers.
> The scenario in my shop is like this: an app server starts a request
> on behalf of a remote client. The request starts a remote query on
> SQL Server A that reaches across to SQL Server B. Our app server has
> aggressive retry logic (long story) so it terminates the original
> process if it has waited too long. SQL Server A handles the disconnect
> fine but SQL Server B doesn't. It has no place to send the data it
> has gathered so that memory allocation on Server B leaks. It is not
> de-allocated until the next time SQL Server restarts.
> MemToLeave and the processes that run there want large chunks of
> contiguous memory. These leaked sections of memory eventually caused
> MemToLeave to become fragmented; leading to the infamous unable to
> reserve memory message in my SQL Server log.
> I'm guessing that someone will post a reply saying SQL Server
> doesn't leak; it is our app server etc. Well, SQL Server does have a
> leak; a hotfix and KB article will be coming to a SQL Server near you
> soon.
> Here is what I would recommend for troubleshooting this process if you
> get any of the errors or symptoms I've listed:
> 1. Read Ken Henderson's article on MemToLeave so you understand this
> poorly documented part of SQL Server.
> http://msdn.microsoft.com/library/d...ev_01262004.asp
> 2. Set the -G switch in your startup parameter to 384 megs. This
> will increase MemtoLeave but reduce the amount of memory available to
> the buffer pool.
> 3. Look for any memory leaks in your own code. Review all use of
> Sp_OA, third party or in-house extended stored procedures etc.
> Remember every byte counts!
> 4. Try to reduce your linked server traffic or identify bottlenecks in
> your remote queries.
> 5. Bypass PSS and spend the money for Essential Support.
> 6. If you suspect your problem is like mine changing this registry
> setting. It is the workaround that we are using until the hotfix is
> released.
> & #91;HKEY_LOCAL_MACHINE\SOFTWARE\Microsof
t\Microsoft SQL Server\your
> instance name\Providers\SQLOLEDB]"AllowInProcess"=dword:00000000
>
> Error messages related to problem:
> ***Unable to get thread context for spid 56
> 2004-08-13 08:18:04.06 server CImageHelper::Init () Failed load of
> dbghelp.dll - Not enough storage is available to process this command.
> 2004-08-13 08:18:04.44 logon Login failed for user 'User1'. Only
> administrators may connect at this time.
> 2004-08-12 14:24:02.09 spid134 WARNING: Failed to reserve contiguous
> memory of Size= 131072.
> 2004-08-12 14:57:04.98 server SQL Server could not spawn
> process_loginread thread.
>
> THIS POSTING IS PROVIDED WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED
> OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
> MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. USE THE
> INFORMATION AND ADVICE AT YOUR OWN RISK.
>