sysobjects id field value limit reached (INT)

Question

Post reply

sysobjects id field value limit reached (INT)

Donn Polix

Ten Centuries

Points: 1393
More actions
November 11, 2015 at 12:53 pm

#399310

We have an application that creates physical tables in the database. The tables serves like a temporary tables and are supposed to be automatically dropped after use.
Over the years, nobody realizes that there is a bug in the application and the tables that were created are not getting cleaned up/dropped. It has accumulated "hundred million" tables and I noticed that the maximum value for the sysobject ID field has already been reached. It appears that tables are still getting created because sql server "reuses" an ID number (probably the ones that were used previously by table objects that's already been dropped).
Is this behavior documented by Microsoft? I tried searching the internet related to this but cannot find any, and I just need to be able to tell the business owners that this behavior (reusing the sysobjects ID once it's maxed out) is documented somewhere from Microsoft.
Appreciate any feedback.
_____________
Donn Policarpio

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

It accumulated 100 million tables and NOBODY noticed? :w00t:

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

I'm not sure there's something documenting either that the id is reusable nor it's completely unique. It might not be an identity either if that's what you were expecting.

You shouldn't care or rely on object_id for anything permanent. If an object gets drop and recreated, it might have a different id.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323504 More actions · Answer 3

could you be misinterpreting object_id as an identity column in sys.objects?

sysobjects get an id based on an increment of some weird number which i didn't remember but could find on the interwebz; they don't start at one and progressively get the next identity.

http://raresql.com/2013/01/29/sql-server-all-about-object_id/

If you create a new object in the database, the first ID will always be 2073058421 in SQL SERVER 2005 and 245575913 in SQL SERVER 2012.
...
the increment for user objects is 16000057 + Last user defined object_ID and will give you a new ID.

if you do select count(*) from sys.objects, i doubt you have 2 billion objects.

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!

Donn Polix Ten Centuries Points: 1393 More actions · Answer 4

Luis Cazares (11/11/2015)
I'm not sure there's something documenting either that the id is reusable nor it's completely unique. It might not be an identity either if that's what you were expecting.
You shouldn't care or rely on object_id for anything permanent. If an object gets drop and recreated, it might have a different id.

Not that much familiar with the vendor application, but I believe there is nothing there that explicitly reference via object id.

_____________
Donn Policarpio

Donn Polix Ten Centuries Points: 1393 More actions · Answer 5

Lowell (11/11/2015)
could you be misinterpreting object_id as an identity column in sys.objects?
sysobjects get an id based on an increment of some weird number which i didn't remember but could find on the interwebz; they don't start at one and progressively get the next identity.
http://raresql.com/2013/01/29/sql-server-all-about-object_id/
If you create a new object in the database, the first ID will always be 2073058421 in SQL SERVER 2005 and 245575913 in SQL SERVER 2012.
...
the increment for user objects is 16000057 + Last user defined object_ID and will give you a new ID.
if you do select count(*) from sys.objects, i doubt you have 2 billion objects.

This is in sql 2000 by the way. I came across the same article - thanks for sharing though. It's also based on his tests, perhaps it's just one of the undocumented.

The Id returned by object_id() is actually the ID field in sysobjects. Yes, it isn't an identity field and I don't know also how the sequence gets based off of.

About 200 million! The last thing I have not tested is to completely exhaust all the numbers like actually generating about 2 billion physical tables and witness the database die infront of me (curious to find out what the error it might spit out) perhaps it's the same arithmetic overflow.

_____________
Donn Policarpio

Donn Polix Ten Centuries Points: 1393 More actions · Answer 6

In response to Alvin's post, it has been brought to attention for a long time ago but because of application vendor support issues it was planned to be migrated next year to a new application. I don't know much about what has come about the talks on the functional/business side, but it's been delayed too long.

_____________
Donn Policarpio