Why is a adding a new field to a small Table in SQL Server being timed-out?

Posted on 2010-11-25
Medium Priority
Last Modified: 2012-08-13
I use SQL Server Management Studio Express to maintain a SQL Server Express 2005 database.

I wanted to add a new "bit" field to the records in a small Table. The Table contains the details of "shops" owned by my client and contains only 8 records, each record having 21 fields.

When I first added the new field to the "development" system on my laptop, the change was saved, as usual, without any problem. However, when I logged on to my client's SQL Server instance and tried to make the same change there, saving failed with the messages "Unable to modify table" and "timeout expired".

When I Googled these messages it seemed to have been experienced by people making changes to Tables that contain millions of records, but my Table only contains 8 records. I have tried changing SQL Server Management Studio Express's "Table Designer Transaction Timeout" setting from its default 30 seconds to 120 seconds, but my attempted change still times out.

Although my Table only contains 8 records, it is linked to other Tables that contain details of "sales" of the shops, but even these "sales" Tables only have thousands of records rather than millions of records.

Making small changes like this to my client's Tables is something I have done several times over the past couple of years, but this is the first time I've ever been timed-out.

Can anyone shed any further light on what the problem might be?

Many thanks. Colin.

Question by:colinasad
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34213798
do you try to do this in the design view?

anyhow, the main issue is likely that the table is locked by other processes, so you cannot change the design while it's locked.

check the output of sp_who2 while your ALTER TABLE is running (with another session) to see who is blocking your session...
LVL 52

Expert Comment

by:Carl Tawn
ID: 34213802
I occasionally experience issue making changes to table on remote servers through the designer. Have you tried scripting the change instead?
LVL 39

Accepted Solution

BrandonGalderisi earned 2000 total points
ID: 34213863
First of all, I hope that you are appending the column to the end, and not trying to stuff it in the middle.  That causes a table rebuild.  Secondly, are you applying a default value and making it non-nullable?  Because it will take a while for that to process.  I suggest using TSQL's alter table command because the query timeout is unlimited by default and breaking it down in to multiple commands.


alter table YOURTABLENAME add YOURCOLUMNNAME bit null  
alter table YOURTABLENAME alter column YOURCOLUMNNAME bit not null  
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


Author Comment

ID: 34215771
Thanks for the prompt responses.

Thus far I have always used the "design" tool to make changes to Tables, rather than scripting the changes.

In this instance I was adding a new field, named "Shop_FlagOwnShop", data type "bit", not allowing nulls, and setting the default value to "0". I was adding this at the bottom of the list of existing fields, and leaving it there (rather than moving it further up the list) when I tried to save the change.

I always make such changes to my client's system early in the morning and late at night when no-one else should be on it. We have our own "Log" and "Locks" Tables that I check to see if there is anyone doing anything. I have not used "sp_who2" before but will try that.

I have tried to make the changes two ways so far : running Management Studio from my own laptop and logging on to my client's SQL Server instance via their server's IP address, and also logging on to my client's server using Remote Desktop then running Management Studio on their server itself. Same problem was encountered both ways.

I will go ahead and see what happens with "sp_who2" and will attempt to make the change via an SQL script.

Many thanks. Colin.

Author Comment

ID: 34215922
I tried the scripts suggested by BrandonGalde and ran them individually.
The first 2 ran successfeully and completed within seconds, but I had to "stop" the execution of the 3rd command after nothing happened after a minute or so.

So I now have the new field in my "TBL_Shops" Table, with the existing records filled with a "0" in the new field. But is seems that "not allowing nulls" seems to be the source of the problem.

When I tried using the Design tool to untick the "Null" box in the field's setting, saving the change again timed-out, with the additional message "Unable to delete relationship 'FK_ShopStock-Shop'". This Foreign Key relationship links records in another table, "TBL_ShopStock", with a host shop record in the "TBL_Shops" Table.
TBL_ShopStock is a much larger Table than TBL_Shops, and contains 31,460 records and doing a "SELECT * FROM_TBL_ShopStock" on my client's data from my laptop took almost 11 minutes to complete.

I was able to use the Design mode to set the "Default" value of the new field to "0". This change was saved immediately, but I had to leave the "Nulls" checkbox ticked.
Again, this seems to point to "not allowing nulls" in my new field being the source of the problem.

Would the size (and speed of accessing) of a "related" Table have an impact on saving a design change to a "host" Table, in particular requesting "No Nulls" in a new field?

Apart from the new field "allowing nulls", I have managed to make the desired change to the Table but it would be good to get to the bottom of the problem.

Many thanks. Colin.
LVL 39

Expert Comment

ID: 34218737
You should run the TSQL to make the column non-nullable.  If it does not complete in several seconds, check blocking by running sp_who2 in a separate window.

Author Comment

ID: 34236883
I had tried using sp_who2 previously but hadn't make much sense of it, and the T-SQL commands allowed me to create the field, but not disallow nulls.

This morning I have had another go at disallowing nulls and using sp_who2.

I am able to connect to my client's SQL Server instance (in another town) in 2 ways :
1. From my laptop I run SQL Server Management Studio Express (SSMSE) and connect to the instance via its server's IP Address and login with a SQL Logon account.
2. From my laptop I use Remote Desktop to connect to my client's server using its IP Address and logon as an XP "Administrator" user and then run SSMSE from the server and logon to the instance using "Windows Authentication".

With both of these running on my laptop, when I run sp_who2 in the first connection, I get a single result, identifying my SQL Login name, with status "RUNNABLE". When I run the same command on the second connection, I get a list of 26 results, most belonging to logon "sa" and most with status "BACKGROUND" or "sleeping".

With connection 1, when I used the Design tool on the Table in question and tried to save the Table after unticking the new field's "NULL" property, I ran sp_who2 again in my second connection. This time the results list increased to 28 rows, but to be honest I'm not sure which the extra ones were.

Surprisingly, this time my change to the Table was saved OK !!! Last week I was being timed out every time I tried to disable nulls, either using the Design tool or T-SQL commands.

What sort of row would I have been looking for with sp_who2 that would have shed light on why a change was being blocked? Is this something that could have been cleared automatically over a week-end? When I was initially trying it last week, I was definitely doing it when no-one else was logged on to the system and certainly no-one else was using the Table in question.

Any further thoughts?
LVL 39

Expert Comment

ID: 34284488
Blocking occurs based on current usage.  It could have been a schema lock on the table or a lock doing a large select, insert, update or delete.  There's no way to say why it was broken unfortunately, only why it is.  And it isn't "is" now.

Author Closing Comment

ID: 34284582
For some reason my original "hanging" problem seemed to resolve it, but I chose the particular posting as the solution because it gave me the SQL commands I needed as an alternative to using the "Design" tool in SSMSEE.
Many thanks for your patience and persistence.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

624 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