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

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.

colinasadAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
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.


ex.

alter table YOURTABLENAME add YOURCOLUMNNAME bit null  
go
update YOURTABLENAME set YOURCOLUMNNAME = 0
go
alter table YOURTABLENAME alter column YOURCOLUMNNAME bit not null  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
 
Carl TawnSystems and Integration DeveloperCommented:
I occasionally experience issue making changes to table on remote servers through the designer. Have you tried scripting the change instead?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
colinasadAuthor Commented:
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.
0
 
colinasadAuthor Commented:
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.
0
 
BrandonGalderisiCommented:
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.
0
 
colinasadAuthor Commented:
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?
0
 
BrandonGalderisiCommented:
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.
0
 
colinasadAuthor Commented:
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.
0
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.

All Courses

From novice to tech pro — start learning today.