I am using transactional replication for a 100GB database that has 2 nosync
subscribers. The pull subscriptions are segmented into like table groups.
They all use a custom profile for the distribution agents that I tweaked
over time to minimize latecy. In the last month I have noticed that 1 of the
subscriptions always has a higher latency in EM then the others. After
looking into several things, we also found out that there was a
hardware/firmware problem with Dell, so we ahve applied that patch and it
has fixed a number of other problems in the DB, but still not the latency
problem on this one subscription. Some of the other subscriptions have much
bigger tables and data changes, so I am at a loss of why this one is causing
me so many problems. I frequently will have to apply a snapshot of this
subscription because it will get so far behind, and then will slwly build
back up again until another snapshot is required.
Can anyone help?
Here are some of the current configs of the custom profile:
BcpBatchsize: 100000
CommitBatchSize: 1000
CommitBatchThreshold: 5000
PollingInterval:1
Thanks,
Kristy
Well nevermind for now. Seems like it's finally leveling off today after I
redid the subscription last night. All day it's been getting smaller (now
down to 10 minutes and the others being in the seconds); where as before it
would just get bigger and bigger (up to days behind.)
I suspect that in addition to the firmware updates and reindexing some
corrupted indexes have helped. But I am just guessing.
--Kristy
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:eeniL84RGHA.1688@.TK2MSFTNGP11.phx.gbl...
> I am using transactional replication for a 100GB database that has 2
nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of
the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have
much
> bigger tables and data changes, so I am at a loss of why this one is
causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>
Showing posts with label latency. Show all posts
Showing posts with label latency. Show all posts
Monday, March 26, 2012
latency on transaction repl problem again
Well it only took a few days before the latency bounced back up again. I
don't understand why this is happening. There is another server that has the
same subscription that is operating over a WAN, and it doesn't have the same
problems. Neither do any of the other subscriptions.
Please help if can. Will post the original email below.
Thx,
Kristy[vbcol=seagreen]
I am using transactional replication for a 100GB database that has 2 nosync
subscribers. The pull subscriptions are segmented into like table groups.
They all use a custom profile for the distribution agents that I tweaked
over time to minimize latecy. In the last month I have noticed that 1 of the
subscriptions always has a higher latency in EM then the others. After
looking into several things, we also found out that there was a
hardware/firmware problem with Dell, so we ahve applied that patch and it
has fixed a number of other problems in the DB, but still not the latency
problem on this one subscription. Some of the other subscriptions have much
bigger tables and data changes, so I am at a loss of why this one is causing
me so many problems. I frequently will have to apply a snapshot of this
subscription because it will get so far behind, and then will slwly build
back up again until another snapshot is required.
Can anyone help?
Here are some of the current configs of the custom profile:
BcpBatchsize: 100000
CommitBatchSize: 1000
CommitBatchThreshold: 5000
PollingInterval:1
Thanks,
Kristy
Kristy. Sorry but I write to you for your help, probably not as you expected.
You mentioned in you have 2 nosync subscribers and that is where I am having
problems. Questions for you are: 1) do you create subscription prior to
restoring or another way around? 2) If you create suscription first, what is
the database for the subscription? This is where I am having problems. I use
the instructions of 'How to manually synchronize replication subscriptions by
using backup or restore', article id 320499. Thanks for your helps and sorry
I am not able to help you out.
"Kristy" wrote:
> Well it only took a few days before the latency bounced back up again. I
> don't understand why this is happening. There is another server that has the
> same subscription that is operating over a WAN, and it doesn't have the same
> problems. Neither do any of the other subscriptions.
> Please help if can. Will post the original email below.
> Thx,
> Kristy
> I am using transactional replication for a 100GB database that has 2 nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have much
> bigger tables and data changes, so I am at a loss of why this one is causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>
>
>
|||Check the history/errors tables for this subscription to try to find out
what is wrong. The tables are in the distribution database and are
msrepl_errors, and msdistribution_history.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:OFVpZbRTGHA.5104@.TK2MSFTNGP10.phx.gbl...
> Well it only took a few days before the latency bounced back up again. I
> don't understand why this is happening. There is another server that has
> the
> same subscription that is operating over a WAN, and it doesn't have the
> same
> problems. Neither do any of the other subscriptions.
> Please help if can. Will post the original email below.
> Thx,
> Kristy
> I am using transactional replication for a 100GB database that has 2
> nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of
> the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have
> much
> bigger tables and data changes, so I am at a loss of why this one is
> causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>
>
|||Kathy,
I have to actually create the pull subscriptions twice on the subscriber.
Once for the publisher to see it initially and then when I restore the
database it removes the pull subscriptions from the subscription DB (because
you are overwriting the tables with the new DB that contains the info.) I
then do the pull subscription again on the subscriber and everything works
fine. Publisher does not seem to get confused or think it is a new
subscription.
HTH
Kristy
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:59B2F52B-7ED1-4111-BA18-02C0DA61722E@.microsoft.com...
> Kristy. Sorry but I write to you for your help, probably not as you
expected.
> You mentioned in you have 2 nosync subscribers and that is where I am
having
> problems. Questions for you are: 1) do you create subscription prior to
> restoring or another way around? 2) If you create suscription first, what
is
> the database for the subscription? This is where I am having problems. I
use
> the instructions of 'How to manually synchronize replication subscriptions
by
> using backup or restore', article id 320499. Thanks for your helps and
sorry[vbcol=seagreen]
> I am not able to help you out.
> "Kristy" wrote:
the[vbcol=seagreen]
same[vbcol=seagreen]
nosync[vbcol=seagreen]
groups.[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
latency[vbcol=seagreen]
much[vbcol=seagreen]
causing[vbcol=seagreen]
build[vbcol=seagreen]
don't understand why this is happening. There is another server that has the
same subscription that is operating over a WAN, and it doesn't have the same
problems. Neither do any of the other subscriptions.
Please help if can. Will post the original email below.
Thx,
Kristy[vbcol=seagreen]
I am using transactional replication for a 100GB database that has 2 nosync
subscribers. The pull subscriptions are segmented into like table groups.
They all use a custom profile for the distribution agents that I tweaked
over time to minimize latecy. In the last month I have noticed that 1 of the
subscriptions always has a higher latency in EM then the others. After
looking into several things, we also found out that there was a
hardware/firmware problem with Dell, so we ahve applied that patch and it
has fixed a number of other problems in the DB, but still not the latency
problem on this one subscription. Some of the other subscriptions have much
bigger tables and data changes, so I am at a loss of why this one is causing
me so many problems. I frequently will have to apply a snapshot of this
subscription because it will get so far behind, and then will slwly build
back up again until another snapshot is required.
Can anyone help?
Here are some of the current configs of the custom profile:
BcpBatchsize: 100000
CommitBatchSize: 1000
CommitBatchThreshold: 5000
PollingInterval:1
Thanks,
Kristy
Kristy. Sorry but I write to you for your help, probably not as you expected.
You mentioned in you have 2 nosync subscribers and that is where I am having
problems. Questions for you are: 1) do you create subscription prior to
restoring or another way around? 2) If you create suscription first, what is
the database for the subscription? This is where I am having problems. I use
the instructions of 'How to manually synchronize replication subscriptions by
using backup or restore', article id 320499. Thanks for your helps and sorry
I am not able to help you out.
"Kristy" wrote:
> Well it only took a few days before the latency bounced back up again. I
> don't understand why this is happening. There is another server that has the
> same subscription that is operating over a WAN, and it doesn't have the same
> problems. Neither do any of the other subscriptions.
> Please help if can. Will post the original email below.
> Thx,
> Kristy
> I am using transactional replication for a 100GB database that has 2 nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have much
> bigger tables and data changes, so I am at a loss of why this one is causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>
>
>
|||Check the history/errors tables for this subscription to try to find out
what is wrong. The tables are in the distribution database and are
msrepl_errors, and msdistribution_history.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:OFVpZbRTGHA.5104@.TK2MSFTNGP10.phx.gbl...
> Well it only took a few days before the latency bounced back up again. I
> don't understand why this is happening. There is another server that has
> the
> same subscription that is operating over a WAN, and it doesn't have the
> same
> problems. Neither do any of the other subscriptions.
> Please help if can. Will post the original email below.
> Thx,
> Kristy
> I am using transactional replication for a 100GB database that has 2
> nosync
> subscribers. The pull subscriptions are segmented into like table groups.
> They all use a custom profile for the distribution agents that I tweaked
> over time to minimize latecy. In the last month I have noticed that 1 of
> the
> subscriptions always has a higher latency in EM then the others. After
> looking into several things, we also found out that there was a
> hardware/firmware problem with Dell, so we ahve applied that patch and it
> has fixed a number of other problems in the DB, but still not the latency
> problem on this one subscription. Some of the other subscriptions have
> much
> bigger tables and data changes, so I am at a loss of why this one is
> causing
> me so many problems. I frequently will have to apply a snapshot of this
> subscription because it will get so far behind, and then will slwly build
> back up again until another snapshot is required.
> Can anyone help?
> Here are some of the current configs of the custom profile:
> BcpBatchsize: 100000
> CommitBatchSize: 1000
> CommitBatchThreshold: 5000
> PollingInterval:1
> Thanks,
> Kristy
>
>
|||Kathy,
I have to actually create the pull subscriptions twice on the subscriber.
Once for the publisher to see it initially and then when I restore the
database it removes the pull subscriptions from the subscription DB (because
you are overwriting the tables with the new DB that contains the info.) I
then do the pull subscription again on the subscriber and everything works
fine. Publisher does not seem to get confused or think it is a new
subscription.
HTH
Kristy
"Kathy" <Kathy@.discussions.microsoft.com> wrote in message
news:59B2F52B-7ED1-4111-BA18-02C0DA61722E@.microsoft.com...
> Kristy. Sorry but I write to you for your help, probably not as you
expected.
> You mentioned in you have 2 nosync subscribers and that is where I am
having
> problems. Questions for you are: 1) do you create subscription prior to
> restoring or another way around? 2) If you create suscription first, what
is
> the database for the subscription? This is where I am having problems. I
use
> the instructions of 'How to manually synchronize replication subscriptions
by
> using backup or restore', article id 320499. Thanks for your helps and
sorry[vbcol=seagreen]
> I am not able to help you out.
> "Kristy" wrote:
the[vbcol=seagreen]
same[vbcol=seagreen]
nosync[vbcol=seagreen]
groups.[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
latency[vbcol=seagreen]
much[vbcol=seagreen]
causing[vbcol=seagreen]
build[vbcol=seagreen]
Latency Issues
I'm seeing latency issues with a single user on MSDE. It appears that
data doesn't get persisted to the database for up to 30 minutes. What the
devil could be causing this? These are all new machines running the latest
service packs.
TIA
Tina
Hi
The application is not committing the data until very late. In a bad case,
where the connection to the server gets lost, SQL Server will rollback the
transaction as it is not committed. You can loose a lot of data though this.
In effect, there is a BEGIN TRANSACTION and somewhere further down the line,
maybe by chance (due to developer luck), there is a COMMIT TRANSACTION,
resulting in the data being committed.
Ask the developer to look at his application.
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/
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:uGNS5ZdFFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm seeing latency issues with a single user on MSDE. It appears that
> data doesn't get persisted to the database for up to 30 minutes. What
the
> devil could be causing this? These are all new machines running the
latest
> service packs.
> TIA
> Tina
>
sql
data doesn't get persisted to the database for up to 30 minutes. What the
devil could be causing this? These are all new machines running the latest
service packs.
TIA
Tina
Hi
The application is not committing the data until very late. In a bad case,
where the connection to the server gets lost, SQL Server will rollback the
transaction as it is not committed. You can loose a lot of data though this.
In effect, there is a BEGIN TRANSACTION and somewhere further down the line,
maybe by chance (due to developer luck), there is a COMMIT TRANSACTION,
resulting in the data being committed.
Ask the developer to look at his application.
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/
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:uGNS5ZdFFHA.2676@.TK2MSFTNGP12.phx.gbl...
> I'm seeing latency issues with a single user on MSDE. It appears that
> data doesn't get persisted to the database for up to 30 minutes. What
the
> devil could be causing this? These are all new machines running the
latest
> service packs.
> TIA
> Tina
>
sql
Latency In Transactional Replication
During last couple of weeks some new Tables & Fields were added. SO both the
time i have to re-intialize the Replication. my question is about LATENCY
where i have seen for both the subscriber(SUB) for SUB-1 is 3.5 Hours &
SUB-2 is 21 hrs. Size of the PUBLISHER DB is 18GB. AS Latency are in
Millisecs i have mentioned in HOURS for better understanding. i want to know
how i can resolve this issue as every time when new table or fields are
added it takes 21-22 hours to complete Replication Re-intialization process.
Any help will be highly appreciated. Thanking you in advance.
Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
changing a PK.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||TWO new Tables were added with unique key for couple of fields & were
not going to be use in Transactional Replication due to limitation that
each table needs to have at least one PK. and this 2 tables as no PK.
So using sp_repladdcolumn will not help, i maybe wrong.
please correct me if i am wrong as i am not SQL expert. again any help
& to correct me will be appreciated.
Paul Ibison wrote:
> Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
> changing a PK.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear Mr. Paul:
I have replied back with details & did see your
feedback. i will appreciate if you please kindly reply me at your
earliest & doing so will be highly appreciated.
thanks
Paul Ibison wrote:
> Now I'm confused
- if you're adding tables to a publication this is
> supported and adding columns to an already published article is supported,
> but this seems to be not relevant to your case somehow. Is the problem that
> you want to add tables which don't have a PK? This is definitely
> unsupported, but you already know that, so please can you add a few more
> details about what it is that you want to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
time i have to re-intialize the Replication. my question is about LATENCY
where i have seen for both the subscriber(SUB) for SUB-1 is 3.5 Hours &
SUB-2 is 21 hrs. Size of the PUBLISHER DB is 18GB. AS Latency are in
Millisecs i have mentioned in HOURS for better understanding. i want to know
how i can resolve this issue as every time when new table or fields are
added it takes 21-22 hours to complete Replication Re-intialization process.
Any help will be highly appreciated. Thanking you in advance.
Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
changing a PK.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||TWO new Tables were added with unique key for couple of fields & were
not going to be use in Transactional Replication due to limitation that
each table needs to have at least one PK. and this 2 tables as no PK.
So using sp_repladdcolumn will not help, i maybe wrong.
please correct me if i am wrong as i am not SQL expert. again any help
& to correct me will be appreciated.
Paul Ibison wrote:
> Use sp_repladdcolumn. There's really no need to reinitialize, unless you are
> changing a PK.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Dear Mr. Paul:
I have replied back with details & did see your
feedback. i will appreciate if you please kindly reply me at your
earliest & doing so will be highly appreciated.
thanks
Paul Ibison wrote:
> Now I'm confused
- if you're adding tables to a publication this is> supported and adding columns to an already published article is supported,
> but this seems to be not relevant to your case somehow. Is the problem that
> you want to add tables which don't have a PK? This is definitely
> unsupported, but you already know that, so please can you add a few more
> details about what it is that you want to do.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
Latency in Replication Monitor
If my publications are not actively receving any changes on the Publishing
end, will the latency increase ? If there are no transactions/commands
being pushed to the subscriber , then how is latency calculated ? I have
setup replication and its been idle for a few days now and the latency shows
7 secs.. Thanks
latency reflects the last transaction/commands replicated. So 7 secs was the
latency of the last delivered transaction/command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23tPZ7s38EHA.3012@.TK2MSFTNGP09.phx.gbl...
> If my publications are not actively receving any changes on the Publishing
> end, will the latency increase ? If there are no transactions/commands
> being pushed to the subscriber , then how is latency calculated ? I have
> setup replication and its been idle for a few days now and the latency
shows
> 7 secs.. Thanks
>
|||I have stopped the distribution agent for a day now and the latency still
shows 7 secs.. Shouldnt it have increased ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uUziGP68EHA.2900@.TK2MSFTNGP09.phx.gbl...
> latency reflects the last transaction/commands replicated. So 7 secs was
the[vbcol=seagreen]
> latency of the last delivered transaction/command.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23tPZ7s38EHA.3012@.TK2MSFTNGP09.phx.gbl...
Publishing
> shows
>
|||No, it will only reflect the new latency when you restart it. Then IIRC, it
will reflect a latency of >>7 days.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eSsxsQD9EHA.1392@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have stopped the distribution agent for a day now and the latency still
> shows 7 secs.. Shouldnt it have increased ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uUziGP68EHA.2900@.TK2MSFTNGP09.phx.gbl...
> the
> Publishing
transactions/commands[vbcol=seagreen]
have
>
|||Hmm.. now what i wanted.. How do you guys prefer to have some replication
monitor alerts.. Im setting up transactional replication and would like to
be alerted if my subscribers are more than 5-10 mins behind my publisher.
Also to note that if the publisher doesnt have any changes to replicate ,
then the alert should not fire.. Please advise
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23WwWYeD9EHA.2196@.TK2MSFTNGP14.phx.gbl...
> No, it will only reflect the new latency when you restart it. Then IIRC,
it[vbcol=seagreen]
> will reflect a latency of >>7 days.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eSsxsQD9EHA.1392@.tk2msftngp13.phx.gbl...
still[vbcol=seagreen]
was[vbcol=seagreen]
> transactions/commands
> have
latency
>
|||I monitor my distribution history tables. If the values in the time column
get beyond a certain point I raise an alert which write the event log. NetIQ
then scrapes the event log and sends out pages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OV6x6uE9EHA.2804@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hmm.. now what i wanted.. How do you guys prefer to have some replication
> monitor alerts.. Im setting up transactional replication and would like to
> be alerted if my subscribers are more than 5-10 mins behind my publisher.
> Also to note that if the publisher doesnt have any changes to replicate ,
> then the alert should not fire.. Please advise
> Thanks
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23WwWYeD9EHA.2196@.TK2MSFTNGP14.phx.gbl...
> it
> still
> was
I
> latency
>
|||Could you send me the sample query that I could use ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eAxUOGQ9EHA.2600@.TK2MSFTNGP09.phx.gbl...
> I monitor my distribution history tables. If the values in the time column
> get beyond a certain point I raise an alert which write the event log.
NetIQ[vbcol=seagreen]
> then scrapes the event log and sends out pages.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OV6x6uE9EHA.2804@.TK2MSFTNGP15.phx.gbl...
replication[vbcol=seagreen]
to[vbcol=seagreen]
publisher.[vbcol=seagreen]
,[vbcol=seagreen]
IIRC,[vbcol=seagreen]
secs[vbcol=seagreen]
?
> I
>
|||Try something like this
DECLARE @.count int
SELECT @.count = COUNT(max_time) FROM
MSdistribution_agents, (SELECT agent_id, max_time=MAX(time) FROM
MSdistribution_history GROUP BY agent_ID ) AS a
WHERE a.agent_id = MSdistribution_agents.id
AND max_time < DATEADD(d,-1,getdate())
IF @.count >0
BEGIN
DECLARE @.SubscriberName sysname
DECLARE expiringSubscriber_cursor CURSOR FOR
SELECT SubscriberName = (SELECT srvname FROM master.dbo.sysservers
WHERE srvid = subscriber_id) FROM
MSdistribution_agents, (SELECT agent_id, max_time = MAX(time) FROM
MSdistribution_history GROUP BY agent_ID ) AS a
WHERE a.agent_id = MSdistribution_agents.id
AND max_time < dateadd(d,-1,getdate())
ORDER BY 1
OPEN expiringSubscriber_cursor
FETCH NEXT FROM expiringSubscriber_cursor
INTO @.SubscriberName
WHILE @.@.FETCH_STATUS = 0
BEGIN
RAISERROR (50001, 19, -1, @.SubscriberName) with log
FETCH NEXT FROM expiringSubscriber_cursor
INTO @.SubscriberName
END
CLOSE expiringSubscriber_cursor
DEALLOCATE expiringSubscriber_cursor
END
Code Sample 10.2 is an example of the alert Code Sample 10.1 uses.
Code Sample 10.2
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'Expiring
Subscriber Alert'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = 'Expiring Subscriber Alert'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = 'Expiring Subscriber Alert',
@.message_id = 50001, @.severity = 0, @.enabled = 1, @.delay_between_responses =
60, @.include_event_description_in = 5, @.event_description_keyword =
'Subscriber %s is expiring', @.category_name = '[Uncategorized]'
END
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1ge68v9EHA.208@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Could you send me the sample query that I could use ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eAxUOGQ9EHA.2600@.TK2MSFTNGP09.phx.gbl...
column[vbcol=seagreen]
> NetIQ
> replication
like[vbcol=seagreen]
> to
> publisher.
replicate[vbcol=seagreen]
> ,
> IIRC,
latency[vbcol=seagreen]
> secs
the[vbcol=seagreen]
calculated
> ?
>
end, will the latency increase ? If there are no transactions/commands
being pushed to the subscriber , then how is latency calculated ? I have
setup replication and its been idle for a few days now and the latency shows
7 secs.. Thanks
latency reflects the last transaction/commands replicated. So 7 secs was the
latency of the last delivered transaction/command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23tPZ7s38EHA.3012@.TK2MSFTNGP09.phx.gbl...
> If my publications are not actively receving any changes on the Publishing
> end, will the latency increase ? If there are no transactions/commands
> being pushed to the subscriber , then how is latency calculated ? I have
> setup replication and its been idle for a few days now and the latency
shows
> 7 secs.. Thanks
>
|||I have stopped the distribution agent for a day now and the latency still
shows 7 secs.. Shouldnt it have increased ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uUziGP68EHA.2900@.TK2MSFTNGP09.phx.gbl...
> latency reflects the last transaction/commands replicated. So 7 secs was
the[vbcol=seagreen]
> latency of the last delivered transaction/command.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23tPZ7s38EHA.3012@.TK2MSFTNGP09.phx.gbl...
Publishing
> shows
>
|||No, it will only reflect the new latency when you restart it. Then IIRC, it
will reflect a latency of >>7 days.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eSsxsQD9EHA.1392@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> I have stopped the distribution agent for a day now and the latency still
> shows 7 secs.. Shouldnt it have increased ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uUziGP68EHA.2900@.TK2MSFTNGP09.phx.gbl...
> the
> Publishing
transactions/commands[vbcol=seagreen]
have
>
|||Hmm.. now what i wanted.. How do you guys prefer to have some replication
monitor alerts.. Im setting up transactional replication and would like to
be alerted if my subscribers are more than 5-10 mins behind my publisher.
Also to note that if the publisher doesnt have any changes to replicate ,
then the alert should not fire.. Please advise
Thanks
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23WwWYeD9EHA.2196@.TK2MSFTNGP14.phx.gbl...
> No, it will only reflect the new latency when you restart it. Then IIRC,
it[vbcol=seagreen]
> will reflect a latency of >>7 days.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eSsxsQD9EHA.1392@.tk2msftngp13.phx.gbl...
still[vbcol=seagreen]
was[vbcol=seagreen]
> transactions/commands
> have
latency
>
|||I monitor my distribution history tables. If the values in the time column
get beyond a certain point I raise an alert which write the event log. NetIQ
then scrapes the event log and sends out pages.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OV6x6uE9EHA.2804@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Hmm.. now what i wanted.. How do you guys prefer to have some replication
> monitor alerts.. Im setting up transactional replication and would like to
> be alerted if my subscribers are more than 5-10 mins behind my publisher.
> Also to note that if the publisher doesnt have any changes to replicate ,
> then the alert should not fire.. Please advise
> Thanks
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23WwWYeD9EHA.2196@.TK2MSFTNGP14.phx.gbl...
> it
> still
> was
I
> latency
>
|||Could you send me the sample query that I could use ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eAxUOGQ9EHA.2600@.TK2MSFTNGP09.phx.gbl...
> I monitor my distribution history tables. If the values in the time column
> get beyond a certain point I raise an alert which write the event log.
NetIQ[vbcol=seagreen]
> then scrapes the event log and sends out pages.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OV6x6uE9EHA.2804@.TK2MSFTNGP15.phx.gbl...
replication[vbcol=seagreen]
to[vbcol=seagreen]
publisher.[vbcol=seagreen]
,[vbcol=seagreen]
IIRC,[vbcol=seagreen]
secs[vbcol=seagreen]
?
> I
>
|||Try something like this
DECLARE @.count int
SELECT @.count = COUNT(max_time) FROM
MSdistribution_agents, (SELECT agent_id, max_time=MAX(time) FROM
MSdistribution_history GROUP BY agent_ID ) AS a
WHERE a.agent_id = MSdistribution_agents.id
AND max_time < DATEADD(d,-1,getdate())
IF @.count >0
BEGIN
DECLARE @.SubscriberName sysname
DECLARE expiringSubscriber_cursor CURSOR FOR
SELECT SubscriberName = (SELECT srvname FROM master.dbo.sysservers
WHERE srvid = subscriber_id) FROM
MSdistribution_agents, (SELECT agent_id, max_time = MAX(time) FROM
MSdistribution_history GROUP BY agent_ID ) AS a
WHERE a.agent_id = MSdistribution_agents.id
AND max_time < dateadd(d,-1,getdate())
ORDER BY 1
OPEN expiringSubscriber_cursor
FETCH NEXT FROM expiringSubscriber_cursor
INTO @.SubscriberName
WHILE @.@.FETCH_STATUS = 0
BEGIN
RAISERROR (50001, 19, -1, @.SubscriberName) with log
FETCH NEXT FROM expiringSubscriber_cursor
INTO @.SubscriberName
END
CLOSE expiringSubscriber_cursor
DEALLOCATE expiringSubscriber_cursor
END
Code Sample 10.2 is an example of the alert Code Sample 10.1 uses.
Code Sample 10.2
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = 'Expiring
Subscriber Alert'))
-- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @.name = 'Expiring Subscriber Alert'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @.name = 'Expiring Subscriber Alert',
@.message_id = 50001, @.severity = 0, @.enabled = 1, @.delay_between_responses =
60, @.include_event_description_in = 5, @.event_description_keyword =
'Subscriber %s is expiring', @.category_name = '[Uncategorized]'
END
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O1ge68v9EHA.208@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Could you send me the sample query that I could use ?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eAxUOGQ9EHA.2600@.TK2MSFTNGP09.phx.gbl...
column[vbcol=seagreen]
> NetIQ
> replication
like[vbcol=seagreen]
> to
> publisher.
replicate[vbcol=seagreen]
> ,
> IIRC,
latency[vbcol=seagreen]
> secs
the[vbcol=seagreen]
calculated
> ?
>
Latency in merge replication
Hi,
I have a merge replication (one publisher/distributor and one subscriber) and I am experiencing latency problems. When I modify some data in one of this servers, it takes about 40 secs to replicate this data. Is it possible decrease this time? I mean, 2 o
r 3 secs would be good.
Thank you
Marcos,
you can decrease the -PollingInterval if you are running continuously, which
makes a big difference. Other ways to optimise are listed in these articles:
http://www.microsoft.com/technet/pro.../mergperf.mspx
http://www.sql-server-performance.co...ion_tuning.asp
HTH,
Paul Ibison
"Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> Hi,
> I have a merge replication (one publisher/distributor and one subscriber)
and I am experiencing latency problems. When I modify some data in one of
this servers, it takes about 40 secs to replicate this data. Is it possible
decrease this time? I mean, 2 or 3 secs would be good.
> Thank you
|||right click on your merge agent, select properties, go to steps, click run
step and a the end of the string type
-PollingInterval 1
This should bring it down considerably, probably within 4s. YMMV
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> Hi,
> I have a merge replication (one publisher/distributor and one subscriber)
and I am experiencing latency problems. When I modify some data in one of
this servers, it takes about 40 secs to replicate this data. Is it possible
decrease this time? I mean, 2 or 3 secs would be good.
> Thank you
|||Paul,
The PollingInterval solved my problem. Thank you.
Marcos
"Paul Ibison" wrote:
> Marcos,
> you can decrease the -PollingInterval if you are running continuously, which
> makes a big difference. Other ways to optimise are listed in these articles:
> http://www.microsoft.com/technet/pro.../mergperf.mspx
> http://www.sql-server-performance.co...ion_tuning.asp
> HTH,
> Paul Ibison
>
> "Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
> message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> and I am experiencing latency problems. When I modify some data in one of
> this servers, it takes about 40 secs to replicate this data. Is it possible
> decrease this time? I mean, 2 or 3 secs would be good.
>
>
|||Hilary,
It was better than I expected. Data is now merged no more than 2s.
Thank you
Marcos
"Hilary Cotter" wrote:
> right click on your merge agent, select properties, go to steps, click run
> step and a the end of the string type
> -PollingInterval 1
> This should bring it down considerably, probably within 4s. YMMV
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
> message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> and I am experiencing latency problems. When I modify some data in one of
> this servers, it takes about 40 secs to replicate this data. Is it possible
> decrease this time? I mean, 2 or 3 secs would be good.
>
>
I have a merge replication (one publisher/distributor and one subscriber) and I am experiencing latency problems. When I modify some data in one of this servers, it takes about 40 secs to replicate this data. Is it possible decrease this time? I mean, 2 o
r 3 secs would be good.
Thank you
Marcos,
you can decrease the -PollingInterval if you are running continuously, which
makes a big difference. Other ways to optimise are listed in these articles:
http://www.microsoft.com/technet/pro.../mergperf.mspx
http://www.sql-server-performance.co...ion_tuning.asp
HTH,
Paul Ibison
"Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> Hi,
> I have a merge replication (one publisher/distributor and one subscriber)
and I am experiencing latency problems. When I modify some data in one of
this servers, it takes about 40 secs to replicate this data. Is it possible
decrease this time? I mean, 2 or 3 secs would be good.
> Thank you
|||right click on your merge agent, select properties, go to steps, click run
step and a the end of the string type
-PollingInterval 1
This should bring it down considerably, probably within 4s. YMMV
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> Hi,
> I have a merge replication (one publisher/distributor and one subscriber)
and I am experiencing latency problems. When I modify some data in one of
this servers, it takes about 40 secs to replicate this data. Is it possible
decrease this time? I mean, 2 or 3 secs would be good.
> Thank you
|||Paul,
The PollingInterval solved my problem. Thank you.
Marcos
"Paul Ibison" wrote:
> Marcos,
> you can decrease the -PollingInterval if you are running continuously, which
> makes a big difference. Other ways to optimise are listed in these articles:
> http://www.microsoft.com/technet/pro.../mergperf.mspx
> http://www.sql-server-performance.co...ion_tuning.asp
> HTH,
> Paul Ibison
>
> "Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
> message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> and I am experiencing latency problems. When I modify some data in one of
> this servers, it takes about 40 secs to replicate this data. Is it possible
> decrease this time? I mean, 2 or 3 secs would be good.
>
>
|||Hilary,
It was better than I expected. Data is now merged no more than 2s.
Thank you
Marcos
"Hilary Cotter" wrote:
> right click on your merge agent, select properties, go to steps, click run
> step and a the end of the string type
> -PollingInterval 1
> This should bring it down considerably, probably within 4s. YMMV
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Marcos Federicce" <Marcos Federicce@.discussions.microsoft.com> wrote in
> message news:AAFE1AE8-732F-4E5C-8EA6-24E1AB33BB18@.microsoft.com...
> and I am experiencing latency problems. When I modify some data in one of
> this servers, it takes about 40 secs to replicate this data. Is it possible
> decrease this time? I mean, 2 or 3 secs would be good.
>
>
latency
Can someone please describe what this is and how I adjust my dist cleanup
agent retention times for it.
I know the basics, but when I go into MSdistribution table there are alot of
fields that seem similiar (ie: current_delivery_rate,
current_delivery_latency, delivery_rate, delivery_latency). Can someone
please explain the differences between these? And also how I can look at
bottlenecks that are increasing the latency and how do I find out my average
latency time for a specified period to valid the improvements?
Thanks,
K
P.S. I just received HC's book, so if someone could direct me to a location
in there, that would be fine too.
Latency is the average amount of time required for a transaction to be
applied on the subscriber after it has been applied on the publisher.
Current_delivery_rate is the Average number of commands delivered per second
since the distribution agent last ran. Current_delivery_latency is the
Latency between the command entering the distribution database and being
applied to the Subscriber since the last history entry.
If your Distribution agent runs in intervals or is scheduled latency can be
lengthy.
delivery_rate is average commands per second.
Recall that a transaction applied on the publisher is broken down to
singletons. So that a transaction which updates 100 rows will be transformed
into 100 update commands. So if you have a lot of singleton transactions
your delivery rate will be higher than if you have a lot of batch processes
which will mean a higher number of commands and a lower delivery rate.
Delivery_latency is Latency between the command entering the distribution
database and being applied to the Subscriber.
So, the lower your polling interval and if your distribution agent is
running continuously these values should be the same.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:Ocmi8LBJFHA.3376@.TK2MSFTNGP14.phx.gbl...
> Can someone please describe what this is and how I adjust my dist cleanup
> agent retention times for it.
> I know the basics, but when I go into MSdistribution table there are alot
of
> fields that seem similiar (ie: current_delivery_rate,
> current_delivery_latency, delivery_rate, delivery_latency). Can someone
> please explain the differences between these? And also how I can look at
> bottlenecks that are increasing the latency and how do I find out my
average
> latency time for a specified period to valid the improvements?
> Thanks,
> K
> P.S. I just received HC's book, so if someone could direct me to a
location
> in there, that would be fine too.
>
agent retention times for it.
I know the basics, but when I go into MSdistribution table there are alot of
fields that seem similiar (ie: current_delivery_rate,
current_delivery_latency, delivery_rate, delivery_latency). Can someone
please explain the differences between these? And also how I can look at
bottlenecks that are increasing the latency and how do I find out my average
latency time for a specified period to valid the improvements?
Thanks,
K
P.S. I just received HC's book, so if someone could direct me to a location
in there, that would be fine too.
Latency is the average amount of time required for a transaction to be
applied on the subscriber after it has been applied on the publisher.
Current_delivery_rate is the Average number of commands delivered per second
since the distribution agent last ran. Current_delivery_latency is the
Latency between the command entering the distribution database and being
applied to the Subscriber since the last history entry.
If your Distribution agent runs in intervals or is scheduled latency can be
lengthy.
delivery_rate is average commands per second.
Recall that a transaction applied on the publisher is broken down to
singletons. So that a transaction which updates 100 rows will be transformed
into 100 update commands. So if you have a lot of singleton transactions
your delivery rate will be higher than if you have a lot of batch processes
which will mean a higher number of commands and a lower delivery rate.
Delivery_latency is Latency between the command entering the distribution
database and being applied to the Subscriber.
So, the lower your polling interval and if your distribution agent is
running continuously these values should be the same.
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
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:Ocmi8LBJFHA.3376@.TK2MSFTNGP14.phx.gbl...
> Can someone please describe what this is and how I adjust my dist cleanup
> agent retention times for it.
> I know the basics, but when I go into MSdistribution table there are alot
of
> fields that seem similiar (ie: current_delivery_rate,
> current_delivery_latency, delivery_rate, delivery_latency). Can someone
> please explain the differences between these? And also how I can look at
> bottlenecks that are increasing the latency and how do I find out my
average
> latency time for a specified period to valid the improvements?
> Thanks,
> K
> P.S. I just received HC's book, so if someone could direct me to a
location
> in there, that would be fine too.
>
Subscribe to:
Posts (Atom)