5

So, I'm an "enterprise software developer" (welcome to hell, here's your accordion), meaning I'm not a DBA and I'm inheriting something that's been around for years and very few people have a crisp explanation for much at all.

My question: what, exactly, is msrepl_tran_version? We have it in all our tables (a rather large number), but I don't believe we're using replication. I guess we did at some point in the past, maybe.

However, we seem to be using this field for concurrent-update detection; at update time, we check this field to see if it's changed, and, if so, reject the user's change (error on the screen, force them to refresh, etc., etc.0

Does this field get updated magically, somehow? Should I be looking for a trigger? (I doubt it.) If we're not using replication, what do changes in this field affect? I can't seem to actually find a good overview explanation of what this column is.

TIA.

John.

4

what, exactly, is msrepl_tran_version? We have it in all our tables (a rather large number), but I don't believe we're using replication. I guess we did at some point in the past, maybe.

when you have configured Transactional replication with updatable subscriptions, replication adds the column msrepl_tran_version to every table. If the table is dropped from the publication, the msrepl_tran_version column is not removed.

Check with your DBA to see if you still have replication or not.

If you are not using Replication, then to see how this column gets updated, I would suggest you to use profiler.

IF this column is not being used anymore, you should be good to remove/drop it. Refer to my answer with script here.

3
  • Thanks. The buzzwords helped me find this: technet.microsoft.com/en-us/library/ms151794(v=sql.105).aspx Looks like this feature ("Updatable Subscriptions for Transactional Replication") is slated to be removed? Does that mean the msrepl_tran_version column will go dormant (not dropped, but not updated either)?
    – JohnL4
    Sep 5 '13 at 17:47
  • @JohnL4 Yes it is depreciated as per (technet.microsoft.com/en-us/library/ms151718.aspx). Does that mean the msrepl_tran_version column will go dormant (not dropped, but not updated either)? I cant vouch on that, but in your environment if it is not needed and you figure that out using Profiler or tracing, then you can happily drop that column using the script that I referenced.
    – Kin Shah
    Sep 5 '13 at 19:10
  • As a followup, for those who come after, we are using that column (for concurrent-update detection), but it's separate from replication. We have explicit code in our update procs to set it to NEWID(). I assume the MS-generated trigger does the same, so, in the case when replication is actually turned on (customer choice), we eat two ids with every update. Oh well. In more-recent code, we have switched to using our own column of type ROWVERSION.
    – JohnL4
    Sep 5 '13 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.