Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 309 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Snapshot Replication Failure
MJV
Member Posts: 38
in SQL General
Hi,
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.
Thanks,
Michael
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.
Thanks,
Michael
0
Answers
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.