|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Table reaching max value of identity very earlyHopefully one of you has seen this before...... We have a new which we have seen reach the max value on four occasions in testing. When I look at the amount of data in the table, it contains about 15-20 rows. We are not doing any automated testing that inserts billions for rows and then deletes them. This is not table that does or will see a large volume of transactions. If I query the Max value of the identity column, I get 56, but when I run the dbcc checkident I get back the following: Checking identity information: current identity value '2147483647', current column value '2147483647'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. I've run the dbcc checkident with the reseed opting to fix the problem, but I'm searching for the root cause. Any Ideas? Steve Updated post to correct a copy/paste error.
Show quote "Steve Reid" wrote: > Hey guys, > > Hopefully one of you has seen this before...... > > We have a new table which we have seen reach the max identity value on four occasions in > testing. When I look at the amount of data in the table, it contains about > 15-20 rows. We are not doing any automated testing that inserts billions > for rows and then deletes them. This is not table that does or will see a > large volume of transactions. If I query the Max value of the identity > column, I get 56, but when I run the dbcc checkident I get back the following: > > Checking identity information: current identity value '2147483647', > current column value '2147483647'. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > > I've run the dbcc checkident with the reseed opting to fix the problem, but > I'm searching for the root cause. > > Any Ideas? > Steve What is the seed/increment? Also, perhaps there are tons of rollbacks... if
an insert fails the number is still made unavailable. run this repro and observe the gaps that are in the table due to failures/rollbacks: USE tempdb; GO CREATE TABLE dbo.foo ( id INT IDENTITY(1,1), bar VARCHAR(5) NOT NULL ); GO -- succeeds: INSERT dbo.foo(bar) SELECT 'fun'; GO -- fails: INSERT dbo.foo(bar) SELECT NULL; GO -- succeeds: INSERT dbo.foo(bar) SELECT 'it'; GO -- fails: INSERT dbo.foo(bar) SELECT 'should'; GO -- rolled back: BEGIN TRAN; INSERT dbo.foo(bar) SELECT 'be'; ROLLBACK TRAN; GO -- succeeds INSERT dbo.foo(bar) SELECT 'isn''t'; GO -- most people expect 1,2,3 but that is not the case: SELECT id, bar FROM dbo.foo ORDER BY id; GO DROP TABLE dbo.foo; GO Show quote "Steve Reid" <Steve R***@discussions.microsoft.com> wrote in message news:18DB375F-5E92-45DA-B4C9-80A34F294CF6@microsoft.com... > Hey guys, > > Hopefully one of you has seen this before...... > > We have a new which we have seen reach the max value on four occasions in > testing. When I look at the amount of data in the table, it contains > about > 15-20 rows. We are not doing any automated testing that inserts billions > for rows and then deletes them. This is not table that does or will see > a > large volume of transactions. If I query the Max value of the identity > column, I get 56, but when I run the dbcc checkident I get back the > following: > > Checking identity information: current identity value '2147483647', > current column value '2147483647'. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > > I've run the dbcc checkident with the reseed opting to fix the problem, > but > I'm searching for the root cause. > > Any Ideas? > Steve The Identity was initially seeded at 1 with an increment of 1.
Also, not a case where tons of rollbacks are occurring. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > What is the seed/increment? Also, perhaps there are tons of rollbacks... if > an insert fails the number is still made unavailable. run this repro and > observe the gaps that are in the table due to failures/rollbacks: > > > > USE tempdb; > GO > > CREATE TABLE dbo.foo > ( > id INT IDENTITY(1,1), > bar VARCHAR(5) NOT NULL > ); > GO > > -- succeeds: > INSERT dbo.foo(bar) SELECT 'fun'; > GO > > -- fails: > INSERT dbo.foo(bar) SELECT NULL; > GO > > -- succeeds: > INSERT dbo.foo(bar) SELECT 'it'; > GO > > -- fails: > INSERT dbo.foo(bar) SELECT 'should'; > GO > > -- rolled back: > BEGIN TRAN; > INSERT dbo.foo(bar) SELECT 'be'; > ROLLBACK TRAN; > GO > > -- succeeds > INSERT dbo.foo(bar) SELECT 'isn''t'; > GO > > -- most people expect 1,2,3 but that is not the case: > SELECT id, bar FROM dbo.foo ORDER BY id; > GO > > DROP TABLE dbo.foo; > GO > > > > > "Steve Reid" <Steve R***@discussions.microsoft.com> wrote in message > news:18DB375F-5E92-45DA-B4C9-80A34F294CF6@microsoft.com... > > Hey guys, > > > > Hopefully one of you has seen this before...... > > > > We have a new which we have seen reach the max value on four occasions in > > testing. When I look at the amount of data in the table, it contains > > about > > 15-20 rows. We are not doing any automated testing that inserts billions > > for rows and then deletes them. This is not table that does or will see > > a > > large volume of transactions. If I query the Max value of the identity > > column, I get 56, but when I run the dbcc checkident I get back the > > following: > > > > Checking identity information: current identity value '2147483647', > > current column value '2147483647'. > > DBCC execution completed. If DBCC printed error messages, contact your > > system administrator. > > > > I've run the dbcc checkident with the reseed opting to fix the problem, > > but > > I'm searching for the root cause. > > > > Any Ideas? > > Steve > > > How do you know? Are you running profiler from the time you reseed to the
time the error starts occuring? Did you try any of the repros I sent? Do they not seem to lead to the exact scenario you are experiencing, just on a smaller and easier-to-reproduce scale? I suggest not being so quick to dismiss the possibilities. Those values are being consumed by something that is either trying to insert rows or is doing so and then removing them. There are no gremlins in the box that are messing with the RESEED value on you. A Show quote "Steve Reid" <SteveR***@discussions.microsoft.com> wrote in message news:BB1C5E71-B7C3-403D-8A7E-18008BF41BB6@microsoft.com... > The Identity was initially seeded at 1 with an increment of 1. > > Also, not a case where tons of rollbacks are occurring. > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> What is the seed/increment? Also, perhaps there are tons of rollbacks... >> if >> an insert fails the number is still made unavailable. run this repro and >> observe the gaps that are in the table due to failures/rollbacks: >> >> >> >> USE tempdb; >> GO >> >> CREATE TABLE dbo.foo >> ( >> id INT IDENTITY(1,1), >> bar VARCHAR(5) NOT NULL >> ); >> GO >> >> -- succeeds: >> INSERT dbo.foo(bar) SELECT 'fun'; >> GO >> >> -- fails: >> INSERT dbo.foo(bar) SELECT NULL; >> GO >> >> -- succeeds: >> INSERT dbo.foo(bar) SELECT 'it'; >> GO >> >> -- fails: >> INSERT dbo.foo(bar) SELECT 'should'; >> GO >> >> -- rolled back: >> BEGIN TRAN; >> INSERT dbo.foo(bar) SELECT 'be'; >> ROLLBACK TRAN; >> GO >> >> -- succeeds >> INSERT dbo.foo(bar) SELECT 'isn''t'; >> GO >> >> -- most people expect 1,2,3 but that is not the case: >> SELECT id, bar FROM dbo.foo ORDER BY id; >> GO >> >> DROP TABLE dbo.foo; >> GO >> >> >> >> >> "Steve Reid" <Steve R***@discussions.microsoft.com> wrote in message >> news:18DB375F-5E92-45DA-B4C9-80A34F294CF6@microsoft.com... >> > Hey guys, >> > >> > Hopefully one of you has seen this before...... >> > >> > We have a new which we have seen reach the max value on four occasions >> > in >> > testing. When I look at the amount of data in the table, it contains >> > about >> > 15-20 rows. We are not doing any automated testing that inserts >> > billions >> > for rows and then deletes them. This is not table that does or will >> > see >> > a >> > large volume of transactions. If I query the Max value of the >> > identity >> > column, I get 56, but when I run the dbcc checkident I get back the >> > following: >> > >> > Checking identity information: current identity value '2147483647', >> > current column value '2147483647'. >> > DBCC execution completed. If DBCC printed error messages, contact your >> > system administrator. >> > >> > I've run the dbcc checkident with the reseed opting to fix the problem, >> > but >> > I'm searching for the root cause. >> > >> > Any Ideas? >> > Steve >> >> >> > If I query the Max value of the identity Too many people assume that the MAX value and the current seed are the same. > column, I get 56, but when I run the dbcc checkident I get back the > following: > > Checking identity information: current identity value '2147483647', > current column value '2147483647'. Sometimes they are, but only on a perfect system. USE tempdb; GO CREATE TABLE dbo.foo ( id INT IDENTITY(1,1), bar VARCHAR(5) NOT NULL ); GO -- succeeds: INSERT dbo.foo(bar) SELECT 'fun'; GO -- fails: INSERT dbo.foo(bar) SELECT NULL; GO INSERT dbo.foo(bar) SELECT NULL; GO INSERT dbo.foo(bar) SELECT NULL; GO INSERT dbo.foo(bar) SELECT NULL; GO -- this is 1 SELECT MAX(id) FROM dbo.foo; GO -- here you assume 1, but it's 5, representing -- 1 successful insert and 4 failed inserts DBCC CHECKIDENT('dbo.foo'); GO DROP TABLE dbo.foo; GO What it sounds like happened is you have a ton of failed inserts that you are not noticing, either because they are in something like VB with ON ERROR RESUME NEXT or nobody is reporting errors. Here is a quick way to demonstrate your exact scenario using TINYINT instead of INT, which will reach the maximum at 255 failed inserts instead of 2 billion. USE tempdb; GO CREATE TABLE dbo.foo ( id TINYINT IDENTITY(1,1), bar VARCHAR(5) NOT NULL ); GO DECLARE @i INT; SET @i = 0; WHILE @i <= 260 BEGIN INSERT dbo.foo(bar) SELECT NULL; SET @i = @i + 1; END GO SELECT MAX(id) FROM dbo.foo; GO DBCC CHECKIDENT('dbo.foo'); GO DROP TABLE dbo.foo; GO You will get 255 of these, which do not abort the batch with the loop: Msg 515, Level 16, State 2, Line 7 Cannot insert the value NULL into column 'bar', table 'tempdb.dbo.foo'; column does not allow nulls. INSERT fails. The statement has been terminated. And then one of these, which does abort the batch with the loop: Msg 8115, Level 16, State 1, Line 7 Arithmetic overflow error converting IDENTITY to data type tinyint. Arithmetic overflow occurred. And the result of the SELECT / DBCC will be as follows. ---- NULL (1 row(s) affected) Checking identity information: current identity value '255', current column value '255'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. So, clearly, even without entering a single row of data into the table, you can reach the 'end' of your possible IDENTITY values. Well.......after several days, I finially found the cause of this error.
One of the engineers had created a bad NUnit to test that things were working. I ended up writing a trigger on the table that captured the identity and datestamp and moved them into another table. Added a few lines to the top of the SP that I wrote that logged the XML input paramater and a datestamp out to another table. Then, I ran a Profiler which looked at anything that touched the table in question. Once the Identity was blown, I was able to look at the tables and trace get the HostName of the machine that caused the error, and then go to the programmer and we figured out what he had done. Problem solved! Thanks for your help. Steve Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > > If I query the Max value of the identity > > column, I get 56, but when I run the dbcc checkident I get back the > > following: > > > > Checking identity information: current identity value '2147483647', > > current column value '2147483647'. > > Too many people assume that the MAX value and the current seed are the same. > Sometimes they are, but only on a perfect system. > > USE tempdb; > GO > > CREATE TABLE dbo.foo > ( > id INT IDENTITY(1,1), > bar VARCHAR(5) NOT NULL > ); > GO > > -- succeeds: > INSERT dbo.foo(bar) SELECT 'fun'; > GO > > -- fails: > INSERT dbo.foo(bar) SELECT NULL; > GO > INSERT dbo.foo(bar) SELECT NULL; > GO > INSERT dbo.foo(bar) SELECT NULL; > GO > INSERT dbo.foo(bar) SELECT NULL; > GO > > -- this is 1 > SELECT MAX(id) FROM dbo.foo; > GO > > -- here you assume 1, but it's 5, representing > -- 1 successful insert and 4 failed inserts > DBCC CHECKIDENT('dbo.foo'); > GO > > DROP TABLE dbo.foo; > GO > > > > > What it sounds like happened is you have a ton of failed inserts that you > are not noticing, either because they are in something like VB with ON ERROR > RESUME NEXT or nobody is reporting errors. Here is a quick way to > demonstrate your exact scenario using TINYINT instead of INT, which will > reach the maximum at 255 failed inserts instead of 2 billion. > > > > USE tempdb; > GO > > CREATE TABLE dbo.foo > ( > id TINYINT IDENTITY(1,1), > bar VARCHAR(5) NOT NULL > ); > GO > > DECLARE @i INT; > SET @i = 0; > WHILE @i <= 260 > BEGIN > INSERT dbo.foo(bar) SELECT NULL; > SET @i = @i + 1; > END > GO > > SELECT MAX(id) FROM dbo.foo; > GO > > DBCC CHECKIDENT('dbo.foo'); > GO > > DROP TABLE dbo.foo; > GO > > > You will get 255 of these, which do not abort the batch with the loop: > > Msg 515, Level 16, State 2, Line 7 > Cannot insert the value NULL into column 'bar', table 'tempdb.dbo.foo'; > column does not allow nulls. INSERT fails. > The statement has been terminated. > > And then one of these, which does abort the batch with the loop: > > Msg 8115, Level 16, State 1, Line 7 > Arithmetic overflow error converting IDENTITY to data type tinyint. > Arithmetic overflow occurred. > > And the result of the SELECT / DBCC will be as follows. > > ---- > NULL > (1 row(s) affected) > > Checking identity information: current identity value '255', current column > value '255'. > DBCC execution completed. If DBCC printed error messages, contact your > system administrator. > > So, clearly, even without entering a single row of data into the table, you > can reach the 'end' of your possible IDENTITY values. > > >  |
|||||||||||||||||||||||
Other interesting topics