Why does @@dbts increase after backup/restore?

Question

I have a SQL Server 2005 database, containing some tables which have a Timestamp (or RowVersion) column. I need to know the value of the current timestamp, so I use the following query: SELECT CAST(@@dbts AS BIGINT);

This will return, for example, 10505.

Immediately afterwards, without updating, inserting, ... anything, I do a BACKUP DATABASE and a RESTORE DATABASE and I run the SELECT query again. Only this time, the result is 14000, while none of the timestamps in the tables have increased.

Why/how does this happen?

Answer 1

The value for dbi_maxDbTimestamp is stored on the database boot page. (page 9 in the primary data file).

This is not written to every time a timestamp value is allocated. Instead SQL Server reserves a few thousand at a time.

For example if @@DBTS is 2000 and the dbi_maxDbTimestamp is also 2000 then SQL Server updates the value written in the boot page to 6000 the next time it needs a timestamp value.

The values from 2001 - 5999 are allocated in memory and "lost" if the database is set offline and then online again.

The backup contains the copy of the boot page that has been updated to 6000. So upon restoring it the timestamp values will start from this number. It knows nothing about any lost intermediate values.

To see this

CREATE DATABASE DbtsTest

GO

USE DbtsTest

GO

DBCC TRACEON(3604);

CREATE TABLE T (X ROWVERSION)

SELECT CAST(@@dbts AS BIGINT);

DBCC PAGE(DbtsTest,1,9,1)

On my system for a newly created database @@dbts is 2,000. The DBCC PAGE output from above is

I have highlighted the timestamp value. CAST(CAST(REVERSE(0xD007000000000000) AS BINARY(8)) AS BIGINT) = 2000

INSERT INTO T DEFAULT VALUES

SELECT CAST(@@dbts AS BIGINT);
DBCC PAGE(DbtsTest,1,9,1)

Now the @@dbts is reported as 2001 but looking at the page itself.

the timestamp value has changed. CAST(CAST(REVERSE(0x7017000000000000) AS BINARY(8)) AS BIGINT) = 6000.

Running

DBCC DBTABLE('DbtsTest')

at this point to view the DBTABLE structure shows both values

dbt_maxDbTimestamp = 6000           
dbt_dbTimestamp = 2001

Finally

BACKUP DATABASE [DbtsTest] TO  
DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup\DbtsTest.bak' 
WITH NOFORMAT, 
     NOINIT,  
     NAME = N'DbtsTest-Full Database Backup', 
     SKIP, 
     NOREWIND, 
     NOUNLOAD,  
     STATS = 10

Then looking at the backup shows it is the 6,000 figure that is written.

Restoring the database and querying SELECT CAST(@@DBTS AS BIGINT) returns 6,000 as expected.