Cancel table design change in SQL Server 2000

In SQL Server Enterprise Manager and change one of the columns and save it, it will create a table with the new definition, and copy all the data to that new table, and then delete the old table when it's done.

But if your table is large (let's say on the order of 100GB), it can take a long time to do this. Even worse, if you don't have sufficient disk space, it doesn't notice ahead of time, and it will spend a long time trying to copy the table, run out of space, and then decide to abort the process.

We have other ways to copy the data in smaller chunks, but those require significantly more manual intervention, so it's usually easier to just let Enterprise Manager figure it out, as long as there's enough disk space.

So for a long running "Design Table" save like this, is there any way to cancel once it's started? Or do you just have to wait for it to fail?

From serverfault $pageUsers[$entry.posts[0].author].name

• The SQL generated by using the Enterprise Manager IDE to modify a table is rarely optimal - it will regularly generate a huge temporary table, drop and create commands for some simple things like increasing a field length. Wherever possible, avoid the IDE and write your own script. And always preview the Change Script before you consider running it.

  The SQL generated in the scenario you describe will create a temp table with the new structure, copy the data into it with some conversion code as applicable, drop the original table and then rename the new one. Along the way it will also drop any dependencies like indexes and foreign keys and recreate them against the new table. It does all this within a transaction, so if this is interrupted the changes will not be committed.

  So, you can open a second instance of Enterprise Manager, open Process Info within it and identify the connection that's performing the change, and kill it. After a few seconds delay, the other instance committing the change will stop with an error, and the original table will remain untouched. I really wouldn't make a habit of this, though.

  Ian Boyd : Some things you just cannot do with SQL Server (e.g. insert a column) You're not going to find a secret way of performing a table change that EM doesn't already know about.

  CodeByMoonlight : I agree, but field order in a table is less important. Often adding a field at the end or modifying an existing field will generate this long wait, and it rarely needs to. Rename a field, and it'll use sp_rename. Change the datatype, and it'll drop/recreate as above when a simple ALTER TABLE...ALTER COLUMN will work fine 95% of the time.

  CodeByMoonlight : By less important, I mean that most of the time I see people insert columns in the middle of tables, it's because the column 'fits better' there with the adjacent columns, like putting a Status and StatusDate together.

  CodeByMoonlight : Then th solution is easy. Either remove the rows with NULL values or give them 'proper' IDs. Then do ALTER TABLE MyTable ALTER COLUMN MyField fieldtype NOT NULL, then run ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (MyField). Of course, that's not what your question asked.

  CodeByMoonlight : 'the' solution - doh
  From CodeByMoonlight

• The table change is going to be, essentially, atomic. There is no way to cancel some operations. It's not the fault of Enterprise Manager. For example:

  CREATE INDEX IX_Authors_Gender ON Authors 
  ( Gender )
  

  is going to take as long as it takes; there is no way to cancel such an operation.

  However, if you really want to abort the operation, because you know it's going to take too long, or it's going to fail anyway - you an force SQL Server to abandon the job earlier by:

  • killing your connection to SQL Server. SQL Server will rollback any transactions in progress
  • stopping/killing SQL Server. When the server comes back up, any transactions in progress will be rolled back
  From Ian Boyd