Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Transactional Replication - Snapshot Management and Frequency

Posted on 2009-04-15
Medium Priority
Last Modified: 2012-05-06

I have a large database of 100Gb running SQL Server 2005 Ent Edition on Win2K3 Ent Ed. Let's call it Server 1.

The database is highly transactional and is very busy for around 18 hours a day.

The number of changes made each day is perhaps 5% of the total data held and individual transactions made are small but numerous.

I have a requirement to replicate a small number of tables from Server 1 to Server 2. Both servers are the same spec but Server 2 is much less busy than Server 1 and is running SSRS.

I've configured transactional replication on Server 1 as Publisher and Distributor with Server 2 acting as a pull Subscriber. Performance appears to be very good (both machines are in the same rack on the same LAN).

My question is at what interval should I configure the snapshot agent to run. The default appears to be 1 hour but I'm wondering if once every 24 hours would suffice? This would allow me to schedule snapshots for a time when activity is low.

I've left all other agents set at their defaults.

Many thanks

Question by:WebAdviser
LVL 43

Accepted Solution

Eugene Z earned 2000 total points
ID: 24164874
are you sure it is snapshot agent ?
it used 1s  for initial setup of transactional replication
and on demand: e.g. reinit replication, etc

it will be triggered automatically as needed
By default it does not have schedule - if you see it it was set manually
and in your case you need to change it to default (no schedule) if you run transactional replication


Author Comment

ID: 24165174
Hi EugeneZ

Thanks for your comments.

I think what is confusing me is the role of the snapshot agent for transactional replication. If I understand you correctly the snapshot only *needs* to be created on the initial set up of replication and then if re-initiailisation is required. Is this correct please?

I'm looking at the SQL Jobs and I've found the REPL-Snapshot job that has been created. This is scheduled to run once an hour. Can I remove the schedule for this as I have no requirement for snapshots myself but I'm unclear whether the replication process needs regular snapshots to continue working OK. Again, your comments would be welcome on this please.

Many thanks


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

572 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