Updating Table Structures

• TkNeo

  The table and database designers in SQL Server Management studio have a default transaction timeout of 30 seconds.  If the transformation that has been requested requires the table to be recreated (for example, if you want to change the order of the rows in the table) and the data in the table is large, it can take much longer than 30 seconds to copy the data to the new table.
  
  You can increase the transaction timeout in SSMS in the Tools | Options | Designers | Table and Database Designers dialog.  Obviously, if you set the timeout to a very large number and there is a network or other problem, you won't find out about it until the timeout expires, which could be several hours if you make the timeout large enough. 
  
  Users should also be aware that if a table edit requires a table recreate, the table will be locked until the transaction completes, which will prevent queries and stored procedures from operating on the table while it is being edited.  Great care should be taken using the table and database designers against production databases where making the data unavailable for extended periods of time (up to the designers transaction timeout) is not acceptible.
  
  

• RMan54

  Hi...
  
  When i am looking at the above Error message, it looks like the SQL Server is quite picky abou the time it allows the Log to grow...
  
  It said ...610 Milliseconds....
  
  Try changing you transaction log so it will allways grow a fixed amount and not a percentage of the existing size! if you set it to 10% groth, then this would mean 100 Megabyte on a 1 Gig log file. If your PC takes to long it might fail
  
  So set the Filegroth to a fixed amount and decrease it! Say to 25 Megabyte or so (Something you know your box can handle in the time)
  
  But this is just a wild guess since i dont have a 2005 Server here
  
  

• NickG

  I reported this issue as a bug for SQL 2005 here:
  http://lab.msdn.microsoft.com/productfeedback/default.aspx
  
  Let's see if someone from MS reviews this issue.  I included reproduction steps for a dummy table with 6 million rows and 30 fields.  It gives the same message.
  
  

• Bob__Bob

  I am have the same issue when attempting to change a column from text data type to ntext type.
  I have followed the instuctions regarding the log file and even though I am increasing it from the default 10% it does not appear to save the changes and reverts back to 10%.
  There were 2 columns in this table I was attemping to change. The first column saved the changes after a reasonable time but when I went to modify the second column which contains larger text data it consistently times out with the same messages the others are experiencing. 
  This data was originally from an Access database, so it is not a huge file.
  

• ChrisThomas

  http://support.microsoft.com/ kbid=915849

  
  

• Elijah

  29.11.2005
  
  Hi Christian:
  
  Thanks for writing - here's the error message I get after initaiting a save of a change to a table called POS_Facts_All:
  
  'POS_Facts_All' table
  - Unable to modify table. 
  Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  
  Though I have SQL Server 2000 and SQL Server 2005 running side by side on the same machine, the only instance of SQL Server that accesses this database is SQL Server 2005.
  
  Any suggestions deeply appreciated, and thanks again for writing.
  
  Best Wishes,
  
  SMM
  

• trulyviral

  Your tables may be in use. What error message do you get
  
  

• UstesG

  Hi Christian:
  
  Thanks again for writing - your comments make complete sense - I will try your suggestion later today and let you know.   Thanks again very muich for taking the time so check this out as carefully as you have - I know other colleagues will find your suggestion very helpful!
  
  Best Wishes,
  
  Michael (smm)
  

• Ashwin Panse - MSFT

  I managed to duplicate your error by modifying a medium-size table (200k rows).< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

  Judging by the name of your table, you are modifying a data warehouse table that can be larger than my simple test table. The modification of you table will generate one huge transaction that will have as effect the growth of transaction log. You can probably find in your SQL Server log this error “Autogrow of file 'DatabaseName_log' in database ' DatabaseName' was cancelled by user or timed out after 610 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.”

  INMHO, the cause of the error is the  “File Growth” value for you log file. If you have the default value of 10 percent you should increase this value.

  To increase it – in Object Explorer right click your database – select properties – select Files page. In the column Autogrowth on the log file row click ‘…’ button. In my case 50 percent value was enough to complete the modification.

   

   

  
  
  

• Jim Wilcox

  We are also receiving this error when trying to add a primary key to an existing table with 4.5 million records.  We are using SQL Server 2005 Standard Edition 64bit on Windows 2003 Server 64bit.
  
  Error Message:
  '2005-11 ALLPCS' table
  - Unable to modify table. 
  Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  
  No error is reported in the event logs and it looks like we have plenty of log space available.
  DBCC SQLPERF (LOGSPACE)
  master                         0.4921875    71.42857    0
  tempdb                        0.7421875    56.44737    0
  model                            0.4921875    84.12698    0
  msdb                            0.4921875    45.23809    0
  ReportServer                0.7421875    45.72368    0
  ReportServerTempDB    0.7421875    43.94737    0
  AdventureWorksDW    1.992188    24.90196    0
  AdventureWorks            1.992188    24.90196    0
  DMS                               0.9921875    46.06299    0
  Bloodhound                    5124.992    27.30231    0
  
  
  Please advise,
  
  Jeff
  
  
  

• ron nash

  I am having the same issue as SMM.  I am using the SQL 2005 Enterprise Edition RTM Build.  I checked the logs and there are no messages about database or log growth.
  
  I have a table with 40 million rows and I am doing some basic changes in table structure (like setting up a column to be able to have nulls).  Since I need to load many tables, the database file size is of 60gb and the transaction log is of 30 gb. 
  
  It runs for about 30 seconds and then gives me the same error:
  
  "'account_base' table
  - Unable to modify table. 
  Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."
  
  In my case, I used the surface configuration manager and enabled all services.  This is a test machine and I can afford to test it out.  I also even disabled the network card and work from the server console to rule out firewall or sql port issues.
  
  This must be a bug of sql2005.  This worked flawlessly in sql2000.
  
  Any feedback would be greatly appreciated.
  
  Edgard
  
• Updating Table Structures