Solved

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

Posted on 2010-11-25
9
410 Views
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.

0
Comment
Question by:colinasad
9 Comments
 
LVL 142

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...
0
 
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?
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 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.


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
 

Author Comment

by:colinasad
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:colinasad
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
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.
0
 

Author Comment

by:colinasad
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
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.
0
 

Author Closing Comment

by:colinasad
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore 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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now