Fill Identity Gap
When we insert a record into table ( if table contains unique key constrains, primary or check constraints), any failures occur then identity gaps will occur.
To Over come this, Create a procedure for record insert, apply try… Catch.. Error handling method, on their CATCH section, get the max() value of ID column.
For example:
SELECT @id=max(NN_ACTCODE) FROM ACTIVITY_MASTER
After that reset the identity field using DBCC command.
Fro example:
DBCC CHECKIDENT (tablename, RESEED,@id )
Hope this may help
With regards