Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSMS Timeout Expired Error When Making Changes To a Table


By:   |   Read Comments (6)   |   Related Tips: More > SQL Server Management Studio

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn't think it would have a timeout issue.  In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.

Solution

To simulate the timeout error, let's create a table with 500,000 rows. We will use this for the examples.

-- Create the table for demo
IF EXISTS (SELECT * FROM sys.objects where name = 'TestingTimeOut')
DROP TABLE TestingTimeOut
GO
create table TestingTimeOut (EmpName varchar(75), Designation varchar(50), Department varchar(50))
GO

-- Populate the table with 500000 rows -- Multiple executions by providing number with GO is not ANSI standard -- Should not be used in production environment INSERT INTO TestingTimeOut VALUES ('Atif Shehzad', 'DBA', 'Human Resource') GO 500000

Transaction timeout for SSMS Designer operates only when SQL Server object modification is performed through the SSMS Designer. It is not a server level parameter and may be viewed or modified in the 'Designers' link in the Options menu of SSMS. Its value can be different for each SSMS client and is not stored as a server level configuration.

To check or change this setting, within SSMS go to Tools > Options and click on the Designers link in the left panel as shown below.

Time-out for SSMS designers

The default value is 30 seconds, but for the timeout simulation let's change it to 1 second and save the changes.

In SSMS, find the table that we just created, right click and select Desgin.  Add an integer type column EmpID as a primary key with an identity property and try to save the modifications. Since we changed the timeout to 1 second, this should cause a timeout error as shown below. If the error is not generated you may need to increase the number of rows in the demo table and try again. (Note if you get this error "Saving changes is not permitted" then check out this tip.)

ssms designer timeout designer expired for operation

This is the T-SQL that SSMS generates and is trying to execute.  We can see here that it is just doing an ALTER TABLE with the ADD option.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestingTimeOut ADD
 EmpID int NOT NULL IDENTITY (1, 1)
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

If we try to add the EmpID as the first column, as shown below we can see that SSMS generates a pre-save warning that this will take some time. .

This is the T-SQL that SSMS generates and we can see that this is a much more intensive script that drops and recreates the table.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestingTimeOut
 (
 EmpID int NOT NULL IDENTITY (1, 1),
 EmpName varchar(75) NULL,
 Designation varchar(50) NULL,
 Department varchar(50) NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_TestingTimeOut OFF
GO
IF EXISTS(SELECT * FROM dbo.TestingTimeOut)
  EXEC('INSERT INTO dbo.Tmp_TestingTimeOut (EmpName, Designation, Department)
  SELECT EmpName, Designation, Department FROM dbo.TestingTimeOut WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestingTimeOut
GO
EXECUTE sp_rename N'dbo.Tmp_TestingTimeOut', N'TestingTimeOut', 'OBJECT' 
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

Summary

So if you face these timeout issues when using SSMS to make changes you now know the exact parameter to manipulate. The solution to this problem is to increase the timeout limit for Designer. The specified limit would be implemented for any sever connected via that SSMS connection.

Next Steps
  • Make sure you understand the code that SSMS is going to execute before saving via SSMS.  You can see the differences above for the two options.
  • Remember that this value can be changed to a higher number if changes take longer than the default of 30 seconds.
  • Click here to read about best practices for ALTER TABLE operations
  • Do not forget to drop the demo table as it has about half million rows


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, May 22, 2012 - 12:41:00 AM - Atif Back To Top

@Russell. Of course increase in rows would result in increased time for such task execution. But we should note that default 30 seconds would not be consumed in case medium size table. However if size reaches to that scenario then there are other options to keep in mind.

  • Although not always feasible but enhancing the memory or improving the disk configurations would help to complete the task optimaly
  • Along with that we may consider to append the new columns instead of inserting in between the existing columns

If even then the problem is raised then increase the pointed time parameter along with keeping in view the safe load limit on server.

Thanks


Monday, May 21, 2012 - 6:07:31 AM - Russell Ikugbonmire Back To Top

Mine is a question. I have just added nine new columns to some tables in my package ussing SSMS. Ihave tested it with about 10 set of data but no such errors for now. Am i likely to experience the timeout error as the data grows? if so can mere increasing the the defaul timeot value from the tool option solve the problem? Iam new to the use of SSMS and SQL Server. Tanks for your valuable tips


Friday, May 18, 2012 - 10:55:48 PM - Atif Back To Top

Thanks for appreciation and comments.


Friday, May 18, 2012 - 1:57:05 AM - Shamas saeed Back To Top

Decent article to read on. 


Thursday, May 17, 2012 - 11:39:58 AM - John Fox Back To Top
Personally, the best practice for me is to use SSMS to generate scripts, and then run the scripts manually. I have a lot more control over the process, can use the script to migrate to QC or production, and I can save the scripts for an audit trail. Granted, I could use SSMS to manage changes from test to other environments, but I prefer to spend a few extra minutes and do it myself. Yes .. I've been doing this for a long time :-) Our environment has around 50 servers, and most of the databases are unique. I can understand how someone with hundreds or thousands of servers with replicated schemas would be better off to use SSMS to manage environments. That doesn't mean everyone needs to. Using scripts is especially good for new DBAs so they can understand the mechanics behind what is being done instead of 'just right click, select these options, and press OK'.

Thursday, May 17, 2012 - 10:30:08 AM - Devi Prasad Back To Top

Nice, Keep it up Atif


Learn more about SQL Server tools