Merge Dynamic filter problem

Hi!

I'm a merge newbie and have a couple of questions. I'm about to setup a merge replication with Sql Server 2005 and Sql Server CE as a subscriber. Situation is like this, we have 10 service technicians using pda.

I want to each pda user have their own data. What I understand I need to use dynamic filter and SUSER_NAME()? Do I need to create a "translation" table to map my system's UserId against SUSER_NAME? How have you solved this problem?

/Magnus

Hello magnus,

One easy approach is to have a column in the table for filtering purpose.

Please take a look on sp_addmergearticle (Transact-SQL) , from http://msdn2.microsoft.com/en-us/library/ms174329.aspx.

[ @.subset_filterclause = ] 'subset_filterclause'
Is a WHERE clause specifying the horizontal filtering of a table article without the word WHERE included. subset_filterclause is of nvarchar(1000), with a default of an empty string.

Important:
For performance reasons, we recommended that you not apply functions to column names in parameterized row filter clauses, such as LEFT([MyColumn]) = SUSER_SNAME(). If you use HOST_NAME in a filter clause and override the HOST_NAME value, you might have to convert data types by using CONVERT. For more information about best practices for this case, see the section "Overriding the HOST_NAME() Value" in Parameterized Row Filters.

Parameterized Row Filters - http://msdn2.microsoft.com/en-us/library/ms152478.aspx.

and

[ @.partition_options = ] partition_options
Defines the way in which data in the article is partitioned, which enables performance optimizations when all rows belong in only one partition or in only one subscription. partition_options is tinyint, and can be one of the following values.

Value Description
0 (default)
The filtering for the article either is static or does not yield a unique subset of data for each partition, that is, an "overlapping" partition.
1
The partitions are overlapping, and data manipulation language (DML) updates made at the Subscriber cannot change the partition to which a row belongs.
2
The filtering for the article yields non-overlapping partitions, but multiple Subscribers can receive the same partition.
3
The filtering for the article yields non-overlapping partitions that are unique for each subscription.

Merge agent fails due to deadlock

Hi:
I met a problem this morning. the merge replication agen stopped, the
error message shows:
The process could not enumerate changes at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
-----
Transaction (Process ID 79) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
(Source: XXXXXX (Data source); Error number: 1205)
The process was successfully stopped.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
I never met this issue before, so please help
1) What may cause this issue?
2) How I could identify which process cause the deadlock issue?
3) Is there any way to log more information to identify what resource
is deadlock, and by who.
Thanks
Yong
Yong,
have a look at enabling traceflags 1204, 1205, 3605 to get more info. Don't
forget to turn off afterwards. Alternatively if you have sql server 2005,
the profiler shows the deadlock tree graphically.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

MemToLeave and Thirdy Pary DLLs

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

Memory usage grows and grows

Hi!
I have an issue with sql server 2005. It weems that the memory usage grows
forever and never decreases. If I get users on they use a certain amount of
memory. If they all get off the system, the memory usage never goes down. If
they get back on it then grows. Eventually the machine starts to thrash
because it runs out of physical memory. What's up with that?
Joe
Joe D. wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount of
> memory. If they all get off the system, the memory usage never goes down. If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that?
> Joe
That's normal behavior for SQL Server - it caches data pages in memory
to minimize disk I/O, and won't release memory unless the O/S requests
it. Part of your job, as the admin, is to balance the memory use
between SQL and the O/S to prevent that "thrashing".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The first half of the description is more or less the expected behavior, and
not a problem. But the following is not the expected behavior:

> Eventually the machine starts to thrash because it runs out of physical memory.
Could you be more specific about 'thrash' and 'runs out of physical memory'?
Linchi
"Joe D." wrote:

> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount of
> memory. If they all get off the system, the memory usage never goes down. If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that?
> Joe
|||Yes...
It appears that it starts using swap space. The CPU load jumps and the
machine slows to a crawl. If I get everyone off and restart the instance,
all gets back to normal. This normally takes about 4 - 5 hours to degrade to
the point of restart.
Joe
"Linchi Shea" wrote:
[vbcol=seagreen]
> The first half of the description is more or less the expected behavior, and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical memory'?
> Linchi
> "Joe D." wrote:
|||The expected behavior is for SQL Server to release memory when the
system becomes memory constrained, just as it is expected to keep
grabbing more when it is available. However for situations like you
describe SQL Server has a configuration setting to limit the total
memory it will use, and it sounds like you need to use it to allow
enough memory for whatever else is on the system.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Jan 2007 12:01:02 -0800, Joe D.
<JoeD@.discussions.microsoft.com> wrote:

>Yes...
>It appears that it starts using swap space. The CPU load jumps and the
>machine slows to a crawl. If I get everyone off and restart the instance,
>all gets back to normal. This normally takes about 4 - 5 hours to degrade to
>the point of restart.
>Joe
|||This is a very common problem and I wish the OS and SQL groups would address
it. SQL Server will hold onto all memory until there is 5 MB of free
memory. It appears as if the disk cache is included in the free memory and
is usually much larger than 5 MB. Therefore, SQL Server never releases
memory. The disk cache will swap the SQL buffer out of memory to disk in
order to make more room for the disk cache if the disk cache is getting too
small. This means that SQL Server will NEVER release memory as long as you
have swap space left. Your only option is to set the max memory of SQL
server to a lower amount so that there is reserved free space for the
OS/disk cache.
I've experienced this myself numerous times including on a 32 GB 4 way
server running the 64 bit version of SQL 2005. This was a box for a data
warehouse and the users complained that it sometimes was very slow. Looking
at the max cache size, it was sometimes using 4 GB of RAM for the disk
cache, but SQL was using 30 GB of RAM (30 + 4 isn't 32). I had to set the
max memory of SQL to 27 GB and their performance improved dramatically.
You can use "Memory:Cache Bytes Peak" to see the max amount of memory used
for cache and "Memory:Cache Bytes" to see the current amount. You can also
look for the committed bytes + cache being higher than physical memory.
This indicates that you are having memory pressure and probably need to
adjust something.
Thanks,
Stephen Mills
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:D305F0C7-355A-461E-8E1A-1C2B2413E87D@.microsoft.com...[vbcol=seagreen]
> The first half of the description is more or less the expected behavior,
> and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical
> memory'?
> Linchi
> "Joe D." wrote:

Memory usage grows and grows

Hi!
I have an issue with sql server 2005. It weems that the memory usage grows
forever and never decreases. If I get users on they use a certain amount of
memory. If they all get off the system, the memory usage never goes down. If
they get back on it then grows. Eventually the machine starts to thrash
because it runs out of physical memory. What's up with that'
JoeJoe D. wrote:
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount o
f
> memory. If they all get off the system, the memory usage never goes down.
If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that'
> Joe
That's normal behavior for SQL Server - it caches data pages in memory
to minimize disk I/O, and won't release memory unless the O/S requests
it. Part of your job, as the admin, is to balance the memory use
between SQL and the O/S to prevent that "thrashing".
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The first half of the description is more or less the expected behavior, and
not a problem. But the following is not the expected behavior:

> Eventually the machine starts to thrash because it runs out of physical memory.[/v
bcol]
Could you be more specific about 'thrash' and 'runs out of physical memory'?
Linchi
"Joe D." wrote:
[vbcol=seagreen]
> Hi!
> I have an issue with sql server 2005. It weems that the memory usage grows
> forever and never decreases. If I get users on they use a certain amount o
f
> memory. If they all get off the system, the memory usage never goes down.
If
> they get back on it then grows. Eventually the machine starts to thrash
> because it runs out of physical memory. What's up with that'
> Joe|||Yes...
It appears that it starts using swap space. The CPU load jumps and the
machine slows to a crawl. If I get everyone off and restart the instance,
all gets back to normal. This normally takes about 4 - 5 hours to degrade t
o
the point of restart.
Joe
"Linchi Shea" wrote:
[vbcol=seagreen]
> The first half of the description is more or less the expected behavior, a
nd
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical memory
'?
> Linchi
> "Joe D." wrote:
>|||The expected behavior is for SQL Server to release memory when the
system becomes memory constrained, just as it is expected to keep
grabbing more when it is available. However for situations like you
describe SQL Server has a configuration setting to limit the total
memory it will use, and it sounds like you need to use it to allow
enough memory for whatever else is on the system.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Jan 2007 12:01:02 -0800, Joe D.
<JoeD@.discussions.microsoft.com> wrote:

>Yes...
>It appears that it starts using swap space. The CPU load jumps and the
>machine slows to a crawl. If I get everyone off and restart the instance,
>all gets back to normal. This normally takes about 4 - 5 hours to degrade
to
>the point of restart.
>Joe|||This is a very common problem and I wish the OS and SQL groups would address
it. SQL Server will hold onto all memory until there is 5 MB of free
memory. It appears as if the disk cache is included in the free memory and
is usually much larger than 5 MB. Therefore, SQL Server never releases
memory. The disk cache will swap the SQL buffer out of memory to disk in
order to make more room for the disk cache if the disk cache is getting too
small. This means that SQL Server will NEVER release memory as long as you
have swap space left. Your only option is to set the max memory of SQL
server to a lower amount so that there is reserved free space for the
OS/disk cache.
I've experienced this myself numerous times including on a 32 GB 4 way
server running the 64 bit version of SQL 2005. This was a box for a data
warehouse and the users complained that it sometimes was very slow. Looking
at the max cache size, it was sometimes using 4 GB of RAM for the disk
cache, but SQL was using 30 GB of RAM (30 + 4 isn't 32). I had to set the
max memory of SQL to 27 GB and their performance improved dramatically.
You can use "Memory:Cache Bytes Peak" to see the max amount of memory used
for cache and "Memory:Cache Bytes" to see the current amount. You can also
look for the committed bytes + cache being higher than physical memory.
This indicates that you are having memory pressure and probably need to
adjust something.
Thanks,
Stephen Mills
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:D305F0C7-355A-461E-8E1A-1C2B2413E87D@.microsoft.com...[vbcol=seagreen]
> The first half of the description is more or less the expected behavior,
> and
> not a problem. But the following is not the expected behavior:
>
> Could you be more specific about 'thrash' and 'runs out of physical
> memory'?
> Linchi
> "Joe D." wrote:
>

Memory Settings

Hi
I Have a sql server standard edition running on win2k server sp3.
1g Ram on the server.
The sever records plant data in an idustrial appilcation every minute.
After a week of use i have noticed that in the task manager view the memory consuption has increased yet when looking at the performance monitor total server memory is equal to target server memory. Is this correct as the memory settings are set to limit
the sql server to 511meg as the sql process is greater than this.
Surely when sql reaches its limit is should release memory back to the system. Is this a memeory leak? or normal .
After another couple of days the sql server crashes and the only way out is a reboot
Can anyone help please very frustrating as the
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
> Hi
> I Have a sql server standard edition running on win2k server sp3.
> 1g Ram on the server.
> The sever records plant data in an idustrial appilcation every minute.
> After a week of use i have noticed that in the task manager view the
> memory consuption has increased yet when looking at the performance
> monitor total server memory is equal to target server memory. Is
> this correct as the memory settings are set to limit the sql server
> to 511meg as the sql process is greater than this.
> Surely when sql reaches its limit is should release memory back to
> the system. Is this a memeory leak? or normal .
> After another couple of days the sql server crashes and the only way out
is a reboot
>
If SQL Server were crashing, you should raise a case with Microsoft Product
Support so the crash can be diagnosed and remedied.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

Memory Settings

Hi
I Have a sql server standard edition running on win2k server sp3.
1g Ram on the server.
The sever records plant data in an idustrial appilcation every minute.
After a week of use i have noticed that in the task manager view the memory
consuption has increased yet when looking at the performance monitor total s
erver memory is equal to target server memory. Is this correct as the memory
settings are set to limit
the sql server to 511meg as the sql process is greater than this.
Surely when sql reaches its limit is should release memory back to the syste
m. Is this a memeory leak? or normal .
After another couple of days the sql server crashes and the only way out is
a reboot
Can anyone help please very frustrating as the
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.>
> Hi
> I Have a sql server standard edition running on win2k server sp3.
> 1g Ram on the server.
> The sever records plant data in an idustrial appilcation every minute.
> After a week of use i have noticed that in the task manager view the
> memory consuption has increased yet when looking at the performance
> monitor total server memory is equal to target server memory. Is
> this correct as the memory settings are set to limit the sql server
> to 511meg as the sql process is greater than this.
> Surely when sql reaches its limit is should release memory back to
> the system. Is this a memeory leak? or normal .
> After another couple of days the sql server crashes and the only way out
is a reboot
>
--
If SQL Server were crashing, you should raise a case with Microsoft Product
Support so the crash can be diagnosed and remedied.
Hope this helps,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.