| Groups | Blog | Home |
sql server replication : Newbie question: "No replicated transactions are available"
I set up Snapshot replication using the wizard and got this message the
next time it was scheduled to run. There are 7 replication jobs on the server as follows: "Expired subscription cleanup" - this runs once a day at 1 a.m. "Agent history clean up: distribution" - this runs every 10 minutes "Distribution clean up: distribution" - this runs every 10 minutes "Replication agents checkup" - this runs every 10 minutes "Reinitialize subscriptions having data validation failures" - this DOES NOT have a schedule The REPL-Distribution job is called "STAGING-HRIS-E2-SERVER-3" and this is scheduled to run every hour on the hour. The REPL-Snapshot jobs is called "STAGING-HRIS-HRIS-2" and this is scheduled to run once a day at 1 a.m. My first instinct is that in order for the REPL-Distribution job to transfer any records, the REPL-Snapshot job must be run first. Since the Snapshot job is only scheduled to run once a day, this is the reason for the "No replicated transactions are available" message. Is my assumption right? So should I just schedule the Snapshot job to run, say, 10 minutes before the REPL-Distribution job? What about the job titled "Reinitialize subscriptions..." that is not scheduled? Should this be scheduled? How often, according to the scenario above? Thanks in advance for any help.
Leave everything as it is.
[quoted text, click to view] > My first instinct is that in order for the REPL-Distribution job to > transfer any records, the REPL-Snapshot job must be run first. Since > the Snapshot job is only scheduled to run once a day, this is the > reason for the "No replicated transactions are available" message. Is > my assumption right? So should I just schedule the Snapshot job to > run, say, 10 minutes before the REPL-Distribution job? With anonymous subscribers the snapshot job will be run each time it is scheduled and it will generate a new snapshot. This can cause locking on your publication database. With named subscribers the snapshot job will be run each time it is scheduled, but it will close down unless you have a new subscriber or require a new subscription due to a reinitialization. The distribution agent does not need a snapshot if you have done a no sync subscription. Chances are you have done a sync subscription. [quoted text, click to view] > > What about the job titled "Reinitialize subscriptions..." that is not > scheduled? Should this be scheduled? How often, according to the > scenario above? > > Thanks in advance for any help. I would not schedule it unless you have data validation problems. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] <mkerrigan@ktoys.com> wrote in message news:1137186776.829836.114130@z14g2000cwz.googlegroups.com... >I set up Snapshot replication using the wizard and got this message the > next time it was scheduled to run. > > There are 7 replication jobs on the server as follows: > > "Expired subscription cleanup" - this runs once a day at 1 a.m. > "Agent history clean up: distribution" - this runs every 10 minutes > "Distribution clean up: distribution" - this runs every 10 minutes > "Replication agents checkup" - this runs every 10 minutes > "Reinitialize subscriptions having data validation failures" - this > DOES NOT have a schedule > > The REPL-Distribution job is called "STAGING-HRIS-E2-SERVER-3" and this > is scheduled to run every hour on the hour. > > The REPL-Snapshot jobs is called "STAGING-HRIS-HRIS-2" and this is > scheduled to run once a day at 1 a.m. > > My first instinct is that in order for the REPL-Distribution job to > transfer any records, the REPL-Snapshot job must be run first. Since > the Snapshot job is only scheduled to run once a day, this is the > reason for the "No replicated transactions are available" message. Is > my assumption right? So should I just schedule the Snapshot job to > run, say, 10 minutes before the REPL-Distribution job? > > What about the job titled "Reinitialize subscriptions..." that is not > scheduled? Should this be scheduled? How often, according to the > scenario above? > > Thanks in advance for any help. >
[quoted text, click to view] Hilary Cotter wrote: > With anonymous subscribers the snapshot job will be run each time it is > scheduled and it will generate a new snapshot. This can cause locking on > your publication database. With named subscribers the snapshot job will be > run each time it is scheduled, but it will close down unless you have a new > subscriber or require a new subscription due to a reinitialization. But I want the data updated every hour. How can it do this if the snapshot job is only scheduled to run once a day? [quoted text, click to view] > The distribution agent does not need a snapshot if you have done a no sync > subscription. Chances are you have done a sync subscription. I guess I'm not fully understanding this process. If there is no snapshot, to me it seems that no data will be updated, so what is the distribution agent distributing? The same exact data it distributed an hour before? This doesn't make sense to me.
The log reader agent will read your transaction log and construct commands
which it writes to the distribution database. The distribution database then reads these commands and applies them on the subscriber. If your distribution agent is reading "no replication transactions are available", either your log reader is not reading them in the transaction log, it is stopped, or there are no transactions hitting your publication database. If you only want data updates every hour, have your distribution agent run hourly. The snapshot agent will generate scripts and data files which will be applied on the subscriber. These scripts will recreate the objects you are replicating on the subscriber, the data will make your subscriber a clone of your publisher (with some exceptions). I hope this answers your questions, if not please post back. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] <mkerrigan@ktoys.com> wrote in message news:1137252443.744733.38970@g44g2000cwa.googlegroups.com... > > Hilary Cotter wrote: >> With anonymous subscribers the snapshot job will be run each time it is >> scheduled and it will generate a new snapshot. This can cause locking on >> your publication database. With named subscribers the snapshot job will >> be >> run each time it is scheduled, but it will close down unless you have a >> new >> subscriber or require a new subscription due to a reinitialization. > > But I want the data updated every hour. How can it do this if the > snapshot job is only scheduled to run once a day? > >> The distribution agent does not need a snapshot if you have done a no >> sync >> subscription. Chances are you have done a sync subscription. > > I guess I'm not fully understanding this process. If there is no > snapshot, to me it seems that no data will be updated, so what is the > distribution agent distributing? The same exact data it distributed an > hour before? This doesn't make sense to me. >
Since this is answering a question that wasn't asked, let me clear it up.
Snapshot replication, in default configuration, wipes out the tables being replicated at the subscriber and repopulates them from scratch each time it runs. The scheduling configuration you have doesn't make any sense. Yes, you are correct. The snapshot agent generates the snapshot. The distribution agent applies it. With the snapshot agent running once per day and the distribution agent running 24 times per day, you will have 1 snapshot pr day generated and 1/24 of the distribution agent cycles will apply something to your subscribers. (Forget the anonymous and named subscribers crap, there is a reason this was completely removed in 2005 due to the massive confusion on what it really meant or what it really did.) Now, if you are needing data updated every hour, you shouldn't be doing this with snapshot replication. You should be doing it with transactional replication. A snapshot is executed to perform the initial synchronization. From that point forward, incremental transactions are picked up from your transaction log and written into the distribution database. Then the distribution agent picks those up and applies them to each subscriber. In your case, I could configure the log reader (the thing moving data into the distribution database) to run continuously and schedule the distribution agent to run once per hour in order to pick up the last hour's worth of data and apply it incrementally to the subscribers. -- Mike http://www.solidqualitylearning.com Disclaimer: This communication is an original work and represents my sole views on the subject. It does not represent the views of any other person or entity either by inference or direct reference. [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:O3YXnDUGGHA.1288@TK2MSFTNGP09.phx.gbl... > The log reader agent will read your transaction log and construct commands > which it writes to the distribution database. The distribution database > then reads these commands and applies them on the subscriber. > > If your distribution agent is reading "no replication transactions are > available", either your log reader is not reading them in the transaction > log, it is stopped, or there are no transactions hitting your publication > database. > > If you only want data updates every hour, have your distribution agent run > hourly. > > The snapshot agent will generate scripts and data files which will be > applied on the subscriber. These scripts will recreate the objects you are > replicating on the subscriber, the data will make your subscriber a clone > of your publisher (with some exceptions). I hope this answers your > questions, if not please post back. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > <mkerrigan@ktoys.com> wrote in message > news:1137252443.744733.38970@g44g2000cwa.googlegroups.com... >> >> Hilary Cotter wrote: >>> With anonymous subscribers the snapshot job will be run each time it is >>> scheduled and it will generate a new snapshot. This can cause locking on >>> your publication database. With named subscribers the snapshot job will >>> be >>> run each time it is scheduled, but it will close down unless you have a >>> new >>> subscriber or require a new subscription due to a reinitialization. >> >> But I want the data updated every hour. How can it do this if the >> snapshot job is only scheduled to run once a day? >> >>> The distribution agent does not need a snapshot if you have done a no >>> sync >>> subscription. Chances are you have done a sync subscription. >> >> I guess I'm not fully understanding this process. If there is no >> snapshot, to me it seems that no data will be updated, so what is the >> distribution agent distributing? The same exact data it distributed an >> hour before? This doesn't make sense to me. >> > >
Hilary and Mike,
Thank you both for your input. Hilary, you are right, it turns out I was using transaction replication, although I could have sworn that I chose to use snapshot when running the replication wizard. Mike, thanks for the explanation, and I will use transactional replication instead of snapshot for this particular data. Mary [quoted text, click to view] Hilary Cotter wrote:
> Mike while your answers are completely correct for the case of snapshot > replication - keep in mind the subject - > > "No replicated transactions are available" - Snapshot replication does not > generate this message - only transactional replication does. While the > poster seems confused as to what replication type they are using, its quite > clear to me which one it is - and that is transactional. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > "Michael Hotek" <mike@solidqualitylearning.com> wrote in message > news:u46rMbzGGHA.2472@TK2MSFTNGP10.phx.gbl... > > Since this is answering a question that wasn't asked, let me clear it up. > > > > Snapshot replication, in default configuration, wipes out the tables being > > replicated at the subscriber and repopulates them from scratch each time > > it runs. The scheduling configuration you have doesn't make any sense. > > Yes, you are correct. The snapshot agent generates the snapshot. The > > distribution agent applies it. With the snapshot agent running once per > > day and the distribution agent running 24 times per day, you will have 1 > > snapshot pr day generated and 1/24 of the distribution agent cycles will > > apply something to your subscribers. > > > > (Forget the anonymous and named subscribers crap, there is a reason this > > was completely removed in 2005 due to the massive confusion on what it > > really meant or what it really did.) > > > > Now, if you are needing data updated every hour, you shouldn't be doing > > this with snapshot replication. You should be doing it with transactional > > replication. A snapshot is executed to perform the initial > > synchronization. From that point forward, incremental transactions are > > picked up from your transaction log and written into the distribution > > database. Then the distribution agent picks those up and applies them to > > each subscriber. In your case, I could configure the log reader (the > > thing moving data into the distribution database) to run continuously and > > schedule the distribution agent to run once per hour in order to pick up > > the last hour's worth of data and apply it incrementally to the > > subscribers. > > > > -- > > Mike > > http://www.solidqualitylearning.com > > Disclaimer: This communication is an original work and represents my sole > > views on the subject. It does not represent the views of any other person > > or entity either by inference or direct reference. > > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > > news:O3YXnDUGGHA.1288@TK2MSFTNGP09.phx.gbl... > >> The log reader agent will read your transaction log and construct > >> commands which it writes to the distribution database. The distribution > >> database then reads these commands and applies them on the subscriber. > >> > >> If your distribution agent is reading "no replication transactions are > >> available", either your log reader is not reading them in the transaction > >> log, it is stopped, or there are no transactions hitting your publication > >> database. > >> > >> If you only want data updates every hour, have your distribution agent > >> run hourly. > >> > >> The snapshot agent will generate scripts and data files which will be > >> applied on the subscriber. These scripts will recreate the objects you > >> are replicating on the subscriber, the data will make your subscriber a > >> clone of your publisher (with some exceptions). I hope this answers your > >> questions, if not please post back. > >> > >> -- > >> Hilary Cotter > >> Looking for a SQL Server replication book? > >> http://www.nwsu.com/0974973602.html > >> > >> Looking for a FAQ on Indexing Services/SQL FTS > >> http://www.indexserverfaq.com > >> > >> <mkerrigan@ktoys.com> wrote in message > >> news:1137252443.744733.38970@g44g2000cwa.googlegroups.com... > >>> > >>> Hilary Cotter wrote: > >>>> With anonymous subscribers the snapshot job will be run each time it is > >>>> scheduled and it will generate a new snapshot. This can cause locking > >>>> on > >>>> your publication database. With named subscribers the snapshot job > >>>> will be > >>>> run each time it is scheduled, but it will close down unless you have a > >>>> new > >>>> subscriber or require a new subscription due to a reinitialization. > >>> > >>> But I want the data updated every hour. How can it do this if the > >>> snapshot job is only scheduled to run once a day? > >>> > >>>> The distribution agent does not need a snapshot if you have done a no > >>>> sync > >>>> subscription. Chances are you have done a sync subscription. > >>> > >>> I guess I'm not fully understanding this process. If there is no > >>> snapshot, to me it seems that no data will be updated, so what is the > >>> distribution agent distributing? The same exact data it distributed an > >>> hour before? This doesn't make sense to me. > >>> > >> > >> > > > >
Mike while your answers are completely correct for the case of snapshot
replication - keep in mind the subject - "No replicated transactions are available" - Snapshot replication does not generate this message - only transactional replication does. While the poster seems confused as to what replication type they are using, its quite clear to me which one it is - and that is transactional. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Michael Hotek" <mike@solidqualitylearning.com> wrote in message news:u46rMbzGGHA.2472@TK2MSFTNGP10.phx.gbl... > Since this is answering a question that wasn't asked, let me clear it up. > > Snapshot replication, in default configuration, wipes out the tables being > replicated at the subscriber and repopulates them from scratch each time > it runs. The scheduling configuration you have doesn't make any sense. > Yes, you are correct. The snapshot agent generates the snapshot. The > distribution agent applies it. With the snapshot agent running once per > day and the distribution agent running 24 times per day, you will have 1 > snapshot pr day generated and 1/24 of the distribution agent cycles will > apply something to your subscribers. > > (Forget the anonymous and named subscribers crap, there is a reason this > was completely removed in 2005 due to the massive confusion on what it > really meant or what it really did.) > > Now, if you are needing data updated every hour, you shouldn't be doing > this with snapshot replication. You should be doing it with transactional > replication. A snapshot is executed to perform the initial > synchronization. From that point forward, incremental transactions are > picked up from your transaction log and written into the distribution > database. Then the distribution agent picks those up and applies them to > each subscriber. In your case, I could configure the log reader (the > thing moving data into the distribution database) to run continuously and > schedule the distribution agent to run once per hour in order to pick up > the last hour's worth of data and apply it incrementally to the > subscribers. > > -- > Mike > http://www.solidqualitylearning.com > Disclaimer: This communication is an original work and represents my sole > views on the subject. It does not represent the views of any other person > or entity either by inference or direct reference. > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:O3YXnDUGGHA.1288@TK2MSFTNGP09.phx.gbl... >> The log reader agent will read your transaction log and construct >> commands which it writes to the distribution database. The distribution >> database then reads these commands and applies them on the subscriber. >> >> If your distribution agent is reading "no replication transactions are >> available", either your log reader is not reading them in the transaction >> log, it is stopped, or there are no transactions hitting your publication >> database. >> >> If you only want data updates every hour, have your distribution agent >> run hourly. >> >> The snapshot agent will generate scripts and data files which will be >> applied on the subscriber. These scripts will recreate the objects you >> are replicating on the subscriber, the data will make your subscriber a >> clone of your publisher (with some exceptions). I hope this answers your >> questions, if not please post back. >> >> -- >> Hilary Cotter >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> <mkerrigan@ktoys.com> wrote in message >> news:1137252443.744733.38970@g44g2000cwa.googlegroups.com... >>> >>> Hilary Cotter wrote: >>>> With anonymous subscribers the snapshot job will be run each time it is >>>> scheduled and it will generate a new snapshot. This can cause locking >>>> on >>>> your publication database. With named subscribers the snapshot job >>>> will be >>>> run each time it is scheduled, but it will close down unless you have a >>>> new >>>> subscriber or require a new subscription due to a reinitialization. >>> >>> But I want the data updated every hour. How can it do this if the >>> snapshot job is only scheduled to run once a day? >>> >>>> The distribution agent does not need a snapshot if you have done a no >>>> sync >>>> subscription. Chances are you have done a sync subscription. >>> >>> I guess I'm not fully understanding this process. If there is no >>> snapshot, to me it seems that no data will be updated, so what is the >>> distribution agent distributing? The same exact data it distributed an >>> hour before? This doesn't make sense to me. >>> >> >> > >
Hilary,
I'm getting the same message (no replication transactions are available), and I'm using snapshot replication. The agent is set to run based on when some sales data is approved, which can be anywhere from every day, to once a month. I discovered that the snapshot folder ends up with only one .bcp file. The others keep getting removed. This seems to occur when a user gets an error like "Unable to open BCP host data-file.." . Once this happens it looks like all the other files, except one BCP file gets wiped out. Is there a setting somewhere that can be selected to NOT delete the other files? I have to keep rerunning the snapshot to get back the files. Any help would be appreciated (My publisher/distributor are SQL 2005, and my subscribers are SQL 2000). Thanks, Amy Marshall
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |