Snapshot Agent will not start

Question

Snapshot Agent will not start

Conan The Canadian

Ten Centuries

Points: 1335
More actions
August 17, 2011 at 11:34 am

#246370

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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

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.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Conan The Canadian Ten Centuries Points: 1335 More actions · Answer 2

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

Any errors in the SQL Log or event log on publisher or distributor?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Conan The Canadian Ten Centuries Points: 1335 More actions · Answer 4

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 5

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

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Conan The Canadian Ten Centuries Points: 1335 More actions · Answer 6

SA for both the source and destination databases.

(I really appreciate the help!)

Kev -=Conan The Canadian=-
@ConanTheCdn

Conan The Canadian Ten Centuries Points: 1335 More actions · Answer 7

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

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

Interesting. You can add the switches to the job directly as well.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 9

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! 🙂

My blog

Andy Steinke Mr or Mrs. 500 Points: 565 More actions · Answer 10

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!)

thepotplants Hall of Fame Points: 3079 More actions · Answer 11

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.

Andy Steinke Mr or Mrs. 500 Points: 565 More actions · Answer 12

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...