CODEWITHSUNDEEP
sqlsql-servert-sql
user222427
user222427

Reputation:

SQL converting columns times out

I need a query that will alter a single column from nvarchar(max) to 32. The real problem is this table has 800,000 rows. And my alter table myTable alter column mycolumn statement times out. Any suggestions or tips?

Upvotes: 0

Views: 221

Answers (5)

Dave Markle
Dave Markle

Reputation: 97791

When you execute the statement, open another copy of SSMS, and run the statement

sp_who2

That will show you, among other things, a column called "BlkBy". That's the SPID of a process which may be blocking your query from completing. You may have an open transaction somewhere else in the system. If you know what that process is, and you know it won't blow up your universe, kill it.

Upvotes: 0

gbn
gbn

Reputation: 432491

If you run a SQL Script in SSMS it has no timeout set. You can only get a timeout using c# etc, and it's the default 30 second CommandTimeout.

I would suggest changing the timeout to 3600 for example, or running it in SSMS.

The other thing to think of: this change will be logged so it can rollback. Make sure you resize the log file upfront to a respectable size so it doesn't have to grow by 10% each time (when the changes you are making use us current log space).

Or combine this with codymanix's answer

Upvotes: 2

AakashM
AakashM

Reputation: 63378

Two things I can think of to try:

  • first do an UPDATE truncating the data to 32 characters; this might help the ALTER run more quickly, since it won't have to do any truncation itself. The UPDATE could be batched if necessary

Or

  • Create a new nvarchar(32) column with a temporary name
  • Populate it from the nvarchar(max) column
  • DROP the nvarchar(max) column
  • Rename the (32) column to the original name of the (max) column

Upvotes: 1

nsmyself
nsmyself

Reputation: 3565

See this.

You can also specify the timeout counter or just disable it via GUI.

Upvotes: 0

codymanix
codymanix

Reputation: 29508

Maybe adding a new column, then selecting the data in the new column, and then remove the old column and rename the new column with the original name will help.

Another simpler approach would be to create a new table with the specifications as needed and then do select .. into.. After this is completed the old table can be dropped.

Upvotes: 3

Related Questions