Solved

alter table to add default takes too long time

Posted on 2003-11-26
6
1,029 Views
Last Modified: 2012-05-04
Is there anything I can do to expedite adding a default to a table?
  We have an on-line database that I want to set a default.  I made an off-line of a table MyTable and changed the default of one field to 'Active'
  Alter table ep_dependents add default 'Active' for dep_status

It took 1 second to change this default on the copy, so I thought I could relatively do the same thing in production.  I tried to run it in production and let it go half an hour before I realized it was still trying to complete and I probably denied some users while this was attempting to process.  

any recommendation?  It is probably best to take the DB off-line in the middle of the night, but I also wonder if there is a way to do this on the fly even if I have to knock users off for a minute...
0
Comment
Question by:Qualtagh
6 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 9829383
Hi Qualtagh,
post the table structure...
what indexes are there ?
does the column participate in any constraints?
is replication active for the table?
are there any triggers?



Cheers!
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9829518
My impresion is that in fact you were waiting for exclusive lock because of other users... To alter the structure of a table needs exclusive lock to that table.... Nobody can work on that table in that time... So in fact you were waiting for locks.... so you never locked anybody in the menatime...

What you need is to make this alter to run at night (or at a time when no other user/process is accesing that table)..

good luck,
    xenon
0
 

Author Comment

by:Qualtagh
ID: 9829563
Hi Lowfatspread,
  There is an identity field (increment and seed are one).  The field in question is not involved in the triggers on this table I'm 95% sure (I eyeballed the 3 triggers for any reference).  The field in question allows NULL at this time and had no previous constraint.  It is a Char(12).  No replication involved.

Hi Xenon,
  you are probably right that I need to wait until no other process access.  what do I do if I want to run during a low volume time (say 11:00am) and still a user is connected and I need to force this before the next morning?  I suppose I take the Database off-line.  
  In this case, would I be best off making a batch file to take the DB off-line and alter the table and put the table back on-line?  Maybe, I could schedule this to be done at 3AM to avoid most conflicts using a local package...

I'm 3 years exp. in Database, but only about 1.5 for on-line database.  Let me know any more Expertise.  Thanks !
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 9829787
Agree with xenon, you need exclusive use.

Either schedule a job (sql server agent--create a new job and post your alter statement as a TSQL job), open query analyzer and do something like this:

WAITFOR TIME '1:00'
go
your alter statement here



The above statement will wait till 1am and then run....

Brett
0
 

Author Comment

by:Qualtagh
ID: 9832851
Thanks for ideas

Since I was running the job at 3:00am and it didn't complete by 3:30, the WAIT statement actually wouldn't have helped me in this case.
  I ended up  killing  a process.  I forgot what it said, but when I went under Enterprise Manager and Management's current processes it showed one of the processes in Red.  It was a select statement from a view accessing the table I was trying to modify so I got fed up with it and killed it for keeping me up and denying access to that table.

Qualtagh
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 9834830
Qualtagh the B grade is not good...you received your answer.... that the problem was that you did not had an exclusive lock on the table...

And yes, is not a good practice to let the database to be 'hit' by users while you are making changes to the database structure......
 So what you have to do is whenever you have to alter database structure, put the database in SINGLE_USER mode, and then connect to it, make the changes, than change back the database to MULTI_USER mode...

Or if you are sure that at a certain time the database is not used at all, make the cahnges at that time....

regards,
    xenon
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now