Solved

alter table to add default takes too long time

Posted on 2003-11-26
6
1,030 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
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.

810 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