Adding a field to a table locks up db

Question

Adding a field to a table locks up db

pg53

Ten Centuries

Points: 1303
More actions
June 23, 2004 at 10:15 am

#102076

Sometimes I need to add a new field to a table on a live system. Sometimes this seems to lock up the server completely. Presumably the server needs to get a lock on the entire table to add a new column, and can't because other users are using that table; but when this situation happens I can't even use EM to look at 'process info'
Given it's probably not a wise thing to attempt to add a field to a table on a system while it's busy, nevertheless it seems to be a design flaw if a deadlock can prevent someone going in to terminate the guilty process. So - what am I missing?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Consider there are several factors at play.

1) If the field is fixed length or a non-null varchar column then the data has to resize in each row and page splits and data movement may occurr.

2) If you are using EM and adding to anywhere other than the end the a temporary table is created (tmp_tablename) with the new structure, all the old data is imported, the old table is then dropped, and the temp table is renamed.

3) If the table doesn't have a clustered index and data movement occurrs then all non-clustered indexs will have to update their pointers to data locations. When a clustered index is available this doesn't happen because the related data of the clustered index is in the non-clustered index for reference, when no clustered file/page/row pointer is used.

Those are the biggest things I can think of right off.

pg53 Ten Centuries Points: 1303 More actions · Answer 2

Thanks Antares - and you've answered another question too, which is 'does the position of the added field make a difference'

But my original question is really this - Is it right that certain operations, such as adding a new column, can load the server to the extent that EM can't be used to kill that process? Isn't there some way that EM can take priority over the other running processes? Or, to turn it around, if someone creates a 'troublesome' SP that locks the server, what do I do?

TIA

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 3

IF you haven't already, you can boost the priority of the SQLServer process on the server it is running on by:

right click on the server name in EM, and select properties

go to the Processor tab and put a tick in the 'Boost SQL server priority on Windows' box.

Restart the SQL Server service

I'm not sure why your EM is locking up, it sounds like your server is really struggling for memory or disk space (make sure your database isn't fixed size and nearly full).

Regards

Peter Tillotson

Adam Tice SSC Journeyman Points: 87 More actions · Answer 4

Just a suggestion...

Get used to using Query Analyzer for performing most of your DBA tasks. Not only will it increase your knowledge of the database, it will also give you a more robust method of dealing with conditions that render EM unusable.

For instance, in your example where a particular user creates a troublesome SP, this can easily be resolved by executing "kill <spid>". There are a variety of tools available in QA for determining which users and processes are consuming resources, causing locks, etc.

If you are interested in learning more, here's a great article by Bill Wunder to get you started: http://www.sswug.org/see/15415

Hope this helps.

Adam

pg53 Ten Centuries Points: 1303 More actions · Answer 5

Thanks Adam - unfortunately the article is on a restricted part of the site, but your reply does say that there are situations where EM is unusable. Seems like a bit of a design flaw to me, but I shall take your advice and look into QA more.