Hello everybody,
i need to set a MSDE database as 'publisher' for merge replication.
My difficult is that I haven't understand how I cand do it.
I have not Enterprise Manager for set the merge, How I can Do it ?
Thank you so much,
You could create a merge publication on your normal server and get EM to
script it out. This would be in the form of a series of system stored
procedure calls. After changing the servername, you should have a script
that could be run on MSDE.
Alternatively, you could create it using SQLDMO. I don't have an example of
merge, but I do have 2 examples of transactional on
www.replicationanswers.com.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ha scritto nel messaggio news:%23eBzl8WAFHA.2016@.TK2MSFTNGP15.phx.gbl...
Thank you for your answer,
but i need a tool that make it for me.
There are any open-source/free software that i can use ?
thank you!
ck1;
|||I had a browse of the 3rd party tools for managing MSDE,
but the 3 I looked at didn't list replication
administration. Scripting out a subscriuption is not
difficult, but I can appreciate the functionality you are
looking for and I'll also keep an eye on this thread to
see if anyone else has such a GUI tool.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||What language? I have a library (VB6 and .NET versions) to set up merge
replication programmatically that's been used in a couple of client projects
successfully..
GUI can be put on it relatively easily.
Paul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:26e001c502c7$94d45ce0$a401280a@.phx.gbl...
> I had a browse of the 3rd party tools for managing MSDE,
> but the 3 I looked at didn't list replication
> administration. Scripting out a subscriuption is not
> difficult, but I can appreciate the functionality you are
> looking for and I'll also keep an eye on this thread to
> see if anyone else has such a GUI tool.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
Showing posts with label msde. Show all posts
Showing posts with label msde. Show all posts
Friday, March 30, 2012
Merge Module with Verbose Logging?
Is this possible?
When troubleshooting MSDE install issues in the field, verbose logging
would be nice. But as far as I can see, it's only available for the
standalone install (/L*v), and not the merge module.
I should probably add that my install is an Installshield-based
compressed setup.exe.
When troubleshooting MSDE install issues in the field, verbose logging
would be nice. But as far as I can see, it's only available for the
standalone install (/L*v), and not the merge module.
I should probably add that my install is an Installshield-based
compressed setup.exe.
Monday, March 26, 2012
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
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
Friday, March 23, 2012
Merge ActiveX component removes subscriber records
Hi,
I am trying to synchronize data between a publisher running in SQL Server 2000 enterprise edition and a subscriber running in MSDE 2000 using DMO with Merge ActiveX component. My program is in VB.NET.
Now, everytime I try to synchronize data, the changes at subscriber don't get reflected at publisher. Here is the code segment:
objMerge.ReinitializeSubscription(true)
objMerge.Initialize()
objMerge.Run()
objMerge.Terminate()
We are using dynamic filters with SUSER_SNAME(). However, the data for specific users filtered by SUSER_SNAME works fine while getting data from publisher to the subscriber.
Thanks.
You shouldn't be reinitializing. This is one way you can guarantee you will loose your Subscriber records.
Looking for a SQL Server replication book?
If you want to upload the Subscriber changes to the Publisher before the initialization runs use the bUploadBeforeReinit property of the ReInitializeSubscription method to true.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi,
I didn't get your answer. It says not to use ReinitializeSubscription and again it says use ReinitializeSubscription with bUploadBeforeReinit parameter set to true.
Please explain.
Thanks.
|||Hmm.. Sorry I seem to have missed that you were using objMerge.ReinitializeSubscription(true) in your first post. I somehow was under the impression you were doing this objMerge.ReinitializeSubscription() or objMerge.ReinitializeSubscription(false)
Basically what I was trying to ask you is why are you doing a reinitialization? You normally only do this if there has been a schema/publication change.
If there is no need to do a reinitialization I would not use this statement.
If you are doing a objMerge.ReinitializeSubscription(true) then transactions that occur on the Subscriber should not be deleted unless there are some conflicts. Does conflict viewer reveal anything?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks Hilary for the suggestion. You are right. There were actually conflicts as we didn't find the Identity Range tab for articles enabled and hence could not set the proper identity range. We generated the publication script, manually set the identity
range to sp_addmergearticle and then created publication by running this script - now everything worked just fine even without using ReinitializeSubscription(True).
Now the question is why the identity range tab for the artcles were not active in the first place?
sql
I am trying to synchronize data between a publisher running in SQL Server 2000 enterprise edition and a subscriber running in MSDE 2000 using DMO with Merge ActiveX component. My program is in VB.NET.
Now, everytime I try to synchronize data, the changes at subscriber don't get reflected at publisher. Here is the code segment:
objMerge.ReinitializeSubscription(true)
objMerge.Initialize()
objMerge.Run()
objMerge.Terminate()
We are using dynamic filters with SUSER_SNAME(). However, the data for specific users filtered by SUSER_SNAME works fine while getting data from publisher to the subscriber.
Thanks.
You shouldn't be reinitializing. This is one way you can guarantee you will loose your Subscriber records.
Looking for a SQL Server replication book?
If you want to upload the Subscriber changes to the Publisher before the initialization runs use the bUploadBeforeReinit property of the ReInitializeSubscription method to true.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi,
I didn't get your answer. It says not to use ReinitializeSubscription and again it says use ReinitializeSubscription with bUploadBeforeReinit parameter set to true.
Please explain.
Thanks.
|||Hmm.. Sorry I seem to have missed that you were using objMerge.ReinitializeSubscription(true) in your first post. I somehow was under the impression you were doing this objMerge.ReinitializeSubscription() or objMerge.ReinitializeSubscription(false)
Basically what I was trying to ask you is why are you doing a reinitialization? You normally only do this if there has been a schema/publication change.
If there is no need to do a reinitialization I would not use this statement.
If you are doing a objMerge.ReinitializeSubscription(true) then transactions that occur on the Subscriber should not be deleted unless there are some conflicts. Does conflict viewer reveal anything?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks Hilary for the suggestion. You are right. There were actually conflicts as we didn't find the Identity Range tab for articles enabled and hence could not set the proper identity range. We generated the publication script, manually set the identity
range to sp_addmergearticle and then created publication by running this script - now everything worked just fine even without using ReinitializeSubscription(True).
Now the question is why the identity range tab for the artcles were not active in the first place?
sql
Monday, March 19, 2012
memory usage of sqlservr.exe
Hi,
Can we limit the memory usage of sqlservr.exe ?
I am using MSDE to run a 24x7 system and the sqlservr.exe takes around 200,000K (as shown in task manager). My concern is whether such usage would have adverse effect on other processes.
The system configuration is:
PIII-700MHz, 256MB RAM
MSDE 2000 over Win NT 4.0 (SP6)
Thanks in advance.
regards,
henryThere is a way to limit how much memory sql server can use in a given machine. But judging from the hardware configuration of your server, the machine is barely enough to run sql 2000. I don't know how much resource you need to run your database, but since you've mentioned 'other processes' in the box I would think that box has other things running. The slowdown could very well be caused by reaching the limit of your hardware capacities.
To set a fixed amount of memory
Expand a server group.
Right-click a server, and then click Properties.
Click the Memory tab.
Click Use a fixed memory size (MB), and then position the fixed memory slider.|||Hi Joe!
Thaks for your valuable input.
Yes, there is are several other exes running alongwith the msde.
Actually, i have not noticed slowdown in any process. But due to the varying memory consumption of the sqlmangr.exe, i am wondering whether the fixed memory option should be applied.
Please advise.
Thanks and regards,
henry|||The memory usage you see in Task Manager is simple the max of what you give to sql. SQL will allocate as much memory as you define in the Max Memory property. The actual usage of memory in sql is different. If you want to see the actual usage of memory by sql, run the performance monitor. If you don't see any slowdown, you can give it a try to lower the max memory.|||Hi!
I used the Performance monitor and compared the Private bytes with Dynamic memory and with fixed memory.
Except for the Private bytes and working set which get restricted in fixed memory case, i could not differentiate between the two options.
On reading the SQL online books, i found that the default dynamic setting is best for the system and i think i shall keep the setting as it is.
Thanks and regards,
henry|||Probably sounds stupid, but still, wouldn't hurt restarting that NT server once a week, even if it needs to be up 24x7. Could help improve overall performance. Generally they do this during Sunday night, when server usage is reduced.
Best regards!
Can we limit the memory usage of sqlservr.exe ?
I am using MSDE to run a 24x7 system and the sqlservr.exe takes around 200,000K (as shown in task manager). My concern is whether such usage would have adverse effect on other processes.
The system configuration is:
PIII-700MHz, 256MB RAM
MSDE 2000 over Win NT 4.0 (SP6)
Thanks in advance.
regards,
henryThere is a way to limit how much memory sql server can use in a given machine. But judging from the hardware configuration of your server, the machine is barely enough to run sql 2000. I don't know how much resource you need to run your database, but since you've mentioned 'other processes' in the box I would think that box has other things running. The slowdown could very well be caused by reaching the limit of your hardware capacities.
To set a fixed amount of memory
Expand a server group.
Right-click a server, and then click Properties.
Click the Memory tab.
Click Use a fixed memory size (MB), and then position the fixed memory slider.|||Hi Joe!
Thaks for your valuable input.
Yes, there is are several other exes running alongwith the msde.
Actually, i have not noticed slowdown in any process. But due to the varying memory consumption of the sqlmangr.exe, i am wondering whether the fixed memory option should be applied.
Please advise.
Thanks and regards,
henry|||The memory usage you see in Task Manager is simple the max of what you give to sql. SQL will allocate as much memory as you define in the Max Memory property. The actual usage of memory in sql is different. If you want to see the actual usage of memory by sql, run the performance monitor. If you don't see any slowdown, you can give it a try to lower the max memory.|||Hi!
I used the Performance monitor and compared the Private bytes with Dynamic memory and with fixed memory.
Except for the Private bytes and working set which get restricted in fixed memory case, i could not differentiate between the two options.
On reading the SQL online books, i found that the default dynamic setting is best for the system and i think i shall keep the setting as it is.
Thanks and regards,
henry|||Probably sounds stupid, but still, wouldn't hurt restarting that NT server once a week, even if it needs to be up 24x7. Could help improve overall performance. Generally they do this during Sunday night, when server usage is reduced.
Best regards!
Monday, March 12, 2012
Memory Usage Following ISA Installation
Hi There
I have just installed ISA 2004 to a Win 2003 server. This had also installed
an instance of SQL MSDE which is eating up the memory. Is there an easy
command prompt statement or registry hack I can run to restrict the maximum
memory usage possible as we are regularly getting "Low Virtual Memory" errors
as it does not seem to be releasing the memory quickly enough. I'm not very
familiar with SQL, so any help out be greatly appriciated. There is no SQL or
MSDE option under the programs list in the Start Menu, so I can't open
Enterprise manager like you would on a normal SQL box. However, there is a
SQL folder within the Program Files directory although this looks a bit slim
compared to a normal install. Failing being able to restrict memory usage, is
it feasble to remove the MSDE installation? I also have Backup Exec running
which is dependant upon the MSSQL service.
Many thanks
Bob
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" wrote:
> Hi There
> I have just installed ISA 2004 to a Win 2003 server. This had also installed
> an instance of SQL MSDE which is eating up the memory. Is there an easy
> command prompt statement or registry hack I can run to restrict the maximum
> memory usage possible as we are regularly getting "Low Virtual Memory" errors
> as it does not seem to be releasing the memory quickly enough. I'm not very
> familiar with SQL, so any help out be greatly appriciated. There is no SQL or
> MSDE option under the programs list in the Start Menu, so I can't open
> Enterprise manager like you would on a normal SQL box. However, there is a
> SQL folder within the Program Files directory although this looks a bit slim
> compared to a normal install. Failing being able to restrict memory usage, is
> it feasble to remove the MSDE installation? I also have Backup Exec running
> which is dependant upon the MSSQL service.
> Many thanks
> Bob
|||Thanks for the reply, however, maybe its me, but I didn't think that really
answered my problem, maybe I'm not looking in the right places, but where is
the Min and Max memory settings? Is it something I have to do from a command
prompt?
Bob
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> INF: SQL Server Memory Usage:
> http://support.microsoft.com/default...;en-us;q321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Bob Sampson" wrote:
|||Hi
Yes. MSDE does not have a UI, you need to run T-SQL code through OSQL.
To set a minimum of 64Mb and a Maximum of 256Mb, run from the command
prompt:
osql -Q"sp_configure 'min server memory', 64 RECONFIGURE WITH OVERRIDE" -E
osql -Q"sp_configure 'sp_configure 'max server memory', 256 RECONFIGURE WITH
OVERRIDE" -E
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" <BobSampson@.discussions.microsoft.com> wrote in message
news:44FB02FD-8B86-4061-B8AC-D2D4F9F38165@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, however, maybe its me, but I didn't think that
> really
> answered my problem, maybe I'm not looking in the right places, but where
> is
> the Min and Max memory settings? Is it something I have to do from a
> command
> prompt?
> Bob
> "Mike Epprecht (SQL MVP)" wrote:
I have just installed ISA 2004 to a Win 2003 server. This had also installed
an instance of SQL MSDE which is eating up the memory. Is there an easy
command prompt statement or registry hack I can run to restrict the maximum
memory usage possible as we are regularly getting "Low Virtual Memory" errors
as it does not seem to be releasing the memory quickly enough. I'm not very
familiar with SQL, so any help out be greatly appriciated. There is no SQL or
MSDE option under the programs list in the Start Menu, so I can't open
Enterprise manager like you would on a normal SQL box. However, there is a
SQL folder within the Program Files directory although this looks a bit slim
compared to a normal install. Failing being able to restrict memory usage, is
it feasble to remove the MSDE installation? I also have Backup Exec running
which is dependant upon the MSSQL service.
Many thanks
Bob
INF: SQL Server Memory Usage:
http://support.microsoft.com/default...;en-us;q321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" wrote:
> Hi There
> I have just installed ISA 2004 to a Win 2003 server. This had also installed
> an instance of SQL MSDE which is eating up the memory. Is there an easy
> command prompt statement or registry hack I can run to restrict the maximum
> memory usage possible as we are regularly getting "Low Virtual Memory" errors
> as it does not seem to be releasing the memory quickly enough. I'm not very
> familiar with SQL, so any help out be greatly appriciated. There is no SQL or
> MSDE option under the programs list in the Start Menu, so I can't open
> Enterprise manager like you would on a normal SQL box. However, there is a
> SQL folder within the Program Files directory although this looks a bit slim
> compared to a normal install. Failing being able to restrict memory usage, is
> it feasble to remove the MSDE installation? I also have Backup Exec running
> which is dependant upon the MSSQL service.
> Many thanks
> Bob
|||Thanks for the reply, however, maybe its me, but I didn't think that really
answered my problem, maybe I'm not looking in the right places, but where is
the Min and Max memory settings? Is it something I have to do from a command
prompt?
Bob
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> INF: SQL Server Memory Usage:
> http://support.microsoft.com/default...;en-us;q321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Bob Sampson" wrote:
|||Hi
Yes. MSDE does not have a UI, you need to run T-SQL code through OSQL.
To set a minimum of 64Mb and a Maximum of 256Mb, run from the command
prompt:
osql -Q"sp_configure 'min server memory', 64 RECONFIGURE WITH OVERRIDE" -E
osql -Q"sp_configure 'sp_configure 'max server memory', 256 RECONFIGURE WITH
OVERRIDE" -E
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob Sampson" <BobSampson@.discussions.microsoft.com> wrote in message
news:44FB02FD-8B86-4061-B8AC-D2D4F9F38165@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply, however, maybe its me, but I didn't think that
> really
> answered my problem, maybe I'm not looking in the right places, but where
> is
> the Min and Max memory settings? Is it something I have to do from a
> command
> prompt?
> Bob
> "Mike Epprecht (SQL MVP)" wrote:
Memory usage - double of the expected value?
Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
--
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>> Hi
>> No. SQL Server only uses what is is configured to use (700MB).
>> This drop of 1400MB, was it Physical Memory or Commit Charge in task
>> manager?
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the
>> maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>
>
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
--
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>> Hi
>> No. SQL Server only uses what is is configured to use (700MB).
>> This drop of 1400MB, was it Physical Memory or Commit Charge in task
>> manager?
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the
>> maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>
>
Memory usage - double of the expected value?
Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
Frank
Hi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>
|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
Frank
Hi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>
|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>
Memory usage - double of the expected value?
Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager
?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB whi
ch
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twi
ce
> of the memory that was set as maximum and that was dispalyed as real memor
y
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a re
al
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager
?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB whi
ch
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twi
ce
> of the memory that was set as maximum and that was dispalyed as real memor
y
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a re
al
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>
Monday, February 20, 2012
Memory Leaks using MSDE 2000
Hello.
Our company is running an application that uses the MSDE 2000 (SP4), to load
large amount of data using bulk inserts.
The sqlservr.exe process increases from the 4-10MB that it starts with to
80-100MB after all transactions has been completed.
When starting a new transaction session, the process just continue to grow
causing the OS to perform poorly.
Ok, I understand that while on an insert transaction, the memory should grow
but when we are done, why doesn’t it release the resources?
You can see that this is a bad behavior if you just close the process
manually and start it again, the memory will start with 4-10MB, and the
application still works, meaning that the exe have lots of unused resources.
What can I do about that (other than limiting the memory size to xMB)?
Hi
Not leaking, it is by design
INF: SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
> Hello.
> Our company is running an application that uses the MSDE 2000 (SP4), to
> load
> large amount of data using bulk inserts.
> The sqlservr.exe process increases from the 4-10MB that it starts with to
> 80-100MB after all transactions has been completed.
> When starting a new transaction session, the process just continue to grow
> causing the OS to perform poorly.
> Ok, I understand that while on an insert transaction, the memory should
> grow
> but when we are done, why doesn't it release the resources?
> You can see that this is a bad behavior if you just close the process
> manually and start it again, the memory will start with 4-10MB, and the
> application still works, meaning that the exe have lots of unused
> resources.
> What can I do about that (other than limiting the memory size to xMB)?
>
>
|||Ok, it’s by design, pretty weird design, by ok.
Now, how can I handle this, design or not, it is a problem…
Limiting the max memory? Stopping and starring the process of the SQL?
What is the best practice for it?
Eli.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Not leaking, it is by design
> INF: SQL Server Memory Usage
> http://support.microsoft.com/default...b;en-us;321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Eli" <Eli@.discussions.microsoft.com> wrote in message
> news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
>
>
|||Hi
Set the MAX memory.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:5D23EFC5-6240-47E3-AF83-B8B0CB0DC5D7@.microsoft.com...[vbcol=seagreen]
> Ok, it's by design, pretty weird design, by ok.
> Now, how can I handle this, design or not, it is a problem.
> Limiting the max memory? Stopping and starring the process of the SQL?
> What is the best practice for it?
> Eli.
>
> "Mike Epprecht (SQL MVP)" wrote:
Our company is running an application that uses the MSDE 2000 (SP4), to load
large amount of data using bulk inserts.
The sqlservr.exe process increases from the 4-10MB that it starts with to
80-100MB after all transactions has been completed.
When starting a new transaction session, the process just continue to grow
causing the OS to perform poorly.
Ok, I understand that while on an insert transaction, the memory should grow
but when we are done, why doesn’t it release the resources?
You can see that this is a bad behavior if you just close the process
manually and start it again, the memory will start with 4-10MB, and the
application still works, meaning that the exe have lots of unused resources.
What can I do about that (other than limiting the memory size to xMB)?
Hi
Not leaking, it is by design
INF: SQL Server Memory Usage
http://support.microsoft.com/default...b;en-us;321363
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
> Hello.
> Our company is running an application that uses the MSDE 2000 (SP4), to
> load
> large amount of data using bulk inserts.
> The sqlservr.exe process increases from the 4-10MB that it starts with to
> 80-100MB after all transactions has been completed.
> When starting a new transaction session, the process just continue to grow
> causing the OS to perform poorly.
> Ok, I understand that while on an insert transaction, the memory should
> grow
> but when we are done, why doesn't it release the resources?
> You can see that this is a bad behavior if you just close the process
> manually and start it again, the memory will start with 4-10MB, and the
> application still works, meaning that the exe have lots of unused
> resources.
> What can I do about that (other than limiting the memory size to xMB)?
>
>
|||Ok, it’s by design, pretty weird design, by ok.
Now, how can I handle this, design or not, it is a problem…
Limiting the max memory? Stopping and starring the process of the SQL?
What is the best practice for it?
Eli.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Not leaking, it is by design
> INF: SQL Server Memory Usage
> http://support.microsoft.com/default...b;en-us;321363
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Eli" <Eli@.discussions.microsoft.com> wrote in message
> news:3444B8D1-3E64-4EF3-B457-2EFB5543C0C7@.microsoft.com...
>
>
|||Hi
Set the MAX memory.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Eli" <Eli@.discussions.microsoft.com> wrote in message
news:5D23EFC5-6240-47E3-AF83-B8B0CB0DC5D7@.microsoft.com...[vbcol=seagreen]
> Ok, it's by design, pretty weird design, by ok.
> Now, how can I handle this, design or not, it is a problem.
> Limiting the max memory? Stopping and starring the process of the SQL?
> What is the best practice for it?
> Eli.
>
> "Mike Epprecht (SQL MVP)" wrote:
Subscribe to:
Posts (Atom)