By: Atif Shehzad | Comments (7) | Related: > SQL Server Management Studio
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.
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.)
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
About the author
Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
