June 6, 2014 at 7:59 am
Hello,
We have setup a replication in sql 2008 and our replication is quite different.
Publisher is SQL server, distribution database is in publisher instance and subscriber is unknown. We have configured replication through informatica power exchange.
Could anyone please suggest me how to deal with unsubscribe high count issue.
Thanks in advance
June 8, 2014 at 8:15 am
You need to determine possible causes of the high count... It could be you've got to much going on on the publisher/distributor (you infer that these are on the same server - not typically a good idea for "busy" envionrments)
- is your subscriber resources maxed out?
- are you replicating a table/tables that have constantly changing data?
What do you see by running the script below at your distributor?SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT
a.publisher_db,
a.article,
ds.article_id,
ds.UndelivCmdsInDistDB,
ds.DelivCmdsInDistDB,
CASE WHEN md.[status] = 1 THEN 'Started'
WHEN md.[status] = 2 THEN 'Succeeded'
WHEN md.[status] = 3 THEN 'In Progress'
WHEN md.[status] = 4 THEN 'Idle'
WHEN md.[status] = 5 THEN 'Retrying'
WHEN md.[status] = 6 THEN 'Failed'
ELSE 'Other'
END [Status],
CASE WHEN md.warning = 0 THEN 'OK'
WHEN md.warning = 1 THEN 'Expired'
WHEN md.warning = 2 THEN 'Latency'
ELSE 'OTHER'
END [Warnings],
md.cur_latency / 60.0 / 60.0 [Latency (min.)] FROM
Distribution.dbo.MSdistribution_status ds JOIN
Distribution.dbo.MSarticles a
ON a.article_id = ds.article_id
JOIN
Distribution.dbo.MSreplication_monitordata md
ON md.agent_id = ds.agent_id
WHERE
UndelivCmdsInDistDB > 0
AND a.publisher_db = 'YOUR PUBLISHED DATABASE'
ORDER BY
a.publisher_db,
UndelivCmdsInDistDB DESC
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 9, 2014 at 7:36 am
MyDoggieJessie (6/8/2014)
You need to determine possible causes of the high count... It could be you've got to much going on on the publisher/distributor (you infer that these are on the same server - not typically a good idea for "busy" envionrments)- is your subscriber resources maxed out?
- are you replicating a table/tables that have constantly changing data?
What do you see by running the script below at your distributor?
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDGO
SELECT
a.publisher_db,
a.article,
ds.article_id,
ds.UndelivCmdsInDistDB,
ds.DelivCmdsInDistDB,
CASE WHEN md.[status] = 1 THEN 'Started'
WHEN md.[status] = 2 THEN 'Succeeded'
WHEN md.[status] = 3 THEN 'In Progress'
WHEN md.[status] = 4 THEN 'Idle'
WHEN md.[status] = 5 THEN 'Retrying'
WHEN md.[status] = 6 THEN 'Failed'
ELSE 'Other'
END [Status],
CASE WHEN md.warning = 0 THEN 'OK'
WHEN md.warning = 1 THEN 'Expired'
WHEN md.warning = 2 THEN 'Latency'
ELSE 'OTHER'
END [Warnings],
md.cur_latency / 60.0 / 60.0 [Latency (min.)] FROM
Distribution.dbo.MSdistribution_status ds JOIN
Distribution.dbo.MSarticles a
ON a.article_id = ds.article_id
JOIN
Distribution.dbo.MSreplication_monitordata md
ON md.agent_id = ds.agent_id
WHERE
UndelivCmdsInDistDB > 0
AND a.publisher_db = 'YOUR PUBLISHED DATABASE'
ORDER BY
a.publisher_db,
UndelivCmdsInDistDB DESC
Thanks very much for the reply...
surprisingly i don't get any rows when i run the above script....does this mean i dont have latency ?
And please see the answers for your questions...
- is your subscriber resources maxed out? No, subscriber is informatica, we have zero visibility...but users complain no issues, no latency and they are seeing real time data.
- are you replicating a table/tables that have constantly changing data? I think so
June 9, 2014 at 7:49 am
Also, Can anyone provide me the script to find out list of commands in distribution that were not replicated to subscriber..
fyi, i do not have subscriber information.....we only need to get this infromation from distributor db.
i have googled a lot about this, but i didnt get much information.....
and i dont understand the result of MSdistribution_status table.
Thanks in advance....
June 9, 2014 at 8:56 am
no rows? Run it when you know it's behind...
You can also try running:
distribution.dbo .sp_replmonitorhelpsubscription @publisher = 'SERVERNAME' , @publication_type = 0
And sp_replcounters
Also check your MSrepl_errors table to see if you've got some stuff going on in there
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 9, 2014 at 9:08 am
Thanks again...
in Idera SQL Dm...we are frequently seeing the following alert and its been active...
2298 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
and i just ran the your script - returned no rows
distribution.dbo .sp_replmonitorhelpsubscription @publisher = 'xxxxx' , @publication_type = 0 - no rows
MSrepl_errors - no rows
June 9, 2014 at 9:11 am
Also when i run sp_replcounters
Fyi, we have 2 publishers in our instance
i get the output as
replicated transaction = 0 (for both db's)
replication rate trans/sec = 66.66 ( for one publisher ) and 838 ( for second publisher )
Replication latency - 4.7 and 3.6
June 10, 2014 at 11:49 pm
Are you absolutely 100% certain you're running this against the proper database and server?
FYI - no worries about having 2 publications, we have about a dozen over here, and about 400 articles
Sounds like you should change your alert setting in SQLDM (by the way we use the same tool)
Just to give you an idea...I've got my unsubscribed transaction count set to 2.5 million 😉
The alert I get daily is
6/10/2014 11:53:11 PM, Unsubscribed Transactions (Count) on NAS2-RDS is Warning.
SQL Server instance NAS2-RDS has 4,776,315 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Man, surely you're not concerned with 2,298 commands being behind???
It's my best guess that because your threshold is set so very low, by the time you actually get a result from your queries, the commands are already caught up.
As a test, set your alert to 250,000 and see how often it fires.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 11, 2014 at 8:15 am
Thank you very much...
I'm 100% sure i ran it on the correct server...btw i ran your queries on distribution database....is that correct ? I dont have access to subscriber, its configured from informatica power exchange.
Do you think changing the threshold might get rid of this issue ? i need to give a detail explanation to my subordinates regarding the alert.....otherwise they will bug mee :w00t:
June 11, 2014 at 8:20 am
Also, do you know is this an issue with replication latency or kind of idera SQLdm bug ?
June 11, 2014 at 8:53 am
Again, 2 thousand commands is nothing to worry about, unless of course you are noticing that for a very long time (like it's still sending you those message after 5-10 minutes). Consider changing your warning/critical values to something a lot higher....like maybe 25,000 and 50,000?
Keep in mind that SQLDM polls your servers every few seconds/minutes and isn't really "real time". I'd recommend you set up an advanced feature for your existing alert:
In your SQLDM alert set up, go to configure alerts, find the Unsubscribed Transactions (Count) alert under the Services Category, click Advanced, then set the "A metric threshold should be exceeded for 5 minutes before an alert is raised"
I have our "warning" threshold set up to 3,000,000 and "Critical" set up for 5,000,000 - with a 5 minute wait before the alert is actually raised and an email sent.
I'm positive this will remedy the situation you are facing. I'm pretty sure that because your alert is set so incredibly low, you're getting the alerts, but by the time you go look, everything's already caught up (giving you a false positive)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 11, 2014 at 10:08 am
Thanks again...
i understand and i'm aware if i change the threshold we can get rid of this alert. But we have't seen this alert previously what concerns me more is why we got this now ? something with the latency ?
June 11, 2014 at 1:40 pm
Could be that someone is running a query against a table, which temporarily blocks the replication commands from being committed at the subscriber(s)
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply