Identity Value missing -- SQL 2000

Question

Identity Value missing -- SQL 2000

arj

SSC-Addicted

Points: 444
More actions
September 26, 2007 at 8:30 am

#181213

Hi All,
It's new problem facing by me. We are using SQL server 2000 and
All the tables are having Identity property true.
Suddenly an error is reported by enduser saying that few transactions are missed(3 transactions were missed from given identity series) from the table.
Then i have gone through all the Log files,event log .. for any specific occurances at that specific time. Except Log backup entry i didn't find any specific entries.
Could any one please tell ...
a) what are the chances for identity values missing.?
b) If the values are not missed and some one have deleted them from table. How to trace who have deleted that transactions.
--arj
Kindest Regards,
arj

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you start a transaction, insert, and then rollback due to a business decision, your identity will increment, but no error is thrown. a missing identity value does not necessarily mean anything bad happened.

example:

set nocount on

CREATE TABLE #sample (sampleid int identity(1,1), sampletext varchar(30) )

insert into #sample(sampletext) values ('one')

insert into #sample(sampletext) values ('two')

select * from #sample

begin tran

insert into #sample(sampletext) values ('three')

insert into #sample(sampletext) values ('four')

select * from #sample

rollback tran

insert into #sample(sampletext) values ('five, not three')

select * from #sample

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

arj SSC-Addicted Points: 444 More actions · Answer 2

Hi,

Thank you for the clarification given. It helps me to give a report to my superiors...

Could any one please tell me how to track the DML operations occuring on SQL tables in SQL 2000. One thing we can capture those operations using triggers.Is there any other way to find out the changes at database level...

Regards,

arj.

Kindest Regards,

arj

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

arj (9/27/2007)
Hi,
Thank you for the clarification given. It helps me to give a report to my superiors...
Could any one please tell me how to track the DML operations occuring on SQL tables in SQL 2000. One thing we can capture those operations using triggers.Is there any other way to find out the changes at database level...
Regards,
arj.

Depends on how much information you would like to get. I've seen a solution that uses traces and searches for specific keywords. Triggers are also a solution, but they can be enabled/disabled if permissions are not set up properly. If access is properly restricted you can restrict all data modifications to stored procedures, which can do some logging. Alternatively you can use third party tools to see either the net result of changes (what the data differences are between a backup and the current database (SQL Data Compare), or what is in the transaction log)

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software