SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article
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
ADVERTISEMENT
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
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
Oct 14, 2014
Here's what my script boils down to when adding a new view (admin.replication_test):
Code:
EXEC sp_addarticle
@publication = N'<publication name>'
, @article = N'replication_test'
, @source_owner = N'admin'
, @source_object = N'replication_test'
, @type = N'view schema only'
;
If I check the GUI, the article appears to be ticked in the publication.
Generate a new snapshot.
View not found in replicated database!
View 9 Replies
View Related
May 21, 2015
I want to remove article from my Transactional replication, can I just simply uncheck it from GUI, or it requires anything else.
View 3 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 24, 2007
Hi,
I'm setting up a heterogeneous transactional push replication with Sybase ASE 12.5.3 as subscriber. With management studio I try to create an procedure article with following properties
Copy extended properties : false
Destination object name : pGS_RefuseRequest
Destination object ownere : dbo
Action if name is in use : keep existing object unchanged
Replicate : Execution of the stored procedure
Create schemas at Subscriber : false
When I save the article and then the publication I got following error message:
Can not add artice 'pGS_RefuseRequest'.
Object was not found on server. Check if this object exists on the server. (Microsoft.SqlServer.Rmo)
That's realy strange because the wizard offered the procedure pGS_RefuseRequest in the list of possible articles.
Fortunatly I can create the article with following TSQL statement :
exec sp_addarticle @publication = N'RIGHTS_EDV_4T_pub'
, @article = N'pGS_RefuseRequest'
, @source_owner = N'dbo'
, @source_object = N'pGS_RefuseRequest'
, @type = N'proc exec'
, @description = N''
, @creation_script = N''
, @pre_creation_cmd = N'none'
, @schema_option = 0x00
, @destination_table = N'pGS_RefuseRequest'
, @destination_owner = N'dbo'
, @status = 0
go
Did anybody seen this bug before ?
It seems to be specific for heterogeneuous replication. In a pure MS environement the Wizard works fine!
Wolfgang Kunk
View 3 Replies
View Related
Jun 15, 2015
How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?
View 2 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
Apr 25, 2015
I need to setup snapshot replication for 1 large table.
Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1
In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)
View 1 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
Oct 27, 2015
We have a domain joined SQL 2008 R2 server performing a snapshot database replication to a non-domain joined SQL 2008 R2 server. The snapshot replication is working with one exception. Under one of the tables, there is a Key, Constraint and Index that are part of the database. The Key and Indexes is copying over. However, the constraint is not. Why would the Key and Index copy but not the Constraint?
View 3 Replies
View Related
Feb 20, 2015
Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....
View 0 Replies
View Related
Jun 14, 2015
I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup
The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?
View 3 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
Apr 25, 2015
Regarding Snapshot replication....
If I have 200 tables on my source DB and 100 tables on my target DB... and I set up a publication w/ just 1 article to be published....
Will the invocation of the snapshot/apply affect any tables on my target other than the article I'm replicating?
View 2 Replies
View Related
Sep 22, 2015
I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.
I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?
My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?
View 1 Replies
View Related
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
View Related
Jul 27, 2007
SQL 2005 (SP1, +2153)
Windows 2003 (SP1)
This is not so much a question as a warning/comment.
I was struggling to understand why the log file on my Merge Replication database was continuing to grow despite having a transaction log backup scheduled hourly.
I finally ran a DBCC OPENTRAN to see if there were open transactions and I got the following (to me, unusual) result:
Oldest distributed LSN : (10:384:4)
Oldest non-distributed LSN : (0:0:0)
When I googled for answers, everything kept coming back with pointers to SQL Transactional replication.
We were doing Merge Replication, so I didn't think the various links were relevant. But as I kept coming up empty for a solution to the issue, I finally went and had a look at the publisher properties and scanned down the list of replicated databases (there's just the one). Voila! The database in question was configured for both Transactional and Merge replication.
How it came to be configured for both Transactional and Merge replication, I do not know. I have a feeling it is somehow embedded in the publication creation script that I created some time ago. When I went to double check, I found the same option set on our Development and Test servers.
So, if you have a merge replication database and you find that you have a log file that is growing uncontrollably, check the publisher properties and look to see whether your database is enabled for transactional replication.
Regards,
hmscott
View 2 Replies
View Related
Jan 17, 2007
In our replication environment, the subscriber is initially set up with an snapshot of the publisher database. However, after that, the subscriber and publisher are different and we can never re-initialize from a snapshot again (we purge data on the publisher to reduce the database size but do not purge the same data on the subscriber; we do this by stubbing out the stored procedures on the subscriber that purge data on the publisher).
If an article is added or dropped from the publication, using snapshot and synchronize, just these changes are propagated to the publisher (without an entire new snapshot).
However, if an Article Property is changed (change SCALL to MCALL under Statement Delivery options for the UPDATE statement), the interface REQUIRES an entire new snapshot. Is there any way I can avoid the new Snapshot? It overwrites the subscriber database and this cannot happen!
Linda
View 5 Replies
View Related
Jan 26, 2007
Hi experts:
We have 4 SQL SERVER 2000 servers linked via replication, and due to business changes we have to add one table to the published databases. And the existed database has more than 20g, recreating the snapshot and re-init replication is not allowed as the business cannot be stopped more than 1 hour. So my question is how to add tables to the published article without recreating the snapshot?
By that is not possilbe, can we publish one new article on the same database?
Thanks in advance!
Ron
View 1 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Feb 6, 2015
I am very new to SQL coming from a SAS background.
I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.
For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.
I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.
This is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.
I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).
SELECT
CC.cc_name as Academy,
SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre,
MD2.m_reference as Course,
MD2.m_name as Course_Name,
CAST(MD2.m_start AS DATE) as Start_Date,
[Code] ...
View 4 Replies
View Related
Dec 1, 2006
Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
View 4 Replies
View Related
Dec 8, 2014
I have huge database on prod. One time I tried to run DBCC CHECKDB, it took more than a day. My question is can I created a snapshot of the prod database on the same server and run DBCC CHECKDB on the Snapshot DB? will doing this interfere production database? I don’t have option to make copy of the database on a test server and run it there.
View 9 Replies
View Related
