Solved

SQL Server 2005 tranactional replication

Posted on 2009-07-05
10
397 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?
0
Comment
Question by:shageman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 15

Expert Comment

by:rob_farley
ID: 24781965
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24782187
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.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24782305
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
0
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
LVL 15

Expert Comment

by:rob_farley
ID: 24782396
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24782807
Oops.. Typed in a hurry and haven't read the question completely.
0
 
LVL 1

Author Comment

by:shageman
ID: 24784990
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
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24790638
Ok. Can you view the Log Reader status?
0
 
LVL 1

Accepted Solution

by:
shageman earned 0 total points
ID: 24790759
3 transaction(s) with 4 command(s) were delivered..  But, still the Row Count on the target machine is 0.
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24957381
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24958078
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".
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question