Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are having to redo a publication/subscription and while running the snapshot agent it stalls at 48% with the message "The process is running and is waiting on a response from the server." Eventually it times out. I increased the timeout, but even after 4 hours it's still stuck at the same place.

I can't figure out what it's 'waiting on' or how to get it to progress further. I've tried deleting and recreating the publication from scratch but same results. Anyone have ideas on how I might troubleshoot it to figure out what the issue is?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Check the history of the agent job for the Snapshot agent on Whatever server is running as the Distributor. Drill down into the history of the agent job and you should find an error. (The error is often listed in the step preceeding the one that is retrying). Will probably be lack of space, etc.

If this does not reveal anything useful then you may need to switch on verbose logging to try and get a better error of the root cause. Its a combination of changing the agent profile and configuring a logging location external to SQL Server. These posts should get you there :-

http://stackoverflow.com/questions/1724322/where-is-the-log-file-located-when-verbose-history-logging-is-turned-on-for-sql

http://blogs.msdn.com/b/repltalk/archive/2010/03/17/troubleshooting-snapshot-agent-hang.aspx

share|improve this answer
    
Thanks. When you say you check the status, I assume you are talking right clicking the publication and clicking 'View Snapshot Agent Status'. When I do that on the distributor (which is same as publisher) I see the error message I mentioned in the post. Beyond that I didn't see anything in logs so am checking out the links you gave on how to enable verbose logging. –  BVernon Mar 11 at 19:45
    
Yes the preceding job STEP history. I have changed the answer to the proper SQL Server nomenclature now. Did you check the details of the snapshot job in Replication Monitor? right click snapshot agent, show details... You should see it running through all of the tables until it gets stuck on your particular object.. sorry doing this from memory as do not have a Replication environment in front of me at the moment. –  DamagedGoods Mar 12 at 12:20
    
Ah, I wasn't looking at the right log. I'm not sure whether it would have shown me anything useful or not that I didn't see as I actively monitored the progress, but ultimately we ended up rebuilding the publication/subscription and archiving about 40% of the data that was old and then it finally worked. Unfortunately since the snapshot job was recreated in the process I don't think I can go back and look at the history for the original job now. Marking this the answer. –  BVernon Mar 13 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.