I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing tha
t
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.this is not bad, your queries are well parameterized and also your SPs,so th
ousands of querys are using the same plan, the problem is when you have a l
arge proc cache and a litle data cache.
--
Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message news:584554A5-
708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing tha
t
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.
Friday, March 30, 2012
Question(s) about memory management
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing that
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.This is a multi-part message in MIME format.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
this is not bad, your queries are well parameterized and also your =SPs,so thousands of querys are using the same plan, the problem is when =you have a large proc cache and a litle data cache.
-- Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message =news:584554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm =noticing that the procedure cache is shrinking at an alarming rate and the buffer =cache is growing. This is surprising to me as this is a proc intensive server =and I know SQL Server dynamically manages the memory. My question is there =anyway to change the amount of memory allocated to the proc cache? I went =through books online, but I only saw things relating to setting the min/max =memory amounts but nothing directly related to the type of management that =I'm wanting to do.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
this is not bad, your queries are well parameterized and also your SPs,so thousands of querys are using the =same plan, the problem is when you have a large proc cache and a litle data cache.
-- MauroMCTS - SQL Server 2005
"Big Ern" wrote in message news:584=554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...I'm monitoring a SQL Server 2000 server running on W2k3 and I'm noticing =that the procedure cache is shrinking at an alarming rate and the =buffer cache is growing. This is surprising to me as this is a proc intensive =server and I know SQL Server dynamically manages the memory. My question =is there anyway to change the amount of memory allocated to the proc cache? =I went through books online, but I only saw things relating to setting =the min/max memory amounts but nothing directly related to the type of = management that I'm wanting to do.
--=_NextPart_000_02CE_01C76B0A.1C9DB110--sql
the procedure cache is shrinking at an alarming rate and the buffer cache is
growing. This is surprising to me as this is a proc intensive server and I
know SQL Server dynamically manages the memory. My question is there anyway
to change the amount of memory allocated to the proc cache? I went through
books online, but I only saw things relating to setting the min/max memory
amounts but nothing directly related to the type of management that I'm
wanting to do.This is a multi-part message in MIME format.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
this is not bad, your queries are well parameterized and also your =SPs,so thousands of querys are using the same plan, the problem is when =you have a large proc cache and a litle data cache.
-- Mauro
MCTS - SQL Server 2005
"Big Ern" <BigErn@.discussions.microsoft.com> wrote in message =news:584554A5-708A-4F68-924C-94A169B44ED9@.microsoft.com...
I'm monitoring a SQL Server 2000 server running on W2k3 and I'm =noticing that the procedure cache is shrinking at an alarming rate and the buffer =cache is growing. This is surprising to me as this is a proc intensive server =and I know SQL Server dynamically manages the memory. My question is there =anyway to change the amount of memory allocated to the proc cache? I went =through books online, but I only saw things relating to setting the min/max =memory amounts but nothing directly related to the type of management that =I'm wanting to do.
--=_NextPart_000_02CE_01C76B0A.1C9DB110
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
this is not bad, your queries are well parameterized and also your SPs,so thousands of querys are using the =same plan, the problem is when you have a large proc cache and a litle data cache.
-- MauroMCTS - SQL Server 2005
"Big Ern"
--=_NextPart_000_02CE_01C76B0A.1C9DB110--sql
Question!
We are in the process of planning a implementation of SMS server, Project
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle the
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle the
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!
Question!
We are in the process of planning a implementation of SMS server, Project
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle th
e
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle th
e
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!
Question!
We are in the process of planning a implementation of SMS server, Project
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle the
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!
Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!
server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle the
database requirements and traffic for these applications? We have
approximately 400 users and operate on a WAN. I would appreciate any real
world input. Thanks!
Yes. SQL can easily handle thousands.
"Stacy Springer" <Stacy Springer@.discussions.microsoft.com> wrote in message
news:F7B4E072-D97C-4D88-B1FF-082190559FD7@.microsoft.com...
> We are in the process of planning a implementation of SMS server, Project
> server and SharePoint Portal Server. Can one SQL Server 2000 SP3a handle
the
> database requirements and traffic for these applications? We have
> approximately 400 users and operate on a WAN. I would appreciate any real
> world input. Thanks!
question with type of replication
Hello,
I have a question on the subscriptions and type of replication to use.
We have about 5 subscriptions to a published db setup as merge replication
via the websync on sql2k5. We would actually like the subscriptions (all
running sql express) to just download changes to the publisher. We do not
need the publisher to pass any data up to the subscribers. Is there a way to
do this? We need to use the functionality of web synchronization, if at all
possible. Thanks in advance.
Jake
Use the exchangetype of uploadonly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Jake" <noreply@.nowhere.com> wrote in message
news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a question on the subscriptions and type of replication to use.
> We have about 5 subscriptions to a published db setup as merge replication
> via the websync on sql2k5. We would actually like the subscriptions (all
> running sql express) to just download changes to the publisher. We do not
> need the publisher to pass any data up to the subscribers. Is there a way
> to do this? We need to use the functionality of web synchronization, if at
> all possible. Thanks in advance.
> Jake
>
|||Hilary,
Where would I add this script setting in SQL2k5? Since the clients are
sql express there really isn't a scheduled agent to run the replication,
we're using a .net app to start the replication process. Prior to this we
are running the following scripts to setup replication. Any further
clarification would be appreciated. Thanks in advance.
--BEGIN: Script to be run at Subscriber FOR
WAN--
sp_addmergepullsubscription
@.publication = 'pub_subscriber_db', --the name given to the publication
during the creation of the publication via the wizard
@.publisher_db = 'subscriber_db', --the name of the actual database running
on the server instance
@.publisher = 'computer-db', --the name of the server instance
@.subscriber_type = 'global',
@.sync_type='automatic'
--BEGIN: Script to be run at Publisher FOR
WAN--
use [subscriber_db]
exec sp_addmergesubscription @.publication = N'pub_wcpc_arizona',
@.subscriber = N'subscriber-01\subscriber',
@.subscriber_db = N'subscriber_db',
@.subscription_type = N'pull',
@.subscriber_type = N'global',
@.subscription_priority = 0,
@.sync_type = N'Automatic'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23PnLtc3RGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Use the exchangetype of uploadonly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Jake" <noreply@.nowhere.com> wrote in message
> news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||Jake,
in SQL Server 2005 there is the @.subscriber_upload_options parameter of
sp_addmergearticle which takes the following values:
0 : No restrictions. Changes made at the Subscriber are uploaded to the
Publisher.
1 : Changes are allowed at the Subscriber, but they are not uploaded to the
Publisher.
2 : Changes are not allowed at the Subscriber
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Is there a way to have changes made on the subscriber downloaded the the
publisher, but additions on the publisher are not uploaded to the
subscriber?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
Let me re-phrase.
Scenario:
We have two (actually more but there shouldn't be a difference between two
or more) subscribers and want their data to download to the publisher, but
we do not want subscriber 1 data to be uploaded to subscriber 2 if
subscriber 2 replicates after subscriber 1 has.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jake,
you could possibly modift the exchangetype parameter on the commandline
dynamically when initiating a pull subscription. However I'd be afraid that
the data will soon become corrupt this way. Merge is really designed for
data to be fully partitioned amongst all the nodes involved. Partitioning is
ok, and not replicating deletes to a subscriber is also ok - but what you
are suggesting could lead to a complete spaghetti of data. Perhaps you could
explain further the business requirements and we might arrive at a different
way of realising them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the responses so far. I think we have it. We're going to add
a column for the workstation and use filter rows with the host_name. If I am
correct this will only replicate the items for that subscriber to the
publisher, back and forth. Is this correct?
Also is there a way to limit the amount of RAM that replication can use? We
have a couple of subscribers, using sql express with little RAM and are
running out of VM. My thought is if we can limit the RAM being used it will
avoid this issue, but increase the amount of time it takes to replicate.
What are your thoughts?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSj%23pL4RGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Jake,
> you could possibly modift the exchangetype parameter on the commandline
> dynamically when initiating a pull subscription. However I'd be afraid
> that the data will soon become corrupt this way. Merge is really designed
> for data to be fully partitioned amongst all the nodes involved.
> Partitioning is ok, and not replicating deletes to a subscriber is also
> ok - but what you are suggesting could lead to a complete spaghetti of
> data. Perhaps you could explain further the business requirements and we
> might arrive at a different way of realising them.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Jake,
for the first part, you could use dynamic filtering, but if you only have a
few subscribers, a separate publication for each subscriber works better.
Limiting the RAM for the replication exe - interesting. To be honest, I
don't know how this can be done. I'll look into it though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the help so far. If you find a way to limit the Ram I'd be
very appreciative.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23mgw854RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Jake,
> for the first part, you could use dynamic filtering, but if you only have
> a few subscribers, a separate publication for each subscriber works
> better. Limiting the RAM for the replication exe - interesting. To be
> honest, I don't know how this can be done. I'll look into it though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
I have a question on the subscriptions and type of replication to use.
We have about 5 subscriptions to a published db setup as merge replication
via the websync on sql2k5. We would actually like the subscriptions (all
running sql express) to just download changes to the publisher. We do not
need the publisher to pass any data up to the subscribers. Is there a way to
do this? We need to use the functionality of web synchronization, if at all
possible. Thanks in advance.
Jake
Use the exchangetype of uploadonly.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Jake" <noreply@.nowhere.com> wrote in message
news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a question on the subscriptions and type of replication to use.
> We have about 5 subscriptions to a published db setup as merge replication
> via the websync on sql2k5. We would actually like the subscriptions (all
> running sql express) to just download changes to the publisher. We do not
> need the publisher to pass any data up to the subscribers. Is there a way
> to do this? We need to use the functionality of web synchronization, if at
> all possible. Thanks in advance.
> Jake
>
|||Hilary,
Where would I add this script setting in SQL2k5? Since the clients are
sql express there really isn't a scheduled agent to run the replication,
we're using a .net app to start the replication process. Prior to this we
are running the following scripts to setup replication. Any further
clarification would be appreciated. Thanks in advance.
--BEGIN: Script to be run at Subscriber FOR
WAN--
sp_addmergepullsubscription
@.publication = 'pub_subscriber_db', --the name given to the publication
during the creation of the publication via the wizard
@.publisher_db = 'subscriber_db', --the name of the actual database running
on the server instance
@.publisher = 'computer-db', --the name of the server instance
@.subscriber_type = 'global',
@.sync_type='automatic'
--BEGIN: Script to be run at Publisher FOR
WAN--
use [subscriber_db]
exec sp_addmergesubscription @.publication = N'pub_wcpc_arizona',
@.subscriber = N'subscriber-01\subscriber',
@.subscriber_db = N'subscriber_db',
@.subscription_type = N'pull',
@.subscriber_type = N'global',
@.subscription_priority = 0,
@.sync_type = N'Automatic'
GO
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23PnLtc3RGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Use the exchangetype of uploadonly.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Jake" <noreply@.nowhere.com> wrote in message
> news:uOdUdXyRGHA.3052@.TK2MSFTNGP09.phx.gbl...
>
|||Jake,
in SQL Server 2005 there is the @.subscriber_upload_options parameter of
sp_addmergearticle which takes the following values:
0 : No restrictions. Changes made at the Subscriber are uploaded to the
Publisher.
1 : Changes are allowed at the Subscriber, but they are not uploaded to the
Publisher.
2 : Changes are not allowed at the Subscriber
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Is there a way to have changes made on the subscriber downloaded the the
publisher, but additions on the publisher are not uploaded to the
subscriber?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Paul,
Let me re-phrase.
Scenario:
We have two (actually more but there shouldn't be a difference between two
or more) subscribers and want their data to download to the publisher, but
we do not want subscriber 1 data to be uploaded to subscriber 2 if
subscriber 2 replicates after subscriber 1 has.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23bIDV73RGHA.2300@.TK2MSFTNGP11.phx.gbl...
> Jake,
> in SQL Server 2005 there is the @.subscriber_upload_options parameter of
> sp_addmergearticle which takes the following values:
> 0 : No restrictions. Changes made at the Subscriber are uploaded to the
> Publisher.
> 1 : Changes are allowed at the Subscriber, but they are not uploaded to
> the Publisher.
> 2 : Changes are not allowed at the Subscriber
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Jake,
you could possibly modift the exchangetype parameter on the commandline
dynamically when initiating a pull subscription. However I'd be afraid that
the data will soon become corrupt this way. Merge is really designed for
data to be fully partitioned amongst all the nodes involved. Partitioning is
ok, and not replicating deletes to a subscriber is also ok - but what you
are suggesting could lead to a complete spaghetti of data. Perhaps you could
explain further the business requirements and we might arrive at a different
way of realising them.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the responses so far. I think we have it. We're going to add
a column for the workstation and use filter rows with the host_name. If I am
correct this will only replicate the items for that subscriber to the
publisher, back and forth. Is this correct?
Also is there a way to limit the amount of RAM that replication can use? We
have a couple of subscribers, using sql express with little RAM and are
running out of VM. My thought is if we can limit the RAM being used it will
avoid this issue, but increase the amount of time it takes to replicate.
What are your thoughts?
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSj%23pL4RGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Jake,
> you could possibly modift the exchangetype parameter on the commandline
> dynamically when initiating a pull subscription. However I'd be afraid
> that the data will soon become corrupt this way. Merge is really designed
> for data to be fully partitioned amongst all the nodes involved.
> Partitioning is ok, and not replicating deletes to a subscriber is also
> ok - but what you are suggesting could lead to a complete spaghetti of
> data. Perhaps you could explain further the business requirements and we
> might arrive at a different way of realising them.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Jake,
for the first part, you could use dynamic filtering, but if you only have a
few subscribers, a separate publication for each subscriber works better.
Limiting the RAM for the replication exe - interesting. To be honest, I
don't know how this can be done. I'll look into it though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the help so far. If you find a way to limit the Ram I'd be
very appreciative.
Jake
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23mgw854RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Jake,
> for the first part, you could use dynamic filtering, but if you only have
> a few subscribers, a separate publication for each subscriber works
> better. Limiting the RAM for the replication exe - interesting. To be
> honest, I don't know how this can be done. I'll look into it though.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
question with trigger
i have a table which has columns scheduledrent,actualrent beside other columns.
the actual rent is based on some calculations but uses scheduledrent for the calculations.
the actual rent is based on some calculations but uses scheduledrent for the calculations.
everytime there is a change to scheduledrent, the actualrent should also change. so if i write a trigger saying
if updated(scheduledrent)
do some calculations and change the actualrent..using an update stmt.
would it make the change only for that row or for all the rows in the table? if it does it for all the rows, how do i make it apply only to that row. how should i have my update stmt in such a case. my primarykey is a combination of 4 rows. so can i get all the 4 values into some variables and while i do the update do it on that condition ? or is there any easier way of doing it.
thanks.
D.Inside the trigger code you have access to a logical table named "inserted". You can run your update logic with that table and it will only update the rows that were updated. The logical table "inserted" is available for update triggers and, naturally enough, insert triggers.
The logical table "deleted" is available for delete triggers and shows the records to be deleted. In an update trigger the "deleted" table shows the original values before they are to be changed.|||ok it works if i change it to inserted.
have one q though. i have 10 ppl accessing the db at any time. so if all of them make an update to the db (not to the same record though) each one will have an entry in the logical inserted table. so how would the system differentiate between them, since they all login to sql server through the same asp.net account.
thanks for the tip.|||The inserted logical table will only be for the record(s) that triggered that specific update. So everyone will have their own inserted logical tables and won't be stepping on one another.|||oh so sql server creates an inserted table for each user ?
but how does the sql server differentiate between users ?
lets say userA logged onto mysite and made an update to a row. the row gets inserted into the inserted table. userB also logs in at the same time and makes an update to another row. the new row also gets into the inserted table. now both these users are logged into the sqlserver through the same ASP.NET account. so how does sql server differentiate betwen userA and userB and their updated rows?
thanks again.|||It's a "logical" table meaning that it acts like a table but isn't really one. It is there to allow the trigger code to inspect the state of the old and new records so that the trigger code can take whatever action is appropriate. The logical table inside one trigger event isn't visible or accessible to the logical table in any other trigger event, even for the same user logged on multiple times simultaneously updating different record(s).|||i dont think i have understood completely. do you know any article that xplains in detail how the inserted table actually works...?
thanks anyway. will leave it for now. hope it works. will get back incase i have any prb.
thanks McMurdoStation.sql
Subscribe to:
Posts (Atom)