Solved

SQL Server 2005 tranactional replication

Posted on 2009-07-05
10
386 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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 142

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

778 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