Systems Engineering and RDBMS


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:


Now, in Session #1, run this statement:

use deciphertest
begin tran
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:

use deciphertest
begin tran
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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: