Reseed Identity is behaving differently in New Table and in Existing Table

Reseed Identity is behaving differently in New Table and in Existing Table

If you execute the DBCC CHECKIDENT ('Tablename',reseed,0) in an existing database and a newly created database,the command is behaving totally different way.

Example below

CREATE TABLE Test1
(
  Id int identity(1,1) primary key,
  name nvarchar(10) )

DELETE FROM Test1

EXEC DBCC CHECKIDENT ('Test1',reseed,0);

 INSERT INTO Test1 (name) VALUES('Test')

In the above result system started storing identity from "0" instead of "1"

 DELETE FROM Test1

EXEC DBCC CHECKIDENT ('Test1',reseed,0);

INSERT INTO Test1 (name) VALUES('Test')

To correct the issue verify the last value first based on that reseed the data

Declare @lastValue int

SELECT @lastValue = CONVERT(INT,last_value )
FROM sys.identity_columns ic WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON ic.OBJECT_ID = o.OBJECT_ID
WHERE o.name = 'Test1'

IF @lastValue IS NOT NULL
  EXEC DBCC CHECKIDENT ('Test',reseed,0);

Delete/Disable Multiple SQL Server jobs

DECLARE @jobId binary(16),
@PK_Job INT,
@Counter TINYINT

DECLARE @Jobs TABLE
(
PK_Job INT IDENTITY(1,1),
job_id BINARY(16)
)
BEGIN
INSERT INTO @Jobs
(
job_id
)
SELECT job_id FROM msdb.dbo.sysjobs

SET @PK_Job = 0

WHILE 1 = 1
BEGIN
---- Looping through @Jobs Table
SELECT
TOP 1 @jobId = job_id,
@PK_Job = PK_Job
FROM
@Jobs
WHERE
PK_Job > @PK_Job
ORDER BY
PK_Job ASC

SELECT @Counter = @@ROWCOUNT
IF @Counter = 0
BREAK

IF (@jobId IS NOT NULL)
BEGIN
--For Disable un-comment the below code
--EXEC msdb.dbo.sp_update_job @jobId, @enabled = 0

--For Delete un-comment the below code
--EXEC msdb.dbo.sp_delete_job @jobId
PRINT @jobId
END
END

END

Space used to store data in Table and Index

create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)

declare @id nvarchar(128)

declare c cursor for
SELECT name FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'dbo' AND type='U'
open c
fetch c into @id

while @@fetch_status = 0 begin

insert into #t
exec sp_spaceused @id

fetch c into @id
end

close c
deallocate c

select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc

drop table #t