Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Microsoft only guarantees that @@DBTS will be unique; it does not make any promises about what may or may not happen to the value after a backup/restore or any other database-level operation. What are you using this value for? Why do you care if it changes? Can't you set your baseline from the last successful restore and reset it whenever you do so? It's not like a production database should go through this sequence very often... –  Aaron Bertrand Oct 4 '13 at 13:46
    
We use a BI environment that operates on a backup of our live database. The backup is made every night. The timestamps are used for incremental updates in the BI environment. So when @@dbts increases after a restore, sometimes data ends up missing... The fundamentals of how this system works can't be changed for the moment. –  Tino Oct 4 '13 at 14:02
2  
Your system seems fundamentally broken if it is relying on any particular values for @@dbts. It isn't guaranteed. –  Martin Smith Oct 4 '13 at 14:18
1  
@Tino well you're not going to be able to change the way @@DBTS works, so something's going to have to give... –  Aaron Bertrand Oct 4 '13 at 14:18

1 Answer 1

up vote 8 down vote accepted

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

DBCC Page 1

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.

DBCC Page 2

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.

enter image description here

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

share|improve this answer
    
You can also use DBCC DBINFO(NameOfDatabase) [WITH TABLERESULTS]; to see the value of dbi_maxDbTimestamp. –  Paul White Oct 5 '13 at 6:36
    
@PaulWhite - Though in the event that the timestamp goes past 0xFFFFFFFFFFFFFF7F that seems to return the wrong results (stuck at 9223372036854775807 whilst @@DBTS reports 0x8000000000000001 and DBCC PAGE shows 0x9f0f000000000080) –  Martin Smith Oct 5 '13 at 12:52
    
+1 for irrefutable proof from DBCC PAGE –  Max Vernon Oct 5 '13 at 21:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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