Posted by decipherinfosys on December 16, 2008
We have blogged before about how to check for and re-seed identity values in a table in SQL Server. You can access that post over here. One of the questions that a friend had asked yesterday was whether it is an online operation or whether one needs to schedule it to run during downtime. Let’s create a table first and then test it out:
CREATE TABLE IDENTCHECK (COL1 INT IDENTITY, COL2 INT);
Now, in Session #1, run this statement:
insert into identcheck (col2) values (10);
In Session #2, run:
dbcc checkident (IDENTCHECK, RESEED, 100);
and you will see that this session is now blocked. You can see that by running sp_lock or using the other blocking SQL queries that we have posted on our blog here. You will see that it is trying to take a Sch-M (Schema Modification) lock and hence is blocked due to the IX (Intent Exclusive) and X (Exclusive) locks taken by the session #1.
Now, in Session #3, run this:
insert into identcheck (col2) values (100);
And now, commit the first session. You will see that session 2 completes immediately and reseeds the value to 100 and you get this message in that session:
Checking identity information: current identity value ‘1’, current column value ‘100’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
And commit the third session as well now and you will see that it has taken the COL1 value of 101 now:
select * from identcheck
1 10 ==> From Session #1
101 100 ==> From Session #3
So, the answer is that it can be done online but since it is a schema modifcation, it will get blocked by other open transactions.