Identity column increases abruptly, abnormally.

Hi
I am facing a big problem. please help.
We r having SQL Server 2000 Enterprise.
I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
delete.
These triggers are inserting the audit for these actions into another Table2.
Table2 has an identity column which increment by 1 automatically.
Problem:
Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
As identity column increases automatically, I am helpless.
Is there any patch/service pack for this.
Please Suggest.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UKHi,
Some thing new :)
Can you verify the application/code which is used inside tge trigger and
confirm whether you have too many rollbacks happening.
This is the only thing I can think off now.
This sort of issue might also happen if the server does not goes off through
a graceful shutdown.
Thanks
Hari
SQL Server MVP
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another
> Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Also check whether there is replication involved with range based identity.
May be one of the subscribers has an identity range starting from 500 +...
"Sameer" wrote:
> Hi
> I am facing a big problem. please help.
> We r having SQL Server 2000 Enterprise.
> I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> delete.
> These triggers are inserting the audit for these actions into another Table2.
> Table2 has an identity column which increment by 1 automatically.
> Problem:
> Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> As identity column increases automatically, I am helpless.
> Is there any patch/service pack for this.
> Please Suggest.
>
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens
> Bracknell
> UK|||Thanks Hari for ur prompt reply.
ya there r some possibilities of rollbacks.
i really appreciate ur guess.
Can u please explain what happens to a identity column & its values when
rollback comes into picture?
Please help .. I may be able to solve the problem by ur valuable feedback.
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hari Prasad" wrote:
> Hi,
> Some thing new :)
> Can you verify the application/code which is used inside tge trigger and
> confirm whether you have too many rollbacks happening.
> This is the only thing I can think off now.
> This sort of issue might also happen if the server does not goes off through
> a graceful shutdown.
> Thanks
> Hari
> SQL Server MVP
> "Sameer" <Sameer@.discussions.microsoft.com> wrote in message
> news:5B883E39-52D6-4D8F-9322-781028306052@.microsoft.com...
> > Hi
> >
> > I am facing a big problem. please help.
> > We r having SQL Server 2000 Enterprise.
> >
> > I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
> > delete.
> > These triggers are inserting the audit for these actions into another
> > Table2.
> > Table2 has an identity column which increment by 1 automatically.
> >
> > Problem:
> > Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
> > As identity column increases automatically, I am helpless.
> >
> > Is there any patch/service pack for this.
> >
> > Please Suggest.
> >
> >
> > --
> > Regards
> > Sameer Gupta
> > C# Designer & Developer
> > Siemens
> > Bracknell
> > UK
>
>|||On Fri, 25 Aug 2006 03:19:01 -0700, Sameer wrote:
>Hi
>I am facing a big problem. please help.
>We r having SQL Server 2000 Enterprise.
>I have 3 triggers (FOR type) on a Table1 on each action - insert, update &
>delete.
>These triggers are inserting the audit for these actions into another Table2.
>Table2 has an identity column which increment by 1 automatically.
>Problem:
>Identity column increases itself abruptly sometimes by 100+, 500+, 1000+.
>As identity column increases automatically, I am helpless.
>Is there any patch/service pack for this.
Hi Sameer,
It might help if you posted some code.
--
Hugo Kornelis, SQL Server MVP|||On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>Thanks Hari for ur prompt reply.
>ya there r some possibilities of rollbacks.
>i really appreciate ur guess.
>Can u please explain what happens to a identity column & its values when
>rollback comes into picture?
Hi Sameer,
Since the identity value generation is performed outside of the
transaction context, the values generated for the inserts that were
rolled back are not re-used. See the repro below.
CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
OtherCol int NOT NULL CHECK (OtherCol > 0));
-- First row is inserted
INSERT INTO Demo (OtherCol) VALUES (1);
-- Second row explicitly rolled back
BEGIN TRAN;
INSERT INTO Demo (OtherCol) VALUES (2);
ROLLBACK TRAN;
-- Third row implicitly rolled back because constraint is violated
INSERT INTO Demo (OtherCol) VALUES (-3);
-- Fourth row is inserted
INSERT INTO Demo (OtherCol) VALUES (4);
-- Check results
SELECT * FROM Demo;
DROP TABLE Demo;
Hugo Kornelis, SQL Server MVP|||How can i solve this identity increase problem ... as identity is not in
context of transaction.
Please suggest a solution to rollback identity with transaction.
i'll be grateful to u.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens Business Services
Bracknell
UK
"Hugo Kornelis" wrote:
> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
> >Thanks Hari for ur prompt reply.
> >
> >ya there r some possibilities of rollbacks.
> >i really appreciate ur guess.
> >Can u please explain what happens to a identity column & its values when
> >rollback comes into picture?
> Hi Sameer,
> Since the identity value generation is performed outside of the
> transaction context, the values generated for the inserts that were
> rolled back are not re-used. See the repro below.
> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
> OtherCol int NOT NULL CHECK (OtherCol > 0));
> -- First row is inserted
> INSERT INTO Demo (OtherCol) VALUES (1);
> -- Second row explicitly rolled back
> BEGIN TRAN;
> INSERT INTO Demo (OtherCol) VALUES (2);
> ROLLBACK TRAN;
> -- Third row implicitly rolled back because constraint is violated
> INSERT INTO Demo (OtherCol) VALUES (-3);
> -- Fourth row is inserted
> INSERT INTO Demo (OtherCol) VALUES (4);
> -- Check results
> SELECT * FROM Demo;
> DROP TABLE Demo;
>
> --
> Hugo Kornelis, SQL Server MVP
>|||Basically, you can't keep an identify column from skipping values in the
face of rollbacks unless the transactions are serialized. You need to assign
your own number and then wait for the transaction that uses that number to
commit before assigning the next one. This will have lousy performance but
it's the only way that you can roll back a transaction without missing a
number. For example if you set the sequence number to max sequence number +
1, you need to wait until that insert commits before you can set the next
one because the next transaction needs the new number before it assigns its
number. If you assign the number outside of the transaction with an
identity column, rollbacks will lose the number because it is used and not
written to the table.
Bottom line, you can't guarantee sequential numbers without sequential
transactions so your choices are to make the transactions sequential or to
live with skipped numbers.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sameer" <Sameer@.discussions.microsoft.com> wrote in message
news:D229D6C8-38C3-4CC5-A972-368670CB88EA@.microsoft.com...
> How can i solve this identity increase problem ... as identity is not in
> context of transaction.
> Please suggest a solution to rollback identity with transaction.
> i'll be grateful to u.
> --
> Regards
> Sameer Gupta
> C# Designer & Developer
> Siemens Business Services
> Bracknell
> UK
>
> "Hugo Kornelis" wrote:
>> On Fri, 25 Aug 2006 06:13:02 -0700, Sameer wrote:
>> >Thanks Hari for ur prompt reply.
>> >
>> >ya there r some possibilities of rollbacks.
>> >i really appreciate ur guess.
>> >Can u please explain what happens to a identity column & its values when
>> >rollback comes into picture?
>> Hi Sameer,
>> Since the identity value generation is performed outside of the
>> transaction context, the values generated for the inserts that were
>> rolled back are not re-used. See the repro below.
>> CREATE TABLE Demo (IdentCol int NOT NULL IDENTITY,
>> OtherCol int NOT NULL CHECK (OtherCol > 0));
>> -- First row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (1);
>> -- Second row explicitly rolled back
>> BEGIN TRAN;
>> INSERT INTO Demo (OtherCol) VALUES (2);
>> ROLLBACK TRAN;
>> -- Third row implicitly rolled back because constraint is violated
>> INSERT INTO Demo (OtherCol) VALUES (-3);
>> -- Fourth row is inserted
>> INSERT INTO Demo (OtherCol) VALUES (4);
>> -- Check results
>> SELECT * FROM Demo;
>> DROP TABLE Demo;
>>
>> --
>> Hugo Kornelis, SQL Server MVP|||On Sat, 26 Aug 2006 10:21:02 -0700, Sameer wrote:
>How can i solve this identity increase problem ... as identity is not in
>context of transaction.
>Please suggest a solution to rollback identity with transaction.
>i'll be grateful to u.
Hi Sameer,
IDENTITY should never be used if the actuall values matter. It is
intended to be used as a surrogate key, for internal use by the database
logic but not exposed to the user.
If you care aboout gaps in your IDENTITY sequence, then you shouldn't
use IDENTITY at all. Find the current maximum value in the table, add
one and use that as your new value. Use locks to ensure that two
connections executing the same coode at the same time won't get the same
maximum value, then try to insert new rows with the same new value. Your
concurrency will suffer, but you're rid of gaps - until you manuallly
delete a row, of course.
--
Hugo Kornelis, SQL Server MVP|||Thanks Mr. Hugo, Mr. Hari & Mr. Roger for ur kind suggestions.
I am fully able diagnose what's happening. I just come out of a potential
turn around for the issue.
Thanks very much. I am grateful for ur prompt replies.
--
Regards
Sameer Gupta
C# Designer & Developer
Siemens
Bracknell
UK