.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Kaviya Balasubramanian
Imran Ghani
Post New Web Links

Locking published tables while generating the snapshot infinite (Transactional replication)

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 

I set up a transactional replication, publisher 2005 SP2 to a subscriber SQL 2008r2. The database is about 90 GB. When I want to create a Snapshot, the snapshot agent is running to 96% and then give a error. I have set up some logging, here is the tail of it:

------------------------

2011-05-16 20:16:15.32 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLAutorisatieRegister' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.32 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLBericht' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLBerichttype' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLFault' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$Ziekmeldingsregistratie' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$Ziekte Bijzonderheden' during concurrent snapshot into the distribution database


View Complete Post


More Related Resource Links

transactional replication and sp_MSupd tables

  
Hi thre, My target server keeps running sp_MSupd sp for different values in profiler and synch status shows "delivering replication commands" I keep waiting but this never ends. and ultimately we run a snapshot and it can't be applied. So recreate the whole replication again. It runs fine for a while but again, the sp_MSupd sp starts running on the target server and the replication goes down again. I don't know how to fix this. any advices?   Regards

Can't replicate few tables in Snapshot replication SQL 2005 in two different domain

  

I am not able to replicate few tables of a database using snapshot replication.Both publisher and subscriber are in different network and domain.Although I am able to replicate 950 tables but facing problem in some 30 tables.I am using push replication and I can telnet 1433 port from A server to B server but vice versa not happening.When I am replicating few records of that few tables it is replicating properly but not able to replicate complete table. I tried Verbose history too but I didn't get complete error.

Please help me out.....


transactional replication - no initial snapshot generated

  

When I try to set up a transactional replication, setting it up works fine, but then I get an error from the snapshot assistant: the execution stops at the table sysdiagrams with the error message that the filed „definition“ uses the type varbinary(max).

There was no specific error code, i.e. the error code displayed was '0'

If I try to set up a snapshot replication the sysdiagrams table is not included and it runs through just fine.

 

avoid accidental delete on Subscriber tables - transactional replication

  

Hello pundits..

I am looking to see if we have any way to set on the subcsriber to avoid accidental deletes on subscriber tables to avoid the 20598 error(where the publisher is trying to replicate a record not existing(rather deleted in accident) at the subscriber end). This is for transactional replication

regards


Cannot get Transactional Replication Snapshot to send

  

I have a database called reference that exists on server A and server B.  Both server A and B are publishers and both are their own distributors.  Certain articles are replicated (push) from the reference database on server A to server B and certain articles are replicated (push) from the reference database on server B to server A.  I recently had to restore the reference database on server B with a backup from server A (here is where my problems started).  I deleted the existing server A subscription from the server B publication and then deleted the server B publication, after that I rebuilt the server B publication and added the server A as a subscriber.  When I generated a snapshot of the server B publication the subscription will not recognize the snapshot and continues to say that the snapshot has not been fully generated (yes I verified that the snapshot was fully generated) at this point I even tried to reinitialize the subscription with a new snapshot and I netted the same result.

I then deleted the server B subscription to the server A publication and then deleted the server A publication and then took a backup of server A reference database restored it on server B and ran through the same steps in paragraph 1 with the same result.

I have tried, sp_replrestart, sp_replflush, sp_removedbreplication 'reference', 'tran' an

Transactional Replication - Creating Snapshot after adding a new table

  

Hello,

 

Sorry for my English..

 

Could you tell me if it is possible not to create entire snapshot after adding a new table? Our database is very big and it takes a lot of time to make snapshot.

 

Also could someone tell me exactly how works blocking of tables while creating snapshot?

I've read in BOL that if Snapshot Replication is used then all tables are blocked while creating snapshot for replication. And if Transactional Replication is used, snapshot is created in parallel mode and tables are not blocked and users can work with them. I understand that in second case each table is blocked while it is processing and after processing it is unblocked and next table is blocked. Is it so?

 

Also I am interested how actually tables are blocked? For SELECT/UPDATE/INSERT or how? Where can I read information about it?

 

Thanks,

V

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys

  
When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys replicated?
lcerni

Transactional Replication ISSUE - 487 tables need to be replicated

  

Hi All!

I have configured transactional replication on 7 databases from an sql server 2005 to another sql server 2005.

One of them has 487 tables and when I check "Replication Monitor" it shows that everything ok, but database above , that shows the message "Performance Critical".

My question is : The tables have been replicated or not ? How can I check this ? It´s not possible check table per table. Anyone suggests any other way to check if tables and their data have been replicated.

Thanks in advance ,

RBretas.

 


merge replication system tables fragmentation issue

  
I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Transactional replication: Updates to one of my columns (sometimes) does not get replicated from the

  
hi, This is my scenario: I have a varchar(max) column on a table that is replicated to other servers. I have a stored procedure that does and update to this column and other columns on the table. This is done inside a transaction that also includes other updates. When I call the stored procedure, this one varchar(max) column's value is not replicated. When I just run the single update statement on its own, the value is replicated. Anyone know something about this? Thanks!    

Merge Replication: How to give read access on snapshot share to a sql account

  
Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

transactional replication falling behind. millions of transactions in sp_brosereplcmds

  
Hi, Hi All, we have transactional replication set up with all the agents running at source database. The synchronization status is stuck at "delivering replication transactions". The replication latency in replication monitor says excellent. when I check sp_browsereplcmds. it ever ends. It has over more than millions of rows of output. We were having trouble with this replciation recently because at the source db. they run a large batch. So we decided to schedule snapshot agent to run exactly after that batch but the snapshot doesn't seem to helping out with the latency. what should be done to resolve the issue. Please help. thanks.

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

  
So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

getting an e-mail notification when a transactional replication stucks

  
Hi all, I've SQL Server 2005 and a transactional replication scenario with one puplisher and 11 subscribers. Sometimes one of the subscriptions fails and a manual operation is required. Is there a way to get an e-mail notification whenever the replica fails or stucks or simply degrade its performances?

How to let a non admin business user run a replication snapshot job?

  
According to the msdn library, I would have to give the non admin user (User1) SQLAgentUserRole privileges. But it also says that it has to be a local job and not a multi server job. Is the snapshot agent job a local or multi server job? Considering the snapshot agent is a local job and i make USER1 the owner of that job. He should be able to run the job rite? I need to give USER1 the ability to run sp_start_job to start the snapshot agent ONLY! (and not any other jobs). So what are the steps to do this? I also read that the only way to achieve this is by creating a proxy account? Is there an article somewhere that best describes this?  

Merge Replication, Push Subscription : The snapshot takes centuries to apply

  
Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

Issue with transactional replication on SQL 2005 SP3 using row filters with bit columns

  
We recently upgraded from SQL 2000 to SQL 2005 SP3 and I am noticing something that I think is a bug with replication from a publication that has an article in it with row filters.  It seems to be that if the row filter has columns that are bit datatype and that column is updated on the publisher to cause the row filter to exclude it, it is not removed from the subscriber.  If you update a column that is part of the row filter that is not a bit datatype it works as expected.  I can reproduce this in a test environment with a small base table.  In production this is resulting in an occasional 20598 error "The row was not found at the Subscriber when applying the replicated command". Is anyone aware of a hotfix that addresses this? I can think of a few work arounds but the table is rather large and I really would like to avoid having to pull replication, re-snap or changing datatypes on the base table etc. Any suggestions are greatly appreciated.  Thanks!
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend