Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2010-11-02 : 15:17:56
|
| I was about to post the following when I tried creating a new snapshot (right-click on new subscription and select Reinitialize, then generate new snapshot), which worked. The bad thing is that a ton of deadlocks occurred in the production database when it locked all the tables to create a snapshot.I set up a new replication subscription (transactional) with an already working publisher (both are SQL Server 2008 R2). Everything indicates that the new replication is working (Replication Monitor and job history), but no new data is showing up in the subscriber database.I set it up by backing up the database on the other subscription server, then restoring the backup to the new subscription server. I then used the GUI to create the new subscription. The job history says "In progress" so I don't get it. I would think it wouldn't take hours for it to initialize since the backup was only 20 minutes old and no DDL changes have occurred. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-02 : 20:25:48
|
| did the new subscriber get the snapshot? if not, you're going to need to generate a new one |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2010-11-03 : 10:49:42
|
| It did, thanks. Everything is copacetic now. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-02-11 : 17:16:19
|
| The deadlocks continue to happen every time I restart replication, which is every 3 weeks or so when we release database schema changes. They only happen for the 5-10 minutes it takes to reinitialize, during which exclusive (I think) locks are placed on all the tables as the data is transferred. Is this a symptom of poor database design or does everybody have this problem? How do you deploy schema changes to the publisher without breaking replication? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-11 : 19:48:31
|
| Schema changes to already published articles will be replicated without any action from you.Are you adding new articles?I assume you mean deadlocks at the subscriber? |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-02-12 : 00:40:57
|
| Nope, the deadlocks are at the publisher. I'm not sure if they happen when I'm adding objects or changing objects or both, I'll have to look into that if I can. |
|
|
|
|
|
|
replication in progress but no new data