Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website:

alter table MyTable
add MyNewColumn varchar(max) not null
default ('')

The table contains binary data in a different column and is quite large.

Will cancelling by using the red "Cancel Executing Query" button cause additional problems? I'm just trying to figure out if I should attempt to cancel the query at this point, and what will happen since it's been running for so long.

share|improve this question
If MS SQL Server, try executing sp__block and check if there is any block. If there is a block, execute the below query and get the blocked processes, select * from master..sys_processes identify the blocked processs and kill it using the pid. – Vivek Apr 25 '12 at 16:56
1  
I would say that it depends how big it is. Twenty five minutes may or may not be too long based on the size of the data you're moving around, the server's other responsibilities etc. Best to let to ride unless you can confirm that it is hung... – Nate Apr 25 '12 at 16:56
3  
You should have done this out of hours if it is a live system. Perhaps putting a page into place to say essential maintence is necessary and therefore the system is unavailable. – Ed Heal Apr 25 '12 at 16:59
8  
...why are you testing this out in production??? – Thomas Stringer Apr 25 '12 at 17:08
3  
Why can't I +10 @Shark's comment? – Aaron Bertrand Apr 25 '12 at 17:10
show 2 more comments

migrated from stackoverflow.com Apr 25 '12 at 17:04

2 Answers

up vote 5 down vote accepted

The amount of time it takes to add a new column depends on the amount of data in your table. If you run sp_who2 active to see what the process id is you can kill the job. It will go into rollback, but will put the table back to the way that it was.

You should never try altering a table of a production system without knowing the how long the process will take.

share|improve this answer
Thanks, I ran sp_lock and sp_who2 and was able to find the transaction was being blocked. For the record, I did test this in a dev environment and it executed immediately. – GoatBreeder Apr 25 '12 at 17:31
How many records are in the tables? (dev/prod) – datagod Apr 25 '12 at 19:04
1  
An additional followup, to clarify, the query executed immediately once I killed the process that was locking the table. The table is huge, size wise, probably ~100GB, and has over 100k rows (so not large in that regard). Assuming the process wasn't locking the table the query I posted would have taken < 1sec to run, as was the case when I tested in our development environment. This was all just a fluke. – GoatBreeder Apr 25 '12 at 20:09
@GoatBreeder you may want to edit your question to reflect this. – user4742 Apr 25 '12 at 20:42
up vote 7 down vote

Several problems with your approach:

  • adding a not null MAX column with default value. Make the column nullable and don't specify a default. Adding a default '' instead of letting it be nullable is incorrect semantically and a waste of significant space per-row. Make you code handle NULLs correctly.
  • adding a not null with default column to a table without any testing/preparation. This is a size-of-data operation as every single row in the table has to be updated. Has the potential to generate a huge amount of log because is as a single transaction. With a MAX type not even online column add in SQL Server 2012 could help, but again there is little point on having a MAX column not null with default ''.
  • even if the non nullable MAX with default is truly required, preparing for such an operation would require a mandatory evaluation of the log space needed (between 1.5 and 2.5 times the size of data) and pre-growth of the log file. Most likely, this is what most of the time is spent right now on, growing and zeroing the log.
  • doing it live.
  • asking for real-time advice on forums...

If you attempt to cancel the query you may or may not succeed, and the cancellation can be fast or can itself take another 25 minutes or more, depending on where is the current progress at (could be blocked on locks or could be progressing). In the later case resorting to desperation and attempting to restart the server will cause the DB recovery to last even longer.

share|improve this answer
and varchar(max) columns should only be used when you expect to have over 8000 characters of data due to indexing issues. – HLGEM Apr 25 '12 at 19:52
@HLGEM All of these fields will be populated, and all will have well over 8k characters. But that doesn't even matter because varchar(max) is dynamically sized and specifying a limit will almost always result in wasted space unless each field occupies the maximum set limit. The problem here was that a process was blocking my query, not a db design issue. Also, IMO, null values are antiquated and should not be used unless absolutely necessary because they make programmers account for unnecessary scenarios when working with data. – GoatBreeder Apr 25 '12 at 20:06
4  
@GoatBreeder you have an unconventional view of nulls. – John Buchanan Apr 25 '12 at 21:27
1  
@GoatBreeder: Interesting opinion to offer on a database specific site. I'd say "not understood" rather then "antiquated"... and the alternatives are sentinel values = excess data stored – gbn Apr 26 '12 at 6:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.