x

[Closed] Adding Itentity Column - how long shoud I wait?

How long should I let this query run..

ALTER TABLE CPR.DBO.tblLineTable
ADD  ID  [int] IDENTITY(1,1) NOT NULL

It has over 800,000 rows.. and I see the following

task state = suspended command= alter table wait type = pageiolatch_sh..

Thank in advance! I'm wondering if I should just let let run, or if something is wrong.

more ▼

asked Oct 14, 2012 at 04:25 AM in Default

aimyee gravatar image

aimyee
110 3 4 4

did you let it run or kill it? how long did it take?
Oct 15, 2012 at 02:27 PM KenJ

@aimyee this is a continuation/repetition of your earlier question http://ask.sqlservercentral.com/questions/94323/adding-column-taking-over-hour.html

Please don't duplicate questions. If you are still having issues after the answers on the original question, please post there and engage with those who have already started to help you.
Oct 15, 2012 at 03:09 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

The question has been closed Oct 15, 2012 at 07:54 PM by ThomasRushton for the following reason:

Duplicate Question

1 answer: sort voted first

The pageiolatch_sh is the server waiting for I believe a latch on a page in the buffer pool. Updating 800K rows with a new column very well could take some time depending on your disk IO subsystem and how your table is structured.

If time is a concern, you could think about creating a new table with the new column and then import the data into the new structure, drop the old structure and then rename the new table to the old name. I would highly recommend that you test this first to see if it would be a faster alternative.

Or you could just let it wait it out.

Hope this helps!

more ▼

answered Oct 14, 2012 at 05:07 AM

JohnM gravatar image

JohnM
6.9k 1 3 7

(comments are locked)
10|1200 characters needed characters left

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x346
x34

asked: Oct 14, 2012 at 04:25 AM

Seen: 824 times

Last Updated: Oct 15, 2012 at 03:09 PM