Solved

SQL Server 2005 tranactional replication

Posted on 2009-07-05
10
390 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 184
SQL Restore Script - Syntax Error 8 107
Error in SSIS while executing  - Potential data loss 4 36
Substring works but need to tweak it 14 31
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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