Sql-server – timeout on updating nvarchar(max) after converting datatype

datatypesquery-timeoutsql serversql-server-2008

I recently Upgraded to 2008 Enterprise and took the opportunity to change the biggest table in my db with the maintenance window.

ran the following to change my ntext field

ALTER TABLE acomEstabLocale_tbl ALTER COLUMN [description] nvarchar(MAX)
GO
update acomEstabLocale_tbl set [description] = [description]

when I ran the second statement i got the following

length of LOB data (77404) to be replicated exceeds the exceeds configured maximum

ok, so after a poke around I found the sp_configure and ran :-

EXEC sp_configure ‘max text repl size’, 100000

but now I receiving the following 

nvarchar max Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I'm looking to temporarily extend this query timeout period, can the just be done using the remote query timeout setting on the server properties ? or is there a T-SQL statement for just this query

Best Answer

I'm not sure exactly what the purpose is of your entire-table-update, but perhaps the timeout is because your query is causing:

  • stats to be created
  • triggers to be fired
  • massive writes to the log (and potentially you are waiting on log file auto-grow - instant file initialization doesn't help you here)

If this table is replicated, the database is mirrored, or you've added CDC/Change Tracking then I think it is going to be much worse. If the table is participating in replication, you should read this topic.

What is the update supposed to do? It's too late now for this case but in the future an alternative way to do this for other tables might be:

  1. add a new column with the new data type
  2. populate it in batches with values from the old column, and use transactions or checkpoints to minimize impact to the log (and subsequent features that use the log)
  3. once done, rename or drop the old column, then rename the new column to the old name

Of course this still has to be done in a maintenance window. You don't want users contending for writes during 2.

Related Question