Merge Metadata Cleanup

Hello Everyone,
I am running SQL2K with sp3, continuous merge replication, only one
subscriber.
My current setting for Subscription expiration is the default of 7, I
have just recently changed this from the default of 14 days, as our
Subscriber is basically always available.
I am still trying to figure out how to cleanup my merge metadata. From
what I have read and from what I understand ,
sp_mergemetadataretentioncleanup is automatically run, if the agent
running Merge has the Parameter for MetadataRententionCleanup set to 1
I am under the assumption that if I have has the Parameter for
MetadataRententionCleanup set to 1 it should automatically be cleaning
up my metadata. Even though I have it set to 1 it does not appear
like it is actually cleaning out MSmerge_contents or MSmerge_tombstone.
These tables just keep growing.
Now if I had this parameter set to 0 then I would need to use
sp_mergecleanupmetadata to clean up MSmerge_contents or
MSmerge_tombstone.
Would there ever be a reason for running both?
I did go to BOL but I read the following about using
sp_mergecleanupmetadata:
If you want to run sp_mergecleanupmetadata without the subscriptions
being marked for reinitialization:
Stop all updates to the publication and subscription databases.
Unfortunately, I cannot stop all updates as our servers need to be
available 24/7.
Does anyone run this without stopping replication first? And if you do
run it do you have MetadataRententionCleanup set to 0?
If anyone can help I truly would appreciate it.
Thanks,
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
are you using anonymous subscribers? Merge replication does not clean
up the metadata as efficiently with anonymous subscrptions as opposed
to named subscriptions.
|||Thanks Hilary.
Unfortunately no, we do not have any anonymous subscriptions only one
named subscriptions.
Any other ideas?
Barbara
*** Sent via Developersdex http://www.codecomments.com ***
|||I believe I have my answer:
1) sp_mergecleanupmetadata
2) sp_mergemetadataretentioncleanup
The First should be used for topologies with SQL Server 2000 SP1 or less
(which is not my case)
The Second one is the one to control the process manually for SQL Server
> SP1 which is your case and when the MetadataretentionCleanup is set to
0 (not automatic)
There is also a catch though the second sp is executed automatically by
merge Agent at at startup! when MetadataretentionCleanup is set to 1
therefore when in continuous mode it will be called only once.
So I'll need to manually run sp_mergemetadataretentioncleanup or setup a
job that will do it for me on a scheduled basis.
Thanks for your help Hilary.
Barbara
*** Sent via Developersdex http://www.codecomments.com ***

Memory usage - max setting

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

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

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

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

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

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

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

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

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

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

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

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

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

It is explained in the online books:

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

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

Memory setting right ?

I have a few questions about memory setting inherited

Windows 2003, SQL Server SP4, both Enterprise Edition 8 GB RAM

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

4. Should I set max worker set also ?

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Thanks

Srinivas(masri999@.gmail.com) writes:

Quote:

Originally Posted by

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

This is in Books Online. It's 64 GB for Enterprise Edition, if memory
serves. (Either I look it up, or you do. It's under SQL Server
Architecture, Implementation details. Again, if memory serves.

Quote:

Originally Posted by

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

Yes, I believe you have to turn AWE to use all that memory.

Quote:

Originally Posted by

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

You should stick to 0, and let SQL Server configure it, unless you have
a sepecial reason.

Quote:

Originally Posted by

4. Should I set max worker set also ?

Don't know, but I have never heard of that.

Quote:

Originally Posted by

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

Unfortunately, I never play with big servers myself. I've only been
eavesdropping on others who have been talking about them.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland . See my Comments in [Sri]

Thanks

Erland Sommarskog wrote:

Quote:

Originally Posted by

(masri999@.gmail.com) writes:

Quote:

Originally Posted by

1. How to know how much Memory SQL can address ( SQL script is better
as there is no access to Perfmonitor or EM ) . ie what is maximum
memory SQL can use ?

>
This is in Books Online. It's 64 GB for Enterprise Edition, if memory
serves. (Either I look it up, or you do. It's under SQL Server
Architecture, Implementation details. Again, if memory serves.

[Sri] What I meant was that how much memory SQL Server can see. ie If I
put 8 GB on SQL Server System , whether SQL Server able to utilise all
the 8 GB installed and not wasted .

Quote:

Originally Posted by

Quote:

Originally Posted by

2. In this server awe is set to 0 . Do I have to turn it to 1 so that
SQL can use all memory .

>
Yes, I believe you have to turn AWE to use all that memory.
>

Quote:

Originally Posted by

3. If awe is enabled, what is the minimum and maximum memory size need
to be set ?

>
You should stick to 0, and let SQL Server configure it, unless you have
a sepecial reason.

[Sri] My information is that , once you turn AWE , you should turn off
Dynamically Configure memory . Otherwise SQL Server will utilise all
the memory and leaving 128 MB to O/S . Am I right ?

Quote:

Originally Posted by

Quote:

Originally Posted by

4. Should I set max worker set also ?

>
Don't know, but I have never heard of that.

[Sri] Typo Error . This should have been set working set size .Should I
set it to 1 or 0 ?

Quote:

Originally Posted by

Quote:

Originally Posted by

If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I am
grateful

>
Unfortunately, I never play with big servers myself. I've only been
eavesdropping on others who have been talking about them.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||(masri999@.gmail.com) writes:

Quote:

Originally Posted by

[Sri] My information is that , once you turn AWE , you should turn off
Dynamically Configure memory . Otherwise SQL Server will utilise all
the memory and leaving 128 MB to O/S . Am I right ?

Not entirely, but in essence. Since I don't this stuff too well, I looked in
Books Online. Check the topic "Using AWE Memory on Windows 2000" in Books
Online. There is also another configuration parameter "awe enabled" that
you need to change.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Memory setting on a Cluster

Hello, I am purchasing a HP DL-380 (Cluster)
I am planing to install Windows 2003 and SQL 2000
Both machine has 4 GB o Physical Memory (RAM), but I want to put 6 GB in the
Active Machine and 2 GB in the Passive Machine.
So, That is possible?
1) Is possible to have differentes memory configuration in a cluster?
2) SQL 2000 and Windows 2003 supports 6GB of Physical Memory?
Thanks a lot!!
AA
I forgot to mention that both machine are with 32 bits processors
Thanks again
AA
"AA" <aa@.personal.net.py> wrote in message
news:%23qJn80MIEHA.3848@.tk2msftngp13.phx.gbl...
> Hello, I am purchasing a HP DL-380 (Cluster)
> I am planing to install Windows 2003 and SQL 2000
> Both machine has 4 GB o Physical Memory (RAM), but I want to put 6 GB in
the
> Active Machine and 2 GB in the Passive Machine.
> So, That is possible?
> 1) Is possible to have differentes memory configuration in a cluster?
> 2) SQL 2000 and Windows 2003 supports 6GB of Physical Memory?
>
> Thanks a lot!!
>
> AA
>
|||1. Yes, it is possible. Not sure why you'd want to do it though.
2. Yes, it is supported as long as you enable AWE.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||1. ) because an error in the requirement the cluster come with 4 GB Memory
each, but I think that the Active Server must be more powerful
In the current environment we have installed SQL 2000 in a single machine
dual processor and is UP without restart (the OS and the DB) for more that 6
months, without any kind of problems. In two years we NEVER have any
problems with our SQL 2000 database. I am very happy
So, I want to use the Pasive machine just for emergencies (when the Active
Failure) and increment the Active server power.
Is that ok?
Thanks Michael
AA
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:u6MKyDSIEHA.3968@.TK2MSFTNGP12.phx.gbl...
> 1. Yes, it is possible. Not sure why you'd want to do it though.
> 2. Yes, it is supported as long as you enable AWE.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||Yes, you can do that. You just need to recognize that if you ever have to
fail over, you can have some performance issues.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

memory setting in SQL 2005 in Windows 2003

We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:

http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage

What I need to do?

Thanks

Sounds like you have installed the 32 bit version of SQL Server, not the 64bit version of SQL Server.

Can you verify which version of SQL Server was installed?

|||

If I recall, AWE has no effect in 64 bit OS, and extended memory usage is not properly captured with Perf Monitor.

Please refer to this article:

Configuration -Memory, 32 bit SQL 2005 on 64 Bit Windows 2003
http://msdn2.microsoft.com/en-us/library/ms187499.aspx

And for reference on Memory, this is good starting point:

http://msdn2.microsoft.com/en-us/library/ms187499.aspx

|||

Hi Sandlu,

AWE will have no effect on 64 bit servers.

Yes you still need "LOCK PAGES IN MEMORY".

To check exactly how much memory is being used by SQL Server use Perfmon to check for SQL ServerMemoryManager:

Total Server Memory (KB)

Also check if you have capped the memory by using Max Server Memory under sp_configure.

With regards to slow performance, it could be any problem like disks, memory or no/out of date stats or Index defragmentation.

Jag

|||Thank you, everyone, will try.

Memory optimization

Hi
We are running full text search with 4GB RAM on SQL server 2000. Ihave the
/3G option on in boot.ini. The VM setting is set at 2048 to 4095.
I read it in the manual that the optimal configuration for full text search
is
1. The virtual memory size to at least 3 times the physical memory installed
in the computer.
2. The SQL Server max server memory server configuration option to 1.5 times
the physical memory (half the virtual memory size setting).
Questions:
1. How do I set max sql server memory to 1.5 times physical memory? Max
memory that SQL server allows us to set is 4095 MB? Is this only possible
with AWE?
2. My observation when I made the VM 3X the physical, was that the full text
queries were slower.
What are the right settings and what am I doing wrong?
Matt
Hi
Can any body throw some ideas on this one?
MM
"ISTS" wrote:

> Hi
> We are running full text search with 4GB RAM on SQL server 2000. Ihave the
> /3G option on in boot.ini. The VM setting is set at 2048 to 4095.
> I read it in the manual that the optimal configuration for full text search
> is
> 1. The virtual memory size to at least 3 times the physical memory installed
> in the computer.
> 2. The SQL Server max server memory server configuration option to 1.5 times
> the physical memory (half the virtual memory size setting).
> Questions:
> 1. How do I set max sql server memory to 1.5 times physical memory? Max
> memory that SQL server allows us to set is 4095 MB? Is this only possible
> with AWE?
> 2. My observation when I made the VM 3X the physical, was that the full text
> queries were slower.
> What are the right settings and what am I doing wrong?
> --
> Matt

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.
>