SQL Server – How to ALTER one or more Table Columns avoiding a Command Timeout in T-SQL How to change the type and/or the format of one or more SQL Server Table Columns with an ALTER query avoiding command timeout and without restarting the Database

SQL Server - Recuperare il Product Key da una installazione esistente

If you’ve stumbled upon this post it most likely means that you need to change the type and/or format of one or more table column of a SQL Server using a standard ALTER query avoiding a Command Timeout and – most importantly without having to restart the whole Database.

The Problem

Let’s take for example the following Attachments SQL table:

And let’s say we want to change the Description column from nvarchar(250) to nvarchar(MAX). Needless to say, the table is live on a production server and contains hundreds of thousands records, therefore any attempt to change that data type using the following ALTER query:

Would likely hit a Connection Timeout.

What can we do?

The Fix

The best workaround I found to overcome such scenario is to take advantage of  SP_CONFIGURE, a built-in Stored Procedure which can be used to display or change global configuration settings for the SQL server. More specifically, the setting that we want to change is “remote query timeout”, which defaults to 600 (seconds).

The plan is to bring it to 0, execute our query (without risking a timeout anymore) and then setting back to 600 immediately after. Here’s how we can do that:

However, before doing that, it might be the case of executing SP_CONFIGURE in display mode in order to be 100% sure that the default value to restore is 600:

If that’s not the case, just change the relevant value in the previous query accordingly.

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.