TIMESTAMP not unique! help!
-
This is the best problem I've found to date;
SELECT * FROM Policy WHERE Action_Timestamp =0x00000001107AA2C7
WCPN ... Action_Timestamp
----------- ... ------------------
115184 ... 0x00000001107AA2C7
417974 ... 0x00000001107AA2C7
(2 row(s) affected)
This has been reported to me before by one of the developers, but this is the first time I've seen it. I didn't believe him before, but had to eat my words over this one. Can anybody explain to me why this is happening and how to stop it from occuring?
My understanding of the Timestamp (rowversion) field is that it remains unique across the entire database. This seems consistent with the Books Online spiel.
We're on SQL Server 2000 Standard Edition SP3 & Windows 2000 SP3. The machine is a Quad-Xeon 500MHz system.
Edited by - Mark_Holst on 05/13/2003 01:16:46 AM
-
I had a similar experience once with a unique index.
After lots of digging we found out that de clustered index was broken. ( The clustered index was on that field).
DBCC couldn't see anything wrong with it because it just follows the clustered index. But there were records in the table that were not ! in the clustered index...... Very strange and I have seen it only once.....
After dropping all the indexes and rebuilding them again everything was fine.
You could try it.....
-
Shame it happened on a production system, as I couldn't resolve it at my leisure. We had to manually update the record to force the Timestamp value to update itself.
If I ever come across it on a development system I'll have to take advantage of the rare opportunity to check it out in more detail.
Actually... I might check the backups & transaction log backup, they might have a copy of the entire scenario for me. It'll take me a day or so to get back to it though.
Edited by - Mark_Holst on 05/13/2003 02:38:08 AM
-
I'd like to hear the result if you figure it out.
Andy
-
If you can find how to reproduce from yuor logs then send to MS. But I sure would make them aware as this is supposed to be unique within a single Database.
quote:
which are guaranteed to be unique within a databaseIf it is not then MS needs to know or others may find big issues. I check around on the KB site and found nothing. Nor have I found this complaint elsewhere. Other systems using a timestamp type method have failed in the past and there have been comments made in concern with MS. This would be a good thing to find out and have corrected if is a problem.
-
I've managed to pull an old backup.
I'm a little disappointed I didn't get more commment on this issue.
It's actually quite interesting, since the two records in question were written around 9 months apart.
What I need to know is: how do I contact MS regarding this issue? I've never really had to contact for anything other than a missing XP Home key, but that's another story.
-
I thought that timestamp values were only guaranteed to be unique within a single table, not an entire database. In either case, you still shouldn't have that problem. I had never heard of that occurring either - but as suggested, try to rebuild indices.
If it is something that might occur again on that production system, why not write some sort of stored proc that gets run every X mins - the stored proc runs a query similar to:
select timestampCol
from Table
group by timestampCol
having count(*) > 1
if records are returned, then have an immediate DB backup performed (only really possible if small DB) to further investigate.
Would be interesting to see if it happens again (ala, corrupted table). Sorry couldn't offer any actual help - just conjecture 🙂
Ian
-
Mark, I think the low replies is a combination of timestamps not being used as much as identity, and no one else having seen the problem.
For MS, you can find support info here:
http://support.microsoft.com/default.aspx?scid=fh%3Ben-us%3Bcntactms&LN=EN-US&x=8&y=5
If you work for a MS Partner or have an MSDN subscription, you may have some free support calls you can use. The way support works it's $250 up front, they refund if they agree it's their fault (and they are good about this). You can also submit free on the MS newsgroups.
From BOL:
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
Andy
-
Thinkning about this. Did you have a failure at anytime where you had to rebuild the DB and restore data? Or have to moved the DB from another server between those times?
-
Timestamps are unique across an entire database, that I'm quite sure of.
*scratches head*
To be honest I can't remember the database having to be restored at any point in the last 5 years here.
We did move it from SQL Server 6.5 to 2000 sometime around when that first record was written, but we did a server to server upgrade. So I would have to assume that the records were inserted into a fresh database "DTS style" through that method.
Probably something for a separate question thread - how do most people implement multi-user support other than using timestamps? Date fields? They wouldn't be reliable enough for rapid updates.
I understand that you can use record locking but not when waiting on user input.
I've stuck it on the Microsoft Newsgroup, hopefully someone from Microsoft is having a look. We've already got the Microsoft Server guys scratching their heads over a DFS/XP problem, I think it's on the 2nd week.
-
Agree with Andy Warren.
Never used timestamps.
Did spend some time reading about timestamps in BOL 2000.
-
I'm lucky enough enough to work in a system where we either A, do optimistic locking and last update wins, or we update an owner column with the user's systemid so that no one else can alter it. I agree that timestamps are handy for determining when collisions have occurred, handling those collisions is...interesting.
Andy
-
I definitely would notify MS and supply them as much information on this as possible. The very first paragraph on timestamp data type (BOL, Index tab, timestamp data type, then timestamp data type Transact-SQL reference) says:
timestamp...guaranteed to be unique within a database.
-SQLBill
-
I seem to have isolated the problem. One of the other developers said the problem came up again so I checked it out in more detail... this time with the query shown at the bottom.
It returned all of the tables in the database utilising timestamp columns that had duplicates. Around 100 records in the entire database. Each row in our database has a Action_Date, so using that I compared it to the date from the date we did the SQL Server 6.5 --> 2000 changeover. All of the records came up before that particular time. So I would have to conclude that it had something to do with the SQL Server Upgrade Wizard. I figure the timestamp values would have been regenerated during the population of the new database, so I can't see how I could lay blame on the original 6.5 database.
If anyone else out there has performed an upgrade, could I ask that they run the query below and let us know the results? Thanks!
Declare @curTableList Cursor
Declare @strTableName VarChar(255)
Declare @strColumnName VarChar(255)
Declare @strQuery VarChar(2048)
Set @curTableList = Cursor Local Fast_Forward Read_Only For
Select sysobjects.name, syscolumns.name
From sysobjects Inner Join syscolumns On (sysobjects.id = syscolumns.id)
Inner Join systypes On (systypes.xtype = syscolumns.xtype)
Where systypes.name = 'Timestamp'
And sysobjects.xtype = 'U'
Group By sysobjects.name, syscolumns.name
Open @curTableList
Fetch Next From @curTableList Into @strTableName, @strColumnName
While @@Fetch_Status = 0
Begin
Print '------------------------------------'
Print @strTableName + ': ' + @strColumnName
Print ''
Set @strQuery = 'Select ' + @strColumnName + ' ' + @strTableName + '_Timestamp ' +
'From ' + @strTableName + ' ' +
'Group By ' + @strColumnName + ' ' +
'Having Count(*) > 1'
Exec (@strQuery)
Print '------------------------------------'
Fetch Next From @curTableList Into @strTableName, @strColumnName
End
Close @curTableList
-
Microsoft to the rescue!
After modifying my script to determine the maximum Timestamp value in the database, I ran
Select @@DBTS
to show what the current timestamp value is for the database. It turned out to be well less than the values in the tables. So it was just going to keep getting worse.
Eventually I had to run an undocumented command to reset the value to something higher.
DBCC CHECKDBTS (11, 0x0000000150000000)
Situation resolved. What I would like to know from MS is how this happened and what to do to avoid the situation.
I'll keep y'all informed.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply