February 18, 2011 at 6:42 am
I am new at the company and was told that updated data is not seeing in the production by replications jobs. I checked tabels are there and is still job is running since January15, and job is set to run SQL Server agent account,how can I find out what is wrong with the replication.
Thank you
February 18, 2011 at 6:46 am
What type of replication is this?
Can you see job history. when the job last ran?
What is the latency between the primary and subsriber?
Run dbcc opentran on publisher db and see for open transactions etc.
M&M
February 18, 2011 at 6:55 am
What type of replication is this?
Transactional
Can you see job history. when the job last ran?
January15
What is the latency between the primary and subsriber?
How can I find that?
Run dbcc opentran on publisher db and see for open transactions etc.
Replicated Transaction Information:
Oldest distributed LSN : (630:3025:7)
Oldest non-distributed LSN : (0:0:0)
February 18, 2011 at 7:29 am
A couple things here. You could set up a trace with the distribution agent job name as a filter in the trace to see if there are any commands coming through. To get the distribution agent name you can run the query that I have on my blog at the bottom of this post on the distributor in the distribution database.
You can also verify that you see the distribution agent running on your subscriber by running;
select * from sysprocesses where program_name like 'YourPublisher%'
Finally, if you are seeing the agent running on the subscriber and things don't look like they are coming through in the trace then you can enable logging. Please refer to THIS LINK for instructions. You will need to collect that data and post that back and hopefully we can figure something out from there.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 18, 2011 at 10:59 am
Thank you, I am trying to run this code:
SELECTmda.name AS distribution_agent,
mp.publication AS publication_name,
mda.publisher_db AS publisher_database,
job_id,
job_step_uid
FROMMSdistribution_agents mda INNER JOIN MSpublications mp
ONmda.publication = CASE
WHEN mp.independent_agent = 1 THEN mp.publication
ELSE 'ALL'
END
AND mda.publisher_db = mp.publisher_db
WHEREmda.publisher_db = 'contentDatabase'
AND mda.name LIKE '%YourPubServer%'
ORDER BY mda.name,
mp.publication
Under which db I need to run under? I get error MSdistribution_agents can't find it
February 18, 2011 at 11:02 am
I ran select * from sysprocesses where program_name like 'YourPublisher%'
and get 0 rows
February 18, 2011 at 11:11 am
You did replace the "YourPublisher" with your publication server name correct? Just making sure.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 18, 2011 at 11:27 am
Sorry, I got 3 rows.
What info I should look at?
February 18, 2011 at 11:35 am
Krasavita (2/18/2011)
Thank you, I am trying to run this code:SELECTmda.name AS distribution_agent,
mp.publication AS publication_name,
mda.publisher_db AS publisher_database,
job_id,
job_step_uid
FROMMSdistribution_agents mda INNER JOIN MSpublications mp
ONmda.publication = CASE
WHEN mp.independent_agent = 1 THEN mp.publication
ELSE 'ALL'
END
AND mda.publisher_db = mp.publisher_db
WHEREmda.publisher_db = 'contentDatabase'
AND mda.name LIKE '%YourPubServer%'
ORDER BY mda.name,
mp.publication
Under which db I need to run under? I get error MSdistribution_agents can't find it
Sorry, I missed this reply. You would run this in the distribution database. This will tell you which agent is for the publication you are looking at. Then you can check to see that it shows up in the list from sysprocesses.
Let me know if you find out it is running and connected to the subscriber.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 18, 2011 at 11:36 am
**Note, you have to change the server name in that script as well. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 18, 2011 at 12:03 pm
Thank you, I ran the code and got 9 items back, what is my next step?
February 18, 2011 at 12:11 pm
You need to pick one of the tables that is not replicating and put that in this query to find out the publication name;
selectsp.name
fromsyspublications sp inner join sysarticles sa
on sp.pubid = sa.pubid
wheresa.name = 'YourTable'
Once you have the publication name you can refer to the previous output to see the distribution agent. Make sure that the specific agent for that publication is running on your subscriber server.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 18, 2011 at 1:16 pm
How would I know it is runnning? I have all the info now
February 18, 2011 at 1:26 pm
Krasavita (2/18/2011)
How would I know it is runnning? I have all the info now
If you see that agent running in sysprocesses on the subscriber then it is running. Next step is to enable the logging via the earlier link that I provided.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 18, 2011 at 2:05 pm
Can you verify that this query returns 0 rows when run in your publication database;
SELECTsp.name AS publication_name,
ss.srvname AS subscriber_name,
sa.name AS table_name
FROMsyssubscriptions ssJOIN sysarticles sa
ON ss.artid = sa.artid
JOIN syspublications sp
ON sa.pubid = sp.pubid
WHEREss.status <> 2
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply