Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What causes timestamp/rowversion to update? Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 1:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 3:05 PM
Points: 17, Visits: 63
We've got transactional replication setup on a number of tables in a database from one SQL 2008 server to another. Many of the tables have a timestamp/rowversion column defined, and in our table replication options, we have "convert timestamp to binary" selected to make sure the values stay consistent between the two databases.

Periodically, I've noticed that there will be a large backlog of replicated transactions to deliver, and looking through at what the replication account is doing, the vast majority of the operations are just updating the timestamp column to a new value.

We're trying to figure out what might cause just the timestamp/rowversion column to update on the source db, and sadly I'm not finding a ton of info. It appears that standard maintenance operations (index reorgs/rebuilds, etc) don't update it, anyone have any other suggestions?
Post #1444583
Posted Friday, April 19, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 12,905, Visits: 32,168
could it be someone was cleaning up data and used a REPLACE without a WHERE statement?

the rowversion gets incrmeneted even thought here was no "real" change:
CREATE TABLE  WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30),
MyRowVersion ROWVERSION
)
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'
--affects ALL rows in the table, but no real change to data, but rowversion is modified.
update whatever set descrip = REPLACE(descrip,'SASQUATCH','')
--vs
--no matching rows, rowversion preserved.
update whatever set descrip = REPLACE(descrip,'SASQUATCH','') WHERE DESCRIP LIKE '%SASQUATCH%'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1444589
Posted Friday, April 19, 2013 2:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 3:05 PM
Points: 17, Visits: 63
Lowell (4/19/2013)
could it be someone was cleaning up data and used a REPLACE without a WHERE statement?

the rowversion gets incrmeneted even thought here was no "real" change:


Looks like doing something stupid like running an update and setting a field to the value that already exists does this too, I'm bugging my devs more, but of course no one wants to fess up.
Post #1444599
Posted Friday, April 19, 2013 2:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
dspeterson (4/19/2013)
Lowell (4/19/2013)
could it be someone was cleaning up data and used a REPLACE without a WHERE statement?

the rowversion gets incrmeneted even thought here was no "real" change:


Looks like doing something stupid like running an update and setting a field to the value that already exists does this too, I'm bugging my devs more, but of course no one wants to fess up.


Consider that almost 100% of updates sprocs don't look to see if the values have changed. If they did they would be horrible to write and they would be super slow.

create MyUpdateProc
(
@Col1 varchar(10),
@ID int
) as
update MyTable
set Col1 = @Col1
where ID = @ID

The above is a super simplified typical update sproc. This would cause the rowversion to be modified.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1444605
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse