Identity columns are created to auto generate keys for records. These keys are best suitable for primary keys. If any value crosses the max value for identity column data type then following over flow error may occur, as here in case of tiny int
Msg 8115, Level 16, State 1, Line 5
Arithmetic overflow error converting IDENTITY to data type tinyint.
Here is a script to generate list of all identity columns in database along with their data types and maximum value consumed
Use AdventureWorks GO SELECT SCHEMA_NAME( OBJECTPROPERTY( c.OBJECT_ID, 'SCHEMAID' )) AS [SCHEMA NAME], OBJECT_NAME( c.OBJECT_ID ) AS [TABLE NAME], c.NAME AS [COLUMN NAME], t.name as [Column data Type],seed_value, increment_value,last_value as CurrentMaxValue FROM SYS.identity_COLUMNS c inner join sys.types t on c.user_type_id = t.user_type_id WHERE COLUMNPROPERTY(OBJECT_ID, c.NAME, 'IsIdentity') = 1 AND last_value IS NOT NULL order by last_Value Desc GO
Now you may analyze the list of identity columns. And any column that is near to fully consume its maximum value may easily be tracked here.