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
>

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaks
If you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:

> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sql
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no need
> additional one.
> THnaks
>
>
>
>
sql

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaksIf you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:
> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sql
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no need
> additional one.
> THnaks
>
>
>
>

adding 2 or more vitural machines for sql server.

hi,
the virtual sql servers are sharing the same windwos RAM memory, utilize
processor(s) and Disk IO?
What's is the diff between creating virtual sql servers and adding more sql
instances in one sql server?
1. each Virtual server need sql and windows licenses. sql instanses no need
additional one.
THnaksIf you create two virtual servers, then you need ram for two operating
systems as well as SQL Server. If you create two named instance on one
virtual server, then ram is needed for one operating system, and two sql
servers.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"mecn" wrote:

> hi,
> the virtual sql servers are sharing the same windwos RAM memory, utilize
> processor(s) and Disk IO?
> What's is the diff between creating virtual sql servers and adding more sq
l
> instances in one sql server?
> 1. each Virtual server need sql and windows licenses. sql instanses no nee
d
> additional one.
> THnaks
>
>
>
>

added memory - disk now full

We have a win 2000 server with SQL Server 2K.

Over the weekend, they added 4GB of RAM to the existing 4GB.

They did not enable AWE, nor /PAE /3GB. THat is planned for after an upgrade to 2003, this was in theory just to get the hardware stuff out of the way.

When I left on friday, there was 3.5GB on our data drive, which is typical although much too low. (we are also adding space to the SAN next weekend)

The problem is, when I came in this morning, there was only 9MB left on our data drive. I did request that they run update useage on the main database, but that would only help matters, wouldn't it?

Could the disk space problem be due to the memory addition?

Physical memory is paged in and out from and to the paging file. If you system is setup so the paging file size is system managed, the paging file size will be ~150-300% of physical ram. Adding 4 GB of RAM would have increased the paging file size by quite a bit, hence the change in free disk space.|||IMO, a system with 8GB of memory should disable paging. That is not a widely-shared point of view, however. I disable paging on my 2GB dev. system.

Add additional Subscribers

Hi all,
Have Transactional Replication on SQL 2000 EE SP4 for some time now as
follows:
Publisher/Distributor is an 8 way Server
8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
Dist Agents run at Distributor as PUSH to existing Subscribers as follows
Subscriber1 - ServerA
Subscriber2 - ServerB
Subscriber3 - ServerC
This works great, and I now want to ADD:
Subscriber4 - ServerD
Subscriber5 - ServerE
What I want to do is add Server D and E as Subscribers (these are all
separate physical servers) without having to re-initialize any data on
Servers A, B, or C which have been running just fine.
I can prevent access to the PUBLISHER database (~20GB) and quiesce the
Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if need
be.
What is the best way overall to approach this ?
When the Replication was first setup, I set the Snapshot Agent to use 8 BCP
Threads and it took about 30 mins to generate the Initial Snapshot, and
about 1.5 hours to apply to each Subscriber A, B, C across the network
(these servers are all well connected with dedicated nics on a private LAN
solely for Server to Server Replication traffic).
If I go through the Publication Properties dialog and ADD my two new
Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
follows:
1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E cause
the Snapshot Agent to actually create a fresh snapshot ? I assume it will
"invalidate", but there is no existing snapshot currently available, and the
SS Agent has only been run manually on occasion since the intitial setup to
publish new articles to the subscribers which has worked fine. Otherwise the
SS Agent remains disabled.
2 - Will the Snapshot attempt to be applied to, or mess up anything for the
existing (already in-synch) Subscriptions on Servers A, B, C or should it
*only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
I hope the above makes sense and ariticulates what I'm trying to do. Thanks
in advance for any advice, or hints/tips you can provide.
Thanks,
Frank
Just as a follow up question, if I have the Publisher DB and Subscriber DB's
all in synch, and not accessible to users can I:
1 - Take a FULL Backup of one of the Subscription DB on any of the existing
Subscriber Servers (A/B or C), then restore it to Servers D/E
2 - Use the Publication Wizard to PUSH a new Subscription to Servers D/E
using the No Synch option ?
Does that do all the necessary behind the scenes processing correctly ? I
assume because I've restored a backup of an existing Subscriber DB to the
NEW Subscriber DB that it will have all the INS, UPD, DEL StoredProcs in
place as well ?
I read you can do a backup/restore of the Publication DB on a Subscriber for
a no-synch initialization, but the Subscription DB's have auto created
ClusteredIndexes for the PriKeys that exist on the Publication so would that
be an issue ?
In addition, there are some different non-clustered indexes on the
Subscribers as they are used for read-only queries, and mostly reporting
purposes that are offloaded from running on the Publisher DB.
Thanks,
Frank
"Frank Conte" <Frank.Conte@.discussions.ms.com> wrote in message
news:%23GPqOqpwHHA.4640@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> Have Transactional Replication on SQL 2000 EE SP4 for some time now as
> follows:
> Publisher/Distributor is an 8 way Server
> 8GB RAM, RAID 1 (ldf), RAID 10 (mdf), RAID 0 (snapshot folder)
> Dist Agents run at Distributor as PUSH to existing Subscribers as follows
> Subscriber1 - ServerA
> Subscriber2 - ServerB
> Subscriber3 - ServerC
> This works great, and I now want to ADD:
> Subscriber4 - ServerD
> Subscriber5 - ServerE
> What I want to do is add Server D and E as Subscribers (these are all
> separate physical servers) without having to re-initialize any data on
> Servers A, B, or C which have been running just fine.
> I can prevent access to the PUBLISHER database (~20GB) and quiesce the
> Publisher and Subscribers 1/2/3 on Servers A, B, C for several hours if
> need be.
> What is the best way overall to approach this ?
> When the Replication was first setup, I set the Snapshot Agent to use 8
> BCP Threads and it took about 30 mins to generate the Initial Snapshot,
> and about 1.5 hours to apply to each Subscriber A, B, C across the network
> (these servers are all well connected with dedicated nics on a private LAN
> solely for Server to Server Replication traffic).
> If I go through the Publication Properties dialog and ADD my two new
> Subscribers 4/5 (Servers D/E) using the Wizards...my questions are as
> follows:
> 1 - Will the addition of the two NEW Subscription 4/5 for Servers D/E
> cause the Snapshot Agent to actually create a fresh snapshot ? I assume it
> will "invalidate", but there is no existing snapshot currently available,
> and the SS Agent has only been run manually on occasion since the intitial
> setup to publish new articles to the subscribers which has worked fine.
> Otherwise the SS Agent remains disabled.
> 2 - Will the Snapshot attempt to be applied to, or mess up anything for
> the existing (already in-synch) Subscriptions on Servers A, B, C or should
> it *only* apply itself to the NEW Subscribers 4/5 on Servers D/E ?
> I hope the above makes sense and ariticulates what I'm trying to do.
> Thanks in advance for any advice, or hints/tips you can provide.
> Thanks,
> Frank
>
|||(1) for an initialisation you'll need to create a new snapshot.
(2) initialization of the 2 new subscriptions won't affect existing
subscriptions.
HTH,
Paul Ibison
|||You can use a backup of the subscriber - it is much less work than taking the
publisher backup. You don't have to be concerned about identity columns,
deleteing data from filtred articles, removing redundant tables and so on.
There are more details which might be relevant here:
http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison
|||Hi Paul,
Thanks for the quick reply, and helpful info.
So, according to section 2.1 "Adding Additional Articles" in the link you
provided; if I do this by way of backup an existing Subscriber, restore it
to a new Subscriber(s) than do a no-sych Subscription through the wizard I
LOSE the ability to run the snapshot agent thereafter where it would
ordinarily:
1 - Generate schema, index and data files for a NEWLY added article on the
publisher. For example, I add a new table on the Publisher, then go to the
Publication Properties and check off the new table to be added in the
Article tab. Normally I would run the Snapshot Agent manually and it
generated the files for just that newly added article to the Publication.
2 - Have the Distribution Agent pickup the new articles and push them to my
new Subscribers, like it would for my existing ones that were originally
initialized via Snapshot (sync_type=automatic).
If that is the case, I think I should take the other approach as per my
other POST ?
That is...add the two new Subscribers, generate a fresh snapshot, and let
it apply it to the two new Subscribers all while the Publication DB, and
existing Subscription DB's are quiesced and offline (as in no changes
allowed) ?
Hassan's workaround you noted doesn't apply if adding articles via the
Publication Properties, correct ? It would always require using
sp_addsubscription going forward, and as you mentioned make for a less
maintainable setup than I would like.
Thanks,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:5C31C7A4-96EE-4DC5-963E-0D8751CA04B3@.microsoft.com...
> You can use a backup of the subscriber - it is much less work than taking
> the
> publisher backup. You don't have to be concerned about identity columns,
> deleteing data from filtred articles, removing redundant tables and so on.
> There are more details which might be relevant here:
> http://www.replicationanswers.com/NoSyncInitializations.asp
> HTH,
> Paul Ibison
>
|||Hi Paul,
I think this is the better approach for me (see my other reply to yours) in
this thread.
So provided I do not delete and re-add my existing Subscribers in the
Publication Properties they will remain completely uneffected by the new
snapshot files that I do want applied to my new Subscribers.
That sounds like the way to go to keep the maintainable setup going forward.
Is there any other known workaround similiar to Hassan's that doesn't
require using scripting after using the backup/restore method instead of the
initialize with snapshot files ?
Many thanks for you insight, and on going assistance you provide in these
NG's and via your web site.
Regards,
Frank
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:971720D2-2205-43B4-96B9-91FC56D37051@.microsoft.com...
> (1) for an initialisation you'll need to create a new snapshot.
> (2) initialization of the 2 new subscriptions won't affect existing
> subscriptions.
> HTH,
> Paul Ibison
|||Hi Frank - this is correct. For addition of new 'automatic' articles, you'd
need to do this in scripts. Therefore adding new subscriptions normally will
be more suitable. Actually I do the nosync method primarily when the
bandwidth is small and the snapshot is huge - in which case I zip it up
before FTPing over - quite different to your case as far as I can determine.
Cheers,
Paul Ibison
|||What I've seen some people do is to add the new articles to a new publication
in order for the process to be automatic (after having already done a nosync
initialization). This is fine for a one-off provided the articles aren't
related.
HTH,
Paul Ibison

Add /3GB to Server 4GB

I have SQL Server 2000 cluster this is Active/Active.
The server has total 4GB of RAM with Windows 2000 Advanced Server.
Should the /3GB be applied to the both Active/Active nodes?
I would like the primary node to get 2.8 GB and the secondary node to get
1.2 GB of RAM.
Please help me with this problem.
Thank You,
The /3GB switch affects the VIRTUAL memory, not necessarily the physical RAM
and is for each process, each of which gets their own respective address
space. So, in short, yes, set it on each node of the cluster.
Next, you need to account for the OS and any additional processes that will
be running. Since you do not have sufficient memory to make good use of AWE
memory, do not enable that option.
There are two ways to control how much memory SQL Server uses: the MAX
SERVER MEMORY configuration option and the -g startup parameter. By
default, -g is set for 0.5 MB x number of worker threads (128 MB by default)
+ 256 MB = 384 MB. The -g parameter controls the amount of MEM TO LEAVE in
addition to the worker thread memory reservation and each SQL Server
instance has a separate one.
Since you will not be using AWE, as long as you do not set the WORKING SET
SIZE configuration option, memory allocation will be dynamic. I would keep
the MAX SERVER MEMORY set to Total Physical + 1/2 of the Page file. If both
instances were to failover to the same node, each SQL Server instance would
acquire and release memory as needed.
You could set the MIN SERVER MEMORY configuration option in an attempt to
specify the smallest each instance memory allocation would be in order to
keep either from totally controlling the entire physical allocation.
Something like 500 MB would be sufficient.
Sincerely,
Anthony Thomas

"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:F6DB31B3-053D-46E5-B2D6-7592C831952D@.microsoft.com...
> I have SQL Server 2000 cluster this is Active/Active.
> The server has total 4GB of RAM with Windows 2000 Advanced Server.
> Should the /3GB be applied to the both Active/Active nodes?
> I would like the primary node to get 2.8 GB and the secondary node to get
> 1.2 GB of RAM.
> Please help me with this problem.
> Thank You,
>
>