Snapshot for transactional replication taking too long for large database

Question

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

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

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.

Answer 1

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:

Link

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.

Answer 2

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.