Merge Agent Profile settings

I am trying to limit the downloaded records per merge session over my
less reliable links. Under SQL2000 I could set MaxDownloadChanges in the
agent profile to 1500, but in SQL2005 this value is limited to 300 max.
Has anybody run into this issue before? Why was it changed from SQL2000
to SQL2005?
The parameters are defined in msdb..MSagentparameterlist. I am
considering changing the record to allow a different maximum. Would this
be an issue?
I have no idea why it was changed but you should be able to override the
profile settings in the agent properties.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JE" <jgegroups.spam@.gmail.nospam.com> wrote in message
news:uqaHGxtaHHA.1296@.TK2MSFTNGP02.phx.gbl...
>I am trying to limit the downloaded records per merge session over my less
>reliable links. Under SQL2000 I could set MaxDownloadChanges in the agent
>profile to 1500, but in SQL2005 this value is limited to 300 max. Has
>anybody run into this issue before? Why was it changed from SQL2000 to
>SQL2005?
> The parameters are defined in msdb..MSagentparameterlist. I am considering
> changing the record to allow a different maximum. Would this be an issue?
|||When I try to override it I am not allowed to enter a value higher than
300. In SQL2000 I was able to enter any value, but in SQL2005 it limits
me to 300.
Hilary Cotter wrote:
> I have no idea why it was changed but you should be able to override the
> profile settings in the agent properties.
>
|||Open up replication monitor, locate your publication, locate your
publication (click on it), right click and select view details, click on
Action, Merge Agent Job Properties, and select Steps, run agent, and click
edit, click in the command box, and hit end, then type -MaxDownloadChanges
100
Close the dialog and restart your agent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JE" <jgegroups.spam@.gmail.nospam.com> wrote in message
news:45FFC69D.2030108@.gmail.nospam.com...[vbcol=seagreen]
> When I try to override it I am not allowed to enter a value higher than
> 300. In SQL2000 I was able to enter any value, but in SQL2005 it limits me
> to 300.
> Hilary Cotter wrote:
|||I see, so adding the parameter directly to the merge agent job will
override the value specified in the default merge agent profile and also
allow me to set it to whatever I need.
I wonder if Microsoft can tell us why they limit the MaxDownloadChanges
to 300 in the agent profile properties dialog.
Hilary Cotter wrote:
> Open up replication monitor, locate your publication, locate your
> publication (click on it), right click and select view details, click on
> Action, Merge Agent Job Properties, and select Steps, run agent, and click
> edit, click in the command box, and hit end, then type -MaxDownloadChanges
> 100
> Close the dialog and restart your agent.
>
|||Yes, it will override the profile setting. I don't know why the default
setting in the profile is 300. The default is everything, with a minimum of
100 (should there be over 100 changes to be downloaded).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JE" <jgegroups.spam@.gmail.nospam.com> wrote in message
news:45FFDF85.7030807@.gmail.nospam.com...[vbcol=seagreen]
>I see, so adding the parameter directly to the merge agent job will
>override the value specified in the default merge agent profile and also
>allow me to set it to whatever I need.
> I wonder if Microsoft can tell us why they limit the MaxDownloadChanges to
> 300 in the agent profile properties dialog.
> Hilary Cotter wrote:

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!

Memory pb

Hie,
When i restrict the memory of the db in property dialog box, the process sqlserv.exe increase his memry above this limit why ?Have you enabled AWE? That pretty much circumvents the normal Windows process management, mostly because that is exactly what it is supposed to do!

Even if you haven't enabled AWE, the server responds to its environment. If large blocks of RAM are not being used, then the OS can elect to dynamically ease the restrictions on an application that is memory starved (which happens fast with a busy SQL Server).

-PatP

Memory limit

Hi, my new server has 4.5 GB of RAM.
I'd like to configure SQL2k Enterprise to use 3.3 GB.
After adding /3gb and /pae at Boot.ini and setting max/min server memory to
3.3 GB, SQL still returs the
Warning: unable to allocate 'min server memory' of 3300 MB.
Any clue?
TIA,
Roberto de Souza Santos.Additional configuration is needed - refer to BOL - managing AWE memory
"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>|||"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
What version of OS do you have?
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>

Memory limit

Hi, my new server has 4.5 GB of RAM.
I'd like to configure SQL2k Enterprise to use 3.3 GB.
After adding /3gb and /pae at Boot.ini and setting max/min server memory to
3.3 GB, SQL still returs the
Warning: unable to allocate 'min server memory' of 3300 MB.
Any clue?
TIA,
Roberto de Souza Santos.Additional configuration is needed - refer to BOL - managing AWE memory
"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>|||"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
What version of OS do you have?

> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>

Memory limit

Hi, my new server has 4.5 GB of RAM.
I'd like to configure SQL2k Enterprise to use 3.3 GB.
After adding /3gb and /pae at Boot.ini and setting max/min server memory to
3.3 GB, SQL still returs the
Warning: unable to allocate 'min server memory' of 3300 MB.
Any clue?
TIA,
Roberto de Souza Santos.
Additional configuration is needed - refer to BOL - managing AWE memory
"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>
|||"Roberto Souza" <roberto_tbabh@.hotmail.com> wrote in message
news:OizvTSHBFHA.4028@.TK2MSFTNGP15.phx.gbl...
> Hi, my new server has 4.5 GB of RAM.
> I'd like to configure SQL2k Enterprise to use 3.3 GB.
What version of OS do you have?

> After adding /3gb and /pae at Boot.ini and setting max/min server memory
to
> 3.3 GB, SQL still returs the
> Warning: unable to allocate 'min server memory' of 3300 MB.
> Any clue?
> TIA,
> Roberto de Souza Santos.
>