Solved

alter table to add default takes too long time

Posted on 2003-11-26
6
1,031 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

789 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