November 18, 2011 at 6:15 am
Hello Guys,
Actually I have been with problem with Replication in SQL Server and I do know how can I fix it:
Replication-Replication Snapshot Subsystem: agent failure PMFAMCSCTAHSTBD-PEPSIGROWTH-Pub_PEPSIGROWTH_TBLS-12. The replication agent encountered an exception.
Origem: Unknown
Type of Exception: Microsoft.SqlServer.Management.Smo.FailedOperationException
Message of Exception: Perform pre-fetching of objects failed for Database 'PEPSIGROWTH'.
Post Code:: Not App
Event ID: 14151
November 18, 2011 at 8:07 am
There could be two reasons for this error.
1. Memory issue. If your server has low memory then this error can happen
2. Deadlocks. When this step is done, it locks the table. (Attempts to lock table) This can cause dead locks. Check if there are any dead locks.
-Roy
November 18, 2011 at 8:20 am
Dear,
Thank you, but the memory is 50%, thus, may a good...
and about the deadlock, I think is difficult, just because the instance of database is not much accessed... and It's happened at midnight...
November 22, 2011 at 1:19 am
Hi,
How many tables in the publication?
What type of replication ?
What accounts are you using for your agents ?
Any server configuration that is specific to your company ??
Thanks
Graeme
November 22, 2011 at 4:14 am
How many tables in the publication?
43
What type of replication ?
Transactional
What accounts are you using for your agents ?
sqljob / sysadmin
Any server configuration that is specific to your company ??
I don't understand...
November 22, 2011 at 4:20 am
Ok..
So the Snapshot is failing..is that correct ??
Are you running this as a SQL job or some other way?
Can you run it manually ?
Thanks
Graeme
November 22, 2011 at 4:25 am
So the Snapshot is failing..is that correct ??
I've snapshot and transactional. The snapshot is SQLJOB/SYSADMIN(too) and 'I Guess' to be correct... this error always happened only midnight..
Are you running this as a SQL job or some other way?
I running only to replication.
Can you run it manually ?
I was try, but the sql server said: recused because this job is running for other schedule...
November 22, 2011 at 4:38 am
So you've set up your replication and you now see replication jobs within the agent..is that correct ?
One should be a snapshot agent job. For your transactional replication, I'm guessing you will have removed the schedule as you don't want this running more than once.
If I understand you, the snapshot agent is failing.
Couple of options
1.
So can you run a trace on the sql server to see what is going on at midnight ?
2
Can you run observe the agent running at midnight, you can add some logging to the job step and save the output to a text file..late night ?
3.
can you manually run the agent at midnight..late night ?
Is this a production server or do have a little freedom to run the agent during the day ??
What you need to do is eliminate possibilities and means a little troubleshooting I'm afraid 🙂
HTH
Graeme
November 22, 2011 at 4:55 am
So you've set up your replication and you now see replication jobs within the agent..is that correct ?
I've been this error may a 3 months ago...
I'm guessing you will have removed the schedule as you don't want this running more than once.
why?
So can you run a trace on the sql server to see what is going on at midnight ?
yes, I will, but for you, what kind of trace you think is good to capture? deadlock? miss?
Can you run observe the agent running at midnight, you can add some logging to the job step and save the output to a text file..late night ?
add some logging to the job step? I dont understand that.
can you manually run the agent at midnight..late night ?
may a not, just because exist another a job in running...
Is this a production server or do have a little freedom to run the agent during the day ??
production server
but during the day, I've never received with this error.. only midnight, do you think is network? latency? I've been using alert to error (replication: agent error), but I not received any message of Agent about this error at midnight..
November 22, 2011 at 5:03 am
For transactional replication, the snapshot is only applied once to sync with the subscriber.
Are you saying that you have transactional replication configured so that the snapshot agent runs every night ??:unsure:
You can add logging to the step in the job and set a location to save the text file...check with BOL.
Typically with replication I have found that running the snapshot agent is the least problematic. Are your db files located on a SAN or locally on the server. Is your distrbution db on the same server as the publisher ??
Graeme
November 22, 2011 at 5:09 am
Are you saying that you have transactional replication configured so that the snapshot agent runs every night ??
no, this error happenned always at midnight... but during the day, never happenned..
Are your db files located on a SAN or locally on the server.
locally.
look:
SERVER1
Publisher
SERVER2
Subscriber
The error is show in SERVER1
Is your distrbution db on the same server as the publisher ??
maybe, how can I know?
November 22, 2011 at 5:13 am
Sorry I'm a little confused.
You are using Transactional replication..is that correct ?
What is failing at midnight ?
Is it the distribution agent or the snapshot agent ?
Can you tell me where the distibution database is located ?
November 22, 2011 at 5:18 am
You are using Transactional replication..is that correct ?
also...
What is failing at midnight ?
Transactional replication and Snapshot replication.
Is it the distribution agent or the snapshot agent ?
dont understand.
Can you tell me where the distibution database is located ?
SERVER01
M:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
November 22, 2011 at 7:01 am
Hi,
I think you need to read a little bit more about replication. Especially if you are supporting a production server.
Your answers to my questions suggest a very limited knowledge.
My advice. Be very careful making changes to your server 🙂
Start with BOL and there are some good replication books out there
Graeme
November 28, 2011 at 6:19 am
Graeme100 (11/22/2011)
Hi,I think you need to read a little bit more about replication. Especially if you are supporting a production server.
Your answers to my questions suggest a very limited knowledge.
My advice. Be very careful making changes to your server 🙂
Start with BOL and there are some good replication books out there
Graeme
I tend to agree with Graeme. Please go through the replication configuration on Books Online. Key things which you need to know:
1. Read about what is transactional replication & how it works.
2. Read about the Snapshot agent, distribution agent & log reader agent in detail
3. What is distribution database & what is pull & push replication
4. Replication monitor
Then you will be able to monitor the replication efficiently.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply