January 2, 2008 at 7:33 am
SQL Gurus,
We've had Transactional Replication working for a few months now, but recently ran into a snag because we had some schema changes to the production database that needed to be propagated to the replicated database. What we did was:
1. drop the affected table from replication (with the warning that we would have to re-snapshot)
2. make necessary schema changes
3. add the table back to replication
4. re-initialize subscription taking a new snapshot immediately
I then manually kicked off the process to apply the snapshot on the destination server. When we first did this, it did not take a long time to apply the snapshot, but this time around it was still not done after a few hours.
We then started monitoring the job steps more closely, and determined that there was an error when applying a primary key index to one of the tables. Then I watched it delete every table, and start the process over again, which is why it was running for hours.
I halted the process, and told the distributor to ignore error during loading just to get the data in there, but now I want to go back and determine why it failed. I'm looking through all error logs that I can find, but I can't seem to find where the distributor keeps a detailed log of the steps that it takes so that I can find out which tables the index creation failed on.
I know...the first comment is going to be "why do you have an invalid primary key!!" That is also another curious thing that I'm trying to investigate here. The primary key is on a Case Sensitive char column (not my choice, trust me!) I was also running a trace while the data was importing to get me some more information on what was going on. I noticed a curious sp called sp_tablecollations_90 being called when the indexes were being created. I can't find any information on what this actually does, and makes me wonder if somehow sql server is messing with the collations in the background, which would cause the index creation to fail.
Sorry for the marathon post. Any ideas?
January 2, 2008 at 8:04 am
OK. Well, sometimes just writing something out loud helps you problem solve.
I decided to do a schema compare to find a list of indexes in the main database that don't exist in the replicated one using the INFORMATION_SCHEMA views. The servers are linked, so I was able to do a cross-server schema compare, however when I tried to join the two views together on table_name, I got a collation error. It turns out that the main db has the case sensitive collation turned on at the database level, but the destination db did not. Even though the tables were scripted with the correct collation (I looked in the snapshot folder to verify this), not having it set at the DB level must have caused SQL Server to get confused.
I'm going to consider this issue closed, but if (when!) schema changes happen again, I'll keep a close eye on this.
Thanks again!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply