We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL Server 2005 tranactional replication

shageman
shageman asked
on
Medium Priority
473 Views
Last Modified: 2012-06-21
I have become totally frustrated trying to setup a transactional replication between 2 SQL servers.  Currently, I have the publication pushing to the secondary machine with only 1 small table.  When I view the Relication Monitor, the message "The concurrent snapshot for publication 'trsmain' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the publication."  That's as far as I can get.  I have rebooted both servers multiple times with the same results.

Where am I going wrong?
Comment
Watch Question

Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
Transactional replication needs a snapshot of the article as a starting point for the subscription (which it can then apply the transactions to). It sounds like your snapshot hasn't been made correctly. Can you go into your publication and tell it to make a new snapshot?

Rob
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Kindly take a Full backup of your database followed by a transactional log backup in your publisher and then apply it in Subscription in the same order to make it work.
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
So...

On the publisher, go to the Local Publications folder (under Replication), and right-click on the publication you're after. Choose "Reinitialize all subscriptions".

You then get a dialog box that includes an option to "Use a new snapshot". Choose this one, and tick the "Generate the new snapshot now" checkbox too. Then hit the "Mark for reinitialization" button, and see how it goes.

Alternatively, open the Replication Monitor. Drill down to the publication you're after, right-click on it and choose "Generate snapshot".

Either of these methods should generate a snapshot of the table being replicated, so that the subscriber can pick it up and apply it.

You should also be able to right-click on the publication and choose "View snapshot agent status" to see if it's reporting any errors.

Rob
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
rrjegan17,

This isn't mirroring, it's replication. A snapshot of the article being replicated is what's required, not a full database backup.

Rob
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Oops.. Typed in a hurry and haven't read the question completely.

Author

Commented:
Rob,

Great, followed your advice, first Reinitialized all Subscriptions and chose to create a new snapshot.  Then, just to make sure, I launched the Replication Monitor and chose my subscription, just to make sure I clicked Generate Snapshot.  Did all that, when to the publication which should be receiving the transactions (as I am pushing the publication), and the same message appears when I choose, "View Synchronization Status."

The concurrent snapshot for publication 'trsmain' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the publicat
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
Ok. Can you view the Log Reader status?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
If you use Snapshot replication, does it get the snapshot across okay?

I find it strange that the article is empty on the subscriber - makes me think the filter isn't right or something.

Rob
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
question: did you schedule the replication agents accordingly?
check in the article settings => scheduling, or in the sql agent's job folder, you will see the replication jobs.
if you see "your" job, check if it is set to run in regular intervals.

otherwise, replication won't "run".
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.