Adding a column to a table with 4 million rows

OK,
We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to 1
terabyte of storage.
We have a table with 5 million rows and I added one smallint column to the
end.
It's been running now for an hour, showing very little I/O, and no one can
even connect using Enterprise Manager or so anything else for that matter.
Anyone have an idea on what could be going on here?
Kevin JacksonWhat SQL Server Version do you have ?
"Kevin Jackson" <kjackson@.powerwayinc.com> schrieb im Newsbeitrag
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>|||Yes, there is a lock on that table to add the column. You might want to do
a sp_who2 for starters in that database and look at the ALTER TABLE command.
The CPU and Disk IO columns will give you a general idea of where the
activity is at. What SQL has to do is create a null value for 4 million
rows, which might take a while. Use Performance Monitor to track Page
writes/sec (SQL Server:Buffer Manager in SQL 2000), that may help give a
lowdown on the activity too.
--
***********************************
Andy S.
andy_mcdba@.yahoo.com
***********************************
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>|||Normally, adding a new column should just make metadata changes, and not
take much time at all. Any locks will only be held while the metadata is
being changed, so it wouldn't be something that should cause a major impact.
How are you adding the new column? EM or QA? Are you supplying a default
value or allowing nulls? What version are you using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>|||I believe when you add a column through EM, it copies all the rows to a temp
table with the new column defined, drops the old table and renames it back
again.
Am i right here ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23W0LR6JfDHA.3284@.tk2msftngp13.phx.gbl...
> Normally, adding a new column should just make metadata changes, and not
> take much time at all. Any locks will only be held while the metadata is
> being changed, so it wouldn't be something that should cause a major
impact.
> How are you adding the new column? EM or QA? Are you supplying a default
> value or allowing nulls? What version are you using?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
> news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> > OK,
> >
> > We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected
to
> 1
> > terabyte of storage.
> >
> > We have a table with 5 million rows and I added one smallint column to
the
> > end.
> >
> > It's been running now for an hour, showing very little I/O, and no one
can
> > even connect using Enterprise Manager or so anything else for that
matter.
> >
> > Anyone have an idea on what could be going on here?
> >
> > Kevin Jackson
> >
> >
>|||Found out we are having some sort of intermittent hardware failure talking
to our EMC drive subsystem...
That's why is was taking forever...
Thanks
"Kevin Jackson" <kjackson@.powerwayinc.com> wrote in message
news:u4UUzLJfDHA.1872@.TK2MSFTNGP09.phx.gbl...
> OK,
> We have a quad P4 xeon 2.8ghz Intel server with 4 gig of ram connected to
1
> terabyte of storage.
> We have a table with 5 million rows and I added one smallint column to the
> end.
> It's been running now for an hour, showing very little I/O, and no one can
> even connect using Enterprise Manager or so anything else for that matter.
> Anyone have an idea on what could be going on here?
> Kevin Jackson
>