Snapshot Replication Failure

MJVMJV Member Posts: 38

Our snapshot replication just stopped working about a week ago, and I have been trying to get it running ever since.

It seems to fail at the snapshot generation.

I get error messages containing such things as "unable to read column". There are also numerous timeouts on various steps with the notation that they will be restarted.

The process used to complete within about two hours. Now it runs for upwards of 8 hours and then errs out.

I note that all 32 Gb RAM gets used up in the process pretty much, though no out of memory messages are generated.

We are on sql server 2005 on windows server 2003 R2 SP2 Standard x64 Edition.

I don't have the error message right here to copy in, and I don't expect anyone to look at it that deeply.

I am just hoping for some tips and tricks or common experience sharing.




  • Options
    MJVMJV Member Posts: 38
    Hi All, I just wanted to present the solution I arrived at, in case anyone else encounters this issue.

    according to the snippet from the TechNet article I pasted below, Snapshot Replication is good for small volumes of data only.

    Out database is only 178Gb, and the snapshot somewhat less, but this was enough to cause the crash.

    The snapshot process uses RAM for efficiency. If the data size is too large, all of the RAM gets used up.

    I cured the problem by opening the Articles window for the publication, and removing any tables with large amounts of records that we did not report on (we use the replication for our SSRS data).

    I also removed any empty tables that we don't put any data in that were included in the replication.

    From https://technet.microsoft.com/en-us/library/ms152565(v=sql.90).aspx

    Snapshot Replication

    The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications, but snapshot replication can also be used by itself. Using snapshot replication by itself is most appropriate when one or more of the following is true:

    • Data changes infrequently.
    • It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
    • Replicating small volumes of data.
    • A large volume of changes occurs over a short period of time.
Sign In or Register to comment.