Transactional Replication - Pull Subscription Snapshot Being Applied Continuously
Jan 25, 2007
Hi
We have setup transactional replication between 2 databases on SQL Server 2000 SP3a (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.
From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring
Initialising
Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri
These all complete successfully but then the process kicks off again immediately after reapplying the snapshot. We are unaware of any settings that may be causing this.
Any help on what maybe causing this would be much appreciated.
View 5 Replies
ADVERTISEMENT
Jan 5, 2005
I have setup a pull subscription on the subscriber for transactional replication. The distribution job continues to run, but doesn't do anything because it says: The initial snapshot for publication 'man4' is not yet available.
How can I tell if the snapshot is running, and can I see the snapshot where it was created?
View 1 Replies
View Related
Jun 21, 2007
Hello.
I've been having an error when downloading the snapshot agent from our Publisher.
The articles selected are all the tables and all the stored procedures.
The Subscription is created programmatically and so is the synchronization. When trying to synchronize for the first time and when the subscriber tries to download the snapshot I have the following error:
Error messages:
The schema script 'Distrito_2.sch' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
The process could not read file 'GESZifuncAFRODITE$SILVITEST_GESZIF_GESZIF20070621182845Distrito_2.sch' due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20143)
Get help: http://help/MSSQL_REPL20143
The system cannot find the path specified.
(Source: MSSQL_REPL, Error number: MSSQL_REPL3)
Get help: http://help/MSSQL_REPL3
The snapshot folder is at c:GESZifSnapshotFolder and the unc is \AfroditeSnapshot.
I'm getting a little desperate with this because we really need to propagate the schema changes around the subscribers.
Thanks in advance,
AVD
View 4 Replies
View Related
Oct 3, 2007
I created transactional replication on a database and setup pull subscriptions on each subscriber to run at a scheduled time once a day. The scheduled start time on each subscriber can differ. The transaction log on the publishing database will eventually consume all possible disk space. Is it possible (and safe) to shrink or truncate the transaction log file for the publishing database before all the subscribers completed running its daily pull subscription? If not, how can I manage disk space for the transaction log on the publishing database and ensure all transaction are replicated to the subscriber?
Thanks in advance.
View 1 Replies
View Related
May 21, 2002
I've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7
epldataunc
eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.
My questions are:
1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.
2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.
3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.
I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.
Regards.
View 1 Replies
View Related
Feb 2, 2004
Hi
I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)
We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)
When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.
If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.
Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?
Or is there a better way of doing this?
I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...
TIA
View 2 Replies
View Related
Jun 27, 2007
Why would you schedule a Snapshot Agent to run daily, weekly, monthly, etc. for Transactional replication when the transactions are replicated once the change is committed to the Publication database?
Does this help to replicate DDL against the Subscriber?
Thanks,
Thomas LeBlanc
View 1 Replies
View Related
Sep 7, 2006
When you create a transactional Replication.Is there a way to disable to snapshot agent scheduler. I believe that there is a way to disble it when you create a publisher.
Help ME!!!!!!!!!!!!!!!
View 3 Replies
View Related
Mar 27, 2007
Hello,
We are running transactional replication in a high volume environment and we are starting to see high IO on the distribution database when the distribution cleanup runs. We only build the snapshot once a week and I was wondering if I rebuilt the snapshot more often it would reduce the size of the distribution database and the amount of IO that are being generated during the cleanup?
Thanks in advance
View 4 Replies
View Related
Dec 18, 2002
Trying to set up Merge Replication using FTP with PULL subscriptions on sql 2000
Are there any issues that anyone is aware of that may affect this working.
View 2 Replies
View Related
Jun 2, 2006
Hi,
I have transactional replication setup with SQL 2000 on W2K3 cluster using updateable push subscribers. While setting up replication, we chose default location for snapshot folder that resides on non-clustered drive. Is there a way to change this location without disturbing current replication setup. I looked at the 'alternate snapshot location' solution, but it requires snapshot re-initialization. I am trying to do this with minimal effort and downtime.
Thanks,
np70
View 3 Replies
View Related
Feb 13, 2004
I have transactional replication running. I would like the Snapshot Agent to run regularly (once per day, probably, at 23:00) so that the target data are completely refreshed so that there is little possibility of the target getting out of sync.
The Snapshot Agent's job is kicking off OK, but the "snapshot was not generated because no subscriptions needed initialization". I know I can initialise the subscriptions manually - is there a way to do this so that the initialisation happens just before the snapshot job launches?
Or is this a bad thing to want to do?
TIA
View 1 Replies
View Related
Nov 10, 2006
SQL Server 2005 Books Online provides an article entitled, "Initializing a Transactional Subscription without a Snapshot". Is it possible in SQL Server 2000 to initialize transactional replication without a snapshot?
So far, I have been unable to find a similar procedure mentioned in the SQL 2000 Books Online. I was able to follow the 2005 procedure using SQL 2000 until I got to the step that says to enable the "Allow initialization from backup files" option on the "Subscription Options" tab of the "Publication Properties" dialog. But that option does not appear in the SQL 2000 version of the specified dialog box.
Any ideas?
View 4 Replies
View Related
Feb 20, 2006
I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.
I'm thinking of doing something like this:
Set up the distributor, publication, and subscription
Turn off distribution agent
Set the publisher to "sync with backup"
Backup the publisher, full then log
Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on)
Turn off "sync with backup"
Restore the full and tran log backups to new subscriber db
Create subscriber stored procs in subscriber
Start up distribution agent
I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.
Thanks in advance!
Mike
View 1 Replies
View Related
Apr 9, 2014
I built a number of publications on a SQL Server 2005 box to replicate to a SQL Server 2012 subscriber. All the publications except one are fine. During the snapshot phase of schema script generation I get Script Failed for Table 'dbo.MediaDisplayLibraryFileData'. From the Replication monitor for the Snapshot Agent on the Publication I get, "Column FileData in object MediaDisplayLibraryFileData contains type VarBinaryMax, which is not supported in the target server version, SQL Server 2000." This message makes no sense since the target server version is 2012. I have even checked that the compatibility level was set to 110 before I started the process of setting up replication. How do I resolve this error?
View 0 Replies
View Related
Mar 19, 2015
Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?
The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.
We have a post snapshot script that updates these additional columns on the subscriber.
I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.
Is this possible?
View 1 Replies
View Related
Aug 3, 2006
Hi I want to Set up Such Merge Replication, Which can can Synchronize With out Network, Means Syncronization using CD.
Also When the Network connectivity will be available then it should do synchronization
I wants to transfer some file using CD only those changes to database which hasn't been synchronized, thats why Backup will not work.
Pls Help me, or tell me wether it is possible or not.
Tell me some work around, or ask me details,
You got to help me out, Otherwise I will loss trust in MSDN Forums.
Thanks in advance
Vishalgiri Goswami
View 3 Replies
View Related
Feb 2, 2007
Hi,
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record.
The second trigger inserts record in to another database table and also updates a column of the table "Sample".
The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser.
Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
It would be helpful if u could reply soon.
Thanks and Regards,
Swapna.B.
View 12 Replies
View Related
Sep 15, 2015
We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:
MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp
With other tables, I'll get just one LARGE snapshot file, named:
MyOtherTable_4.bcp
In the latter case, the file can be very large (most recent is 38GB).
In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'
My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.
View 0 Replies
View Related
Apr 28, 2015
Any TSQL script to monitor replication(Transactional, Snapshot) with current status ? I have tried below script but it giving error.
"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."
DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)
View 4 Replies
View Related
Jan 7, 2008
Hi all,
I've been looking for the right stored procedure to create a push subscription for publication but i haven't had much luck so far. So I was wondering if you guys know which SP is appropriate, that'd be super great!! thanks guys!
note. the sp should run on the subscriber database.
thanks again
View 2 Replies
View Related
Jan 24, 2007
I am using SQL 2000. How can I get my transactional replication reinitialized after it has failed with several attempts.
I know one way of doing it through enterprise manager and specifying the subscription to reinitialize. But this will apply the snapshot and will take long time.
Is there a fast way of doing it?
-Nipul
View 1 Replies
View Related
Oct 22, 2015
There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.
When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).
When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.
The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.
I have tried:
- dropping/re-adding the article in question.
- Setting up a new Snapshot Folder
- Validated all the settings and configurations
I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.
View 0 Replies
View Related
Jul 2, 2015
We have transactional replication. After creating replication, we found that many blocking and deadlocks are occurring continuously. Will it cause any blocking on publisher db.
View 4 Replies
View Related
Sep 27, 2006
My name is Tech guy,
I want to use window script program to execute a pull subscription installed on SQL 2005 express edition. Because it free for downloand.
is there a script that can call an existing pull subscription execution.
View 6 Replies
View Related
Sep 14, 2006
Are there any procedural differences between Express and MSDE considering pull subscription?
Thanks
abdul
View 2 Replies
View Related
Aug 13, 2006
Hello,
i have one publisher with one pull subscription. when i make any change in publisher, that change is propegated to subscriber, but when i make any change in subscription, the change is not reflected. i have tried a lot of options but no one worked. please help me.
Regards,
View 9 Replies
View Related
Sep 20, 2000
When doing a pull subscription where the schema is also replicated, the snapshot fails with the message " The process could not create the file
[machine name]D$MSSQL7ReplDataunc[Publisher Name]DateTable Name.sch
There is an OS Error Code of 5 saying Acces is denied.
The file mentioned above is created, so perhaps it fails trying to create a file for the next table.
Thanks for any light that you can shed!
Bill
View 2 Replies
View Related
Sep 14, 2006
Hi there all
I have a problem creating a pull merge subscription on a server that's outside of our firewall. All standard ports are blocked so MS UNC connections are not possible which SQL 2000 uses by default when creating a subscription. Equally FTP is out = insecure.
I found a procedure to that said create the snapshot of the publication, create a backup of the published database, restore that backup to the remote server as a subscription db and then sync using merge. Didn't work, failed (several times).
Can anyone enlighten me as to what I'm doing wrong or indeed if I'm doing anything correctly!
I can create a remote desktop (Term Server) connection to the remote and another back to the publisher (from the subscriber's desktop) and both connections are using SSH tunnels. SQL Server uses a non standard port to communicate over the firewall. The remote server sits behind another firewall/router with port redirection to it's private address. Each server has the other registered and there are no comms problems and indeed there are other replicated dbs between them.
Both servers are win 2003 and the remote (subscriber) is R2 version, both with SQL 2000 Server Std patched up to SP3. (if this has any bearing on the solution which I presume there is)
Many thanks for listening and I hope a few of you can answer as well
Craig
View 4 Replies
View Related
Feb 22, 2008
Can anyone help with how to recreate a pull subscription replication job after I had to recreate the msdb database when it was marked as suspect. I still have the subscription but cannot seem to recreate a pull replication job
View 1 Replies
View Related
Aug 24, 2006
Hello,
i am trying to create a pull subscription to my publication using T-SQL. The pull subcription is created successfully but the problem is that data is not merged correctly. I mean when i run the agent ( despite of having data in subscription / publication ) the data is not merged and i get "No Data Needed To Be Merged" message.
i run following sp in this sequence:
At Subscrber:
1 - sp_addmergepullsubscription
2 - sp_addmergepullsubscription_agent
At Publisher:
1 - sp_addmergesubscription
Again at subscriber :
Transaction for adding merge agent job.
Regards,
View 16 Replies
View Related
