@@DBTS changes by itself (overnight?)

Question

@@DBTS changes by itself (overnight?)

sqlnorth

Mr or Mrs. 500

Points: 593
More actions
July 20, 2022 at 8:35 am

#4065802

I have created a new DB (no backups) 1 table with a few rows, 1 field is a ROWVERSION field named Vercol
When I run this, the 2 outputs are the same but when I run in the morning @@DBTS has moved on. Checking regularly during the day no change. (This is on local SQL2019 and my laptop is rebooted nightly, in case restarting SQL changes @@DBTS?)
Where can I see which element the new @@DBTS value relates to when the DB has 1 table only and it isn't there.
All comments say this field is for local database, which is not being modified or backed up etc. so no idea why @@DBTS is changing, and it makes using it impossible unless I have misunderstood what it should do.
This database only has this 1 table
DECLARE @x ROWVERSION
SET @X = (SELECT MAX(vercol) FROM dbo.[Table1])
SELECT @x AS MAXVerCol
SELECT @@DBTS AS DBTS
This is for a project to pull data down from a place that uses rowversion, so i just want to download whose RowVersion is > what I have locally.
So was going to run query like select fields from linkedserver.db.dbo.tableX where rowversionfield > @@DBTS for example.
But i think I will have to use the @X variable instead as more reliable

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101200 More actions · Answer 1

What is the definition of table1?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sqlnorth Mr or Mrs. 500 Points: 593 More actions · Answer 2

thanks for the reply.

It is really simple isolated table, just for testing

PriKey INT, VerCol rowversion, FieldB varchar(50)

I have now also copied this to another box that does not get SQL restarted, I know this isn't the reason, but just to rule that out.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

sqlnorth wrote:

thanks for the reply.
It is really simple isolated table, just for testing
PriKey INT, VerCol rowversion, FieldB varchar(50)
I have now also copied this to another box that does not get SQL restarted, I know this isn't the reason, but just to rule that out.

If your local table is simply being used to backup data from other tables, I suggest that you do not use ROWVERSION as the VerCol datatype. Instead use BINARY(8) or VARBINARY(8).

sqlnorth Mr or Mrs. 500 Points: 593 More actions · Answer 4

Hi Phil,

Thanks for the info and suggestion, yes the source table/field is a rowversion column and I need to query it to just pull back records (daily/hourly etc.) that have changed since my last download, so my local table I will make binary(8) sure, thanks.

So solution I am thinking of, will be something like this where the remote table has the real ROWVERSION field and my local table will be BINARY(8)

DECLARE @LatestChange binary(8)

SET @LatestChange = (Select MAX(VerCol) from localtable)

SELECT blah, etc From Linkedserver.DBname.dbo.RemoteTable WHERE RemoteVerCol > @LatestChange

Is using MAX reliable/preferred approach to this exercise. It looks safe and a perfectly sound approach but just to ask.

Was just very curious around @@DBTS as found it changes overnight when zero change to the only table in the database

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

Your suggested code looks fine to me. You could simplify it a little:

SELECT @LatestChange = MAX(VerCol) from localtable

sqlnorth Mr or Mrs. 500 Points: 593 More actions · Answer 6

Hi Phil,

Absolutely can do that subtle change, but thank you for confirming approach of MAX(field) is good when used against Rowversion.

I thought it would be but thanks.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

No problem.

Are there any deletes happening in the remote table?

sqlnorth Mr or Mrs. 500 Points: 593 More actions · Answer 8

No deletes, I've already asked that 🙂

They just set a status of the record to 'closed/lost/cancelled' etc.

It is curious though, if you

create a blank DB, create a single table with a ROWVERSION field, then insert 1 record and check max(rowversion field) and @@DBTS they match but tomorrow @@DBTS is higher.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 9

From the ROWVERSION help:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock.

I don't know exactly what that means, but the fact that the word 'time' is used is perhaps behind the reason for the behaviour you are seeing.

sqlnorth Mr or Mrs. 500 Points: 593 More actions · Answer 10

Yes I guess the physical 'date' is somehow influencing @@DBTS, as the database is not changing at all.

I haven't checked at 11:59pm and 00:01am 🙂 just end of working day and start of working day

The way I interpreted it, was that it only changes on an INSERT/UPDATE