0

SYSOBJECTS table has an identity column of int type. But values in ID column are getting close to int max value. I guess it'll become impossible to create any objects when it reaches its maximum.

Is there a way to alter that table to convert ID column to BIGINT for example? Or are there any tools that can solve this problem?

SergB
  • 91
  • 7
  • 2
    I can't help but feel this is asking the wrong question... Why does this mysterious database have (or in total has had) nearly 2.4 million objects in it? – underscore_d Jan 09 '20 at 12:20
  • 1
    You can't alter the definition of system tables, and even if you could, the rest of the engine and system objects wouldn't know you did, so that simply wouldn't work. Keep in mind that the limit of objects created is 2^31-1 -- 2.1 *billion* objects -- are you sure you're close to that? If you have a database that has managed to hit that, you are due for a redesign with a completely new database and a data transformation, which would also solve the problem of resetting the identity. – Jeroen Mostert Jan 09 '20 at 12:21
  • It's a very old database that went through many deploys. Each deploy some views/procedures are dropped and created again so ids are incremented. – SergB Jan 09 '20 at 12:22
  • We don't have so many objects there. It's all because not optimal deployment process. – SergB Jan 09 '20 at 12:23
  • 2
    object ids arent assigned sequentially so probably no need to panic They have a large increment (of 16000057) and wrap around on overflow – Martin Smith Jan 09 '20 at 12:25
  • Martin Smith, thanks, but I can't find any info on how those ids are assigned for system tables and how it works on overflow. Maybe you have some docs references? – SergB Jan 09 '20 at 12:35

2 Answers2

3

you have nothing to worry about.

Object ids are not assigned sequentially. They have a gap of 16000057 between each one and just wrap around on overflow (approx every few hundred object creations) so it is normal you will see some big numbers in there.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Thanks, but I can't find any info on how those ids are assigned for system tables and how it works on overflow. Maybe you have some docs references? – SergB Jan 09 '20 at 12:36
  • 1
    There wont be any documentation about this apart from perhaps something, somewhere, telling you not to assign any meanings on object ids. Certainly the 16000057 and wrap around is an internal implementation detail that is easy to test just by creating some objects but wont be documented – Martin Smith Jan 09 '20 at 12:38
  • 2
    Just created two similar tables and got ids like 19543899 and 35543956. So you are right and there's nothing to worry about :) – SergB Jan 09 '20 at 12:45
  • 2
    Incidentally, 16000057 is the smallest prime number greater than 16000000. This is unlikely to be a coincidence, and likely chosen to play nice with hash functions, such as might be used internally for in-memory hash tables backing the system tables. – Jeroen Mostert Jan 09 '20 at 12:52
0

This won't fit (well) in a comment.

From Maximum Capacity Specifications for SQL Server:

SQL Server Database | Maximum sizes/numbers | Additional Information 
Engine object       | SQL Server (64-bit)   |
--------------------|-----------------------|----------------------------------------------------
Tables per database | Limited by number of  | Database objects include objects such as tables,
                    | objects in a database | views, stored procedures, user-defined functions,
                    |                       | triggers, rules, defaults, and constraints.
                    |                       | The sum of the number of all objects in a database
                    |                       | cannot exceed 2,147,483,647.

If you are close to 2,147,483,647 objects, you need to fix your design. End of story.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • We don't have so many objects. The problem is that they were recreated many times with incremented ids. – SergB Jan 09 '20 at 12:28
  • Yes, that was later revealed in the comments, @SergB ; by which point I had already answered/answering the question. – Thom A Jan 09 '20 at 12:29