The IDENTITY property can be applied to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. These data types have a finite range and once the limit of the range is reached the next identity insert will fail with an "Arithmetic overflow error converting IDENTITY to data type [data type]." error. The overflow script below demonstrates this.

The simplest way to fix the overflow problem is to change the column data type to provide a bigger range. This is not always an easy thing to do so it's better to be aware of identity usage and remaining capacity so change can be planned in advance if necessary. The check script below returns details and capacity info for all identity columns . Note the comment about not handling decimal/numeric identity columns with a precision over 31.

Top Tip Time! Other than tinyint all the potential data types for an identity column are signed so if you set you seed to the data type minimum you will double your capacity compared to starting at zero.

use tempdb
go

if (object_id('dbo.Test') is not null) drop table dbo.Test

create table dbo.Test (
    Id tinyint not null identity(100, 100),
    Something varchar(100) not null
)

insert dbo.Test (Something) values ('One hundred')
insert dbo.Test (Something) values ('Two hundred')
insert dbo.Test (Something) values ('Three hundred') -- will fail

;with cte1 as (
    select 
        quotename(object_schema_name(ic.object_id)) + N'.' + quotename(object_name(ic.object_id)) as table_name,
        ic.name as identity_column_name,
        t.name as type_name,
        cast(ic.seed_value as decimal(38,0)) as seed_value,
        cast(ic.increment_value as decimal(38,0)) as increment_value,
        -- ic.last_value is null if no rows have ever been added
        -- ident_current is the seed if no rows have ever been added
        cast(ic.last_value as decimal(38,0)) as last_value,
        isnull(cast(ic.last_value as decimal(38,0)) + cast(ic.increment_value as decimal(38,0)), cast(ic.seed_value as decimal(38,0))) as next_value,
        cast (case  
            when t.name = 'tinyint' and ic.increment_value > 0 then 255.0 
            when t.name = 'tinyint' and ic.increment_value < 0 then 0.0 
            when t.name = 'smallint' and ic.increment_value > 0 then 32767.0 
            when t.name = 'smallint' and ic.increment_value < 0 then -32768.0
            when t.name = 'int' and ic.increment_value > 0 then 2147483647.0 
            when t.name = 'int' and ic.increment_value < 0 then -2147483648.0
            when t.name = 'bigint' and ic.increment_value > 0 then 9223372036854775807.0
            when t.name = 'bigint' and ic.increment_value < 0 then -9223372036854775808.0
            -- Note: Because of the way SQL Server does decimal maths this script can't handle decimal/numeric identity columns with precision above 31.
            when t.name in ('decimal', 'numeric') and ic.precision <=31 and ic.increment_value > 0 then cast(replicate('9', ic.precision) as decimal(38,0))
            when t.name in ('decimal', 'numeric') and ic.precision <=31 and ic.increment_value < 0 then cast('-' + replicate('9', ic.precision) as decimal(38,0))
        end as decimal(38,0)) as range_end
    from 
        sys.identity_columns ic 
            inner join sys.types t on t.user_type_id = ic.user_type_id
            inner join sys.tables tbl on tbl.object_id = ic.object_id -- just user tables
), cte2 as (
    select
        c.*,
        floor((abs(c.range_end - c.seed_value + c.increment_value)) / abs(c.increment_value)) as total_capacity_rows,
        floor((abs(c.range_end - c.next_value + c.increment_value)) / abs(c.increment_value)) as remaining_capacity_rows
    from 
        cte1 c
)
select
    c.*,
    c.remaining_capacity_rows / c.total_capacity_rows * 100.0 as remaining_capacity_percent
from
    cte2 c
order by
    remaining_capacity_percent desc,
    c.table_name