Solved

alter table to add default takes too long time

Posted on 2003-11-26
6
1,028 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

707 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

20 Experts available now in Live!

Get 1:1 Help Now