August 17, 2011 at 11:34 am
I'm working on a 2008 R2 Enterprise SQL Server.
There are other working transactional replication publications working on this server.
The distributer sql server is also a 2008 R2 Enterprise SQL Server.
Today I created a new Publication, added a subscriber (going to a different instance) and the snapshot agent will not start.
Here are the errors that it gives me:
Error messages:
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed
Here are the details from the job:
Message
The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed.
Executed as user: (bob). Agent shutdown. For more information, see the SQL Server Agent job history for job (bob's job). [SQLSTATE 01000] (Message 20557) Replication-Replication Snapshot Subsystem: agent (bob)failed. The replication agent encountered a failure. See the previous job step history message or Replication Monitor for more information. The step failed. [SQLSTATE 42000] (Error 14151). The step failed.
I have verified that the security is the same as the rest of the replication and the SQL Account is a db_owner of the destination and source databases.
Thoughts?
Kev -=Conan The Canadian=-
@ConanTheCdn
August 17, 2011 at 12:13 pm
Look in replication monitor to see if you see a better error. You could also try starting the agent from the command line to see if you get a better error.
August 17, 2011 at 12:18 pm
Robert Davis (8/17/2011)
Look in replication monitor to see if you see a better error. You could also try starting the agent from the command line to see if you get a better error.
The error in Replication monitor is the same as the Job's history.
I ran EXEC sp_startpublication_snapshot with the publication name, it says it started successfully, but when I check in Replication Monitor it failed right away still with the same errors.
On a different server, restarting the job agent fixed it, but no dice on this one.
Kev -=Conan The Canadian=-
@ConanTheCdn
August 17, 2011 at 12:25 pm
Any errors in the SQL Log or event log on publisher or distributor?
August 17, 2011 at 12:36 pm
Robert Davis (8/17/2011)
Any errors in the SQL Log or event log on publisher or distributor?
Nothing in the Publisher, but there was in the Distributor. I got the same error messages as above but one new one that gives an error number:
Error: 14151, Severity: 18, State: 1.
Poking around the internet, I keep seeing posts about verifying the permissions. I don't see how that'd be an issue seeing how other publications on the same publisher/distributer are working.
Kev -=Conan The Canadian=-
@ConanTheCdn
August 17, 2011 at 12:48 pm
Just a hunch, but verify that the database has a valid owner. If the owner is not valid, you can get weird issues with permissions.
Select d.name, sp.name
From sys.databases d
Left Join sys.server_principals sp On sp.sid = d.owner_sid
August 17, 2011 at 12:54 pm
SA for both the source and destination databases.
(I really appreciate the help!)
Kev -=Conan The Canadian=-
@ConanTheCdn
August 17, 2011 at 1:41 pm
So by command line, I thought you mean with the tsql...well I think I was wrong.
I was looking in BOL about the error and it gave a couple of switchs to use with running the snapshot from command line. So ran it with the -OutputVerbostLevel set to 2 and it worked just fine and generated the snapshot.
Have I mentioned lately how much I love replication? 🙂
Kev -=Conan The Canadian=-
@ConanTheCdn
August 17, 2011 at 2:13 pm
Interesting. You can add the switches to the job directly as well.
August 17, 2011 at 2:16 pm
Conan The Canadian (8/17/2011)
So by command line, I thought you mean with the tsql...well I think I was wrong.I was looking in BOL about the error and it gave a couple of switchs to use with running the snapshot from command line. So ran it with the -OutputVerbostLevel set to 2 and it worked just fine and generated the snapshot.
Have I mentioned lately how much I love replication? 🙂
So when you make sure you could get a good error, it worked! 🙂
July 31, 2013 at 11:58 am
Kevin, I want to say thank you! (Kevin and I were co workers for a time, in fact at the time he wrote this post!). I am running into the same issue and didn't think of the command line option. I'm actually getting errors from running it in verbose mode, but I can't see where I would have found this out any other way. Running snapshot agents from the command line should be in every DBA's bag of tricks (at least those of us dealing with replication all of the time!)
May 8, 2014 at 10:59 pm
Great thread.
This gave me the hint to run the executable from the command line. Took me a while to find it and figure it out but I got there.
For others also struggling, here is the MS guide for trouble shooting using command line: http://technet.microsoft.com/en-us/library/ms151872%28v=sql.105%29.aspx
There's examples: http://technet.microsoft.com/en-us/library/ms147886%28v=sql.105%29.aspx and even detailed info on switch options: technet.microsoft.com/en-us/library/ms146939%28v=sql.105%29.aspx
I had to hack mine around until it worked. As I was using a remote distributor. My advice is to remove the variables and just type the parameters into the script directly.
November 15, 2015 at 1:24 pm
Someday I would like to learn to remember that I have been here before....googling this same situation, seeing I encountered it 2 years before and that two years before that Kevin had run into it. The gray matter goes first...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply