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)
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

  • 1
    Why are you running a self-update like that to touch all the rows? – Aaron Bertrand Sep 19 '12 at 12:18
  • Also some other questions - did you upgrade (in-place, detach/attach, or backup/restore), or did you build a new version on the new server? (I'm fishing for potential collation changes.) Also did you set compat level on the new database, and did you update stats on this table before you ran the update? – Aaron Bertrand Sep 19 '12 at 12:35
  • no I tried to update the collation on the db with a test system as the server and database are different. but this caused issues so I mirrored the config on the new server for now as my window was very small – DamagedGoods Sep 19 '12 at 13:33

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.

  • Hi Aaron. Its a live site, so I setup log shipping to my new server after a full restore. I did the update to itself as was reading an article / blog by someone previously who said this was a strange necessary step to recover the space saved by the nvarchar(max). I did this as one of my first upgrade tests before updating all stats as obviously wanted to test website functionality. My db is running in 2005 compatability for 1 week and then I intend to upgrade to 2008, thanks – DamagedGoods Sep 19 '12 at 13:22
  • And did you actually observe that space was wasted? I would worry about this when you're doing normal table maintenance anyway, not introduce this hacky kludge as part of your change. – Aaron Bertrand Sep 19 '12 at 13:25
  • I noticed in a test system that the space had not been reclaimed, it did reclaim the space when the log had caught up after running the statement. Well I received the timeout so I still need to run the statement again... I take it your suggesting this isn't the preferred method :) can you let me know what would be the most lightweight / preferential, many thanks. Will updating all stats or changing the compatibility version resolve this anyway ? would you update the new column in a where claus? my main reason to do this was to stop this unecessary LOB being replicated to 3 subscribers – DamagedGoods Sep 19 '12 at 13:27
  • How is updating it to the same value going to prevent it from being replicated to 3 subscribers? – Aaron Bertrand Sep 19 '12 at 13:35
  • sorry, the datatype will still replicate but as a nvarchar(max) instead of the next(blob) be in row if it can fit it in (which is nearly always can). – DamagedGoods Sep 19 '12 at 13:59

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.