2

How do I disable the Table Designer timeout in SQL Server Management Studio?

Background

This KB Article seems to indicate that if you stop overriding the Designer timeout:

To resolve this behavior, use one of the following methods:

  • Click to clear the Override connection string time-out value for table designer updates check box for the table designer and for the database designer in SQL Server Management Studio.

enter image description here

it is supposed to revert to the Execution timeout:

enter image description here

But rather than timing out never it times out after 30 seconds:

enter image description here

I've also tried setting the table designer to the maximum value of 65,535. Unfortunately that just causes the timeout to happen after 18 hours, rather than 30 seconds.

How do I disable the table designer timeout in SQL Server Management Studio?

SQL Server Management Studio: 9.00.1399.00

Pre-emptive snarky comment: what kind of table change are you making that needs more than 18 hours to complete? I'm changing a column in a 130 GB table from text to nvarchar(max).

Improve this question
9
  • Well, why do you do it - FROM THE DESIGNER? Snarty comment - make it in a empty database, generate the change script and execute that in management studio. I regularly reformat 200+gb tables (though that just takes a couple of hours) and I would not DREAM of touching the designer for life data. I use a command line driven schema update mechanism that executes the change scripts deposited in it's folder structure. WAY too risky to do stuff like that manually without testing it first.
    – TomTom
    Jun 6, 2013 at 18:03
  • 2
    @TomTom The change script can lead to data loss; with all those GO statements (e.g. the EXEC('INSERT INTO dbo.Tmp_Transactions fails, and then proceeds to drop the original with DROP TABLE dbo.Transactions. i've only lost data once by manually running the generated scripts; i don't want that to happen again. That's why i prefer to do it from the designer, and not IN MANAGEMENT STUDIO. Loud noises. I DON'T KNOW WHAT WE'RE YELLING ABOUT.
    – Ian Boyd
    Jun 6, 2013 at 19:00
  • 1
    Despite your comments above, I wouldn't do it in the designer. Your faith in it is somewhat unfounded. If you must use the designer then you can click the "script" icon in the toolbar to generate the code then sanity check it and run it in a new query window with no timeout problems.
    – Martin Smith
    Jun 7, 2013 at 16:14
  • 1
    Looking at your comments in more detail you are aware that transactions can span across GO statements? Also your stated goal can be done without rebuilding the table anyway. ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR(MAX) [NOT] NULL
    – Martin Smith
    Jun 7, 2013 at 16:33
  • 2
    @IanBoyd - Ah right I see the issue. In that case you can enable sqlcmd mode on the Query menu and add SET XACT_ABORT ON; and :on error exit to the top of the script;
    – Martin Smith
    Jun 7, 2013 at 19:25

1 Answer 1

Reset to default
1

"...i'm changing a column in a 130 GB table from text to nvarchar(max)..."

Hi If you change a column and you expect that it can take considerable amount of time - I would recommend writing a Query.

Depending on you situation it could be as simple as "ALTER COLUMN" or as complex as "Creating temp table, coping all data, dropping table, creating table with new structure, copying data from temp table".

In any situation, having a script will allow you to run it without any timeout probably in you maintenance window (if you have one).

Regards,

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.