Snapshot Agent will not start

  • 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

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • SA for both the source and destination databases.

    (I really appreciate the help!)

    Kev -=Conan The Canadian=-
    @ConanTheCdn

  • 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

  • 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]

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

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

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

  • 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