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.

I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following message

The process is running and is waiting for a response from the server

enter image description here

but the problem is when I see the size of the snapshot folder, I can see it is growing up! But too slowly.

enter image description here enter image description here

Do you have any Idea about this? or any different way of doing the replication? how can I find the more detailed warning or errors messages?

P.S. In my database there are two tables for Images that they make 98% of the size of my database. when I don't include them in my publication, everything goes fine. but when I Include them, I will face the problem.

share|improve this question
add comment

2 Answers

Yes, initializing a large database through a snapshot can be very slow and time consuming. If it is appropriate to your case, you should start by restoring a copy of the database to the target machine.

In setting up the subscription choose "allow initialization from backup files".

Because of limitations in the user interface for setting up replication, you will need to create a script instead of relying on the UI tools. There is a blog post on this at:

http://blogs.msdn.com/b/repltalk/archive/2010/03/16/deep-dive-on-initialize-from-backup-for-transactional-replication.aspx

The post from msdn includes the code sample:

exec sp_addsubscription @publication = N'Repl2000', …..
    @sync_type = N'initialize with backup', @backupdevicetype='Disk',
    @backupdevicename='C:\Repl2000_RestoreThis.bak'--the last backup used to restore on the subscriber 
go
exec sp_addpushsubscription_agent …….
go

The article also includes some troubleshooting tips.

share|improve this answer
    
What are the parameters I must pass? what are the parameters for sp_addpushsubscription_agent and what shall I pass for @publication in the sp_addsubscription stored procedure? –  Mehrdad Kamelzadeh Sep 29 '13 at 20:03
    
These definitely depend on what you are doing. For sp_addsubscription see technet.microsoft.com/en-us/library/ms181702.aspx For sp_addpushsubscription_agent see technet.microsoft.com/en-us/library/ms175006.aspx. Look at the examples at the end of each for help. –  RLF Sep 29 '13 at 21:33
    
I need a complete example if it is possible. Thanks. –  Mehrdad Kamelzadeh Sep 30 '13 at 10:38
    
The complete simple example is available at the bottom of the two links. Just change the names to match your setup. If you want to use the many other parameters, then you need to decide what you want to do. –  RLF Oct 3 '13 at 14:41
add comment

P.S. In my database there are two tables for Images that they make 98% of the size of my database. when I don't include them in my publication, everything goes fine. but when I Include them, I will face the problem.

I would suggest you to have a separate publication for these 2 tables and rest of the tables in another publication. This way if something goes wrong with these 2 tables, then your entire data set is not affected.

any different way of doing the replication?

As @RLF mentioned to initialize replication from backup. There is one another approach as below.

Kendal Van Dyke blogged about the second approach at Reduce Transactional Replication Synchronization Time By Applying Snapshots Manually which is excellent and is a time and life saver. Its a bit of manual process, but I have dealt with it and its a win-win approach.

how can I find the more detailed warning or errors messages?

To enable verbose logging you must add two parameter values to the Run Agent step of the replication agent job for which you wish to review more detailed logging:

-OutputVerboseLevel [0|1|2]
-Output [Full Output Path and File Name]

The OutputVerboseLevel parameter accepts one of three integer values (0,1 or 2).

  • A value of 0 infers that there is no verbose logging on internal commands in the Agent, whereas
  • a value of 1 will result in minimal logging and finally
  • a value of 2 signifies the most-detailed logging you can expect from the associated Replication Agent Job.
share|improve this answer
add comment

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.