?
Solved

alter table to add default takes too long time

Posted on 2003-11-26
6
Medium Priority
?
1,035 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Accepted Solution

by:
arbert earned 375 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 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