Transactional Replication - Snapshot Management and Frequency

Posted on 2009-04-15
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 42

    Accepted Solution

    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

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now