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
>

add sql logins from multiple domains

Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.If the domain in which your SQL Server exists and the other domains are
'trusted', then you could simply add those other domain's logins using
sp_grantlogin.
You could also create a windows group into which you add all the logins you
wanted, and grant access to this windows group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"German" <german.koninin@.crm-worldwideDOTnet> wrote in message
news:eGYi2lIaDHA.2464@.TK2MSFTNGP09.phx.gbl...
Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.

add linked server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks so much in advance,
JamieMake very sure that the folders involved (db location, etc) have full
read/write/execute access for the login account.
Randall Arnold
"Jamie" <gsadd@.yahoo.com> wrote in message
news:O5ALGyfIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select
> * from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop
> the linked server and readd the linked server again. I got the same
> result.
> Who can help a bit?
> Thanks so much in advance,
> Jamie
>

add linked server problem

Hi, there,
I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
just a bald SQL Server.
I want to set up linked server from SQLA , so I can run query like "select *
from SQLB.dbname.dbo.tablename". The command I use is
sp_addlinkedserver @.server = 'SQLB'
After I ran this code, I span the "Linked servers" node of Enterprise
Manager, I saw an image of a linked server named "SQLB". Then I run the
query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
SQLA, I got the following errors
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
I'm the system administrator of the two servers, and the account I ran the
query is sys admin account. I don't know why the command failed. I drop the
linked server and readd the linked server again. I got the same result.
Who can help a bit?
Thanks so much in advance,
Jamie
Make very sure that the folders involved (db location, etc) have full
read/write/execute access for the login account.
Randall Arnold
"Jamie" <gsadd@.yahoo.com> wrote in message
news:O5ALGyfIGHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi, there,
> I have two SQL Server 2000, which are SQLA -- with SAN connected; SQLB --
> just a bald SQL Server.
> I want to set up linked server from SQLA , so I can run query like "select
> * from SQLB.dbname.dbo.tablename". The command I use is
> sp_addlinkedserver @.server = 'SQLB'
> After I ran this code, I span the "Linked servers" node of Enterprise
> Manager, I saw an image of a linked server named "SQLB". Then I run the
> query "select * from SQLB.dbname.dbo.tablename" from Query Analyzer of
> SQLA, I got the following errors
> Server: Msg 17, Level 16, State 1, Line 1
> SQL Server does not exist or access denied.
> I'm the system administrator of the two servers, and the account I ran the
> query is sys admin account. I don't know why the command failed. I drop
> the linked server and readd the linked server again. I got the same
> result.
> Who can help a bit?
> Thanks so much in advance,
> Jamie
>

Actual risk of opening port 1433

Greetings.
I have written a nice little application suite used by 8 or so workstations,
some of which are connected through a VPN. The IT people claim that port
1433 is blocked by default by Nortel's Conntivity VPN, and they will not
make an attempt to change it for fear it will muck up the works elsewhere.
As the SQL server (actually, an instance of MSDE) lives on a dedicated
little WinXP Pro box which does nothing else, I recommended they open port
1433 on their router and point it to that box, allowing the offsites to
circumvent the VPN altogether. The IT director looked at me point blank and
stated that would mean anyone could come in and "hack" both their Win2k
Server, and their IBM Midrange running OS/400.
My question - while I understand the director's concern is completely
irrational... what ACTUAL issues can opening port 1433 to an isolated box
really raise? Even assuming that the intruder coud bypass the credentials (I
am using SQL Authentication, gods help me), what could they possibly do to
anything other than that one MSDE instance?
Many thanks in advance.
Hal Meyer, Proprietor
the patchwerks
(423) 462-2606
http://www.thepatchwerks.com1. SQL Authentication is very insecure.
2. The 'box' is not isolated. It is connected to the network inside the
firewall.
3. Every hacker in the world knows that port 1433 is a standard SQL port and
therefore a target.
4. MSDE runs with LocalSystem permissions. That may provide a platform to
hack the inside servers.
5. Any hacker worth his/her 'salt' will know every weakness of MSDE -and
there a quite a few.
6. The IT people are right!
7. The IT director was very kind in his response to you.
So, suck it up and move on. There is unlikely to be any legitimate business
case for such a 'foolhardy' move.
Your outside users access the internal network using a secure VPN. That
'should' provide them access to the MSDE instance while connected through
the VPN. I would check with the VPN vendor about any problems accessing SQL
Server (MSDE) through the VPN 'tunnel'.
In the rare circumstances where there is a business case to open firewall
port 1433, it usually mandates Rules restricting external IP addresses, more
advanced security (SSL, etc.), as well as constant vigilence and traffic
logging - as well as a rigorous process to attempt to gain approval.
Think about leaving the door key to your home under the 'Welcome' mat. Would
that be a wise action? Wouldn't most potential thieves look under the mat as
their first effort to gain entry.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Hal Meyer" <hmeyer@.comcast.net> wrote in message
news:OK%23WxcvqGHA.1796@.TK2MSFTNGP03.phx.gbl...
> Greetings.
> I have written a nice little application suite used by 8 or so
> workstations, some of which are connected through a VPN. The IT people
> claim that port 1433 is blocked by default by Nortel's Conntivity VPN, and
> they will not make an attempt to change it for fear it will muck up the
> works elsewhere. As the SQL server (actually, an instance of MSDE) lives
> on a dedicated little WinXP Pro box which does nothing else, I recommended
> they open port 1433 on their router and point it to that box, allowing the
> offsites to circumvent the VPN altogether. The IT director looked at me
> point blank and stated that would mean anyone could come in and "hack"
> both their Win2k Server, and their IBM Midrange running OS/400.
> My question - while I understand the director's concern is completely
> irrational... what ACTUAL issues can opening port 1433 to an isolated box
> really raise? Even assuming that the intruder coud bypass the credentials
> (I am using SQL Authentication, gods help me), what could they possibly do
> to anything other than that one MSDE instance?
> Many thanks in advance.
> --
> Hal Meyer, Proprietor
> the patchwerks
> (423) 462-2606
> http://www.thepatchwerks.com
>

Activity on a database?

Try seting up a trace, but just put in the connections,
then look at the databases connected to.
J

>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>
Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...[vbcol=seagreen]
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> databases and I want to
> kind of activity on
> used.
|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx.gbl...
>
|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
>

Activity on a database?

Try seting up a trace, but just put in the connections,
then look at the databases connected to.
J

>--Original Message--
>Hi everyone, I have an SQL2000 with quiet a lot
databases and I want to
>delete some of them. How do I know if there has been any
kind of activity on
>a certain database, so I dont delete one that is being
used.
>Thanks a lot,
>Ivan Mckenzie
>
>.
>Thanks, I suppose this is done with SQL profiler.
"Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
news:2d1d201c469d7$11c0f540$a501280a@.phx
.gbl...[vbcol=seagreen]
> Try seting up a trace, but just put in the connections,
> then look at the databases connected to.
> J
>
> databases and I want to
> kind of activity on
> used.|||Hi,
Run the Profiler for couple of week days . Identify the databases not being
used. Make those databases
offline for minimum 15 days using the below command.
alter database <dbname> set offline
If you are not getting any calls regarding that database, you can backup
those databases , copy to a Tape (or other safe location) and delete the
database.
Thanks
Hari
MCDBA
"Ivan Mckenzie" <imck@.inerza.com> wrote in message
news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Thanks, I suppose this is done with SQL profiler.
>
> "Julie" <anonymous@.discussions.microsoft.com> escribi en el mensaje
> news:2d1d201c469d7$11c0f540$a501280a@.phx
.gbl...
>|||Thanks a lot.
"Hari Prasad" <hari_prasad_k@.hotmail.com> escribi en el mensaje
news:%23V5N%23SiaEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Run the Profiler for couple of week days . Identify the databases not
being
> used. Make those databases
> offline for minimum 15 days using the below command.
> alter database <dbname> set offline
> If you are not getting any calls regarding that database, you can backup
> those databases , copy to a Tape (or other safe location) and delete the
> database.
> --
> Thanks
> Hari
> MCDBA
> "Ivan Mckenzie" <imck@.inerza.com> wrote in message
> news:u6LMgydaEHA.1652@.TK2MSFTNGP09.phx.gbl...
>