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.
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.
I occasionally experience issue making changes to table on remote servers through the designer. Have you tried scripting the change instead?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.
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.
ASKER
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?
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?
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.
ASKER
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.
Many thanks for your patience and persistence.
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...