Increasing a column size fails on a specific table

Question

Increasing a column size fails on a specific table

Bill Hair

SSC Enthusiast

Points: 156
More actions
June 9, 2004 at 10:11 am

#102056

This is a fun one.
THE SETUP
We have a SQL Server running 7.0 sp1 (yes, I said sp1). The server stats are dual xeon 2.8 and 6 gb of ram on Windows 2k server sp4. I moved the sqlserverlast year after we started having server issues with our dual piii 550 & 2 gb of ram. Unfortunately we have a few db's with errors (dbcc checkdb showed this), however, most of them are index errors. My boss wants to wait to correct (it has not been a problem and we have successfully removed the errors with no issues on a test server) the errors until some future time.
THE ISSUE
Primary Production database (7 gb in size) with a bunch of tables that are mostly useless and one table that has about 150 columns (95% of all the data). **Yes this database was very poorly written and is quite an embarassment, but I cannot do anything about it.**
We have one column in the huge table that we need to increase from 30 characters to 50 characters to accommodate a guid (the column is configured as nvar). When I go into design, increase the column from 30 to 50 and then click save, Enterprise Manager becomes unusable, the server goes to about 30 - 50% utilization for about 20 minutes (sqlserver.exe process). After the server recovers from heavy processor utilization, enterprise manager again is usable, the column is still at 30 characters.
THE QUESTION:
How can I monitor what is going on? Any ideas on what is causing the column to reject its increase size?
Thanks in advance
BillH

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

You should execute an alter table alter column statement through query analyser rather than go through enterprise manager. When you run an alter table through EM it creates a temporary table (with the updated column length), inserts all the data into that table, drops the old table and then renames the new table to the old table name.

Bearing in mind that it's attempting to shift nearly 7Gb of data around it's going to take forever, potentially fill the disk, be blocked by any select, insert, update process.

Updating the length through QA eliminates this problem.

Shamless self promotion - read my blog http://sirsql.net

Edward McGovern Ten Centuries Points: 1269 More actions · Answer 2

To find out what is going on, go into Table Design, make the change then script the changes. There is a little scroll button at the top of this screen to do that. Don't save the changes, just script.

The issue is that SQL creates a new table with your structure, moves the data and then deletes and renames. Its alot if the table is large.

(You could also do a sql trace to see whats happening).

To resolve just run in query

Alter Table Tablename

ALTER Column FieldName nchar(50)

Dont forget to add Null or Not null at end depending on what you want.

Just look up in BOL Alter Table.

Peace.

Bill Hair SSC Enthusiast Points: 156 More actions · Answer 3

I'll give it a shot on one of our test servers. The primary server has about 120 gb free.

Thanks for the quick response.

BillH

Alamir SSCertifiable Points: 6085 More actions · Answer 4

why you don't install SP4 ?.. may be it solves your problem

Alamir Mohamed
Alamir_mohamed@yahoo.com

Bill Hair SSC Enthusiast Points: 156 More actions · Answer 5

From what I've read, you should not install a sp unless you've repaired any corruption in the database. We have 3 of 15 databases with corruption (from the old server). One of them is the msdb (I think minor), the other two are our primary production databases.

If I can upgrade to sp4 without repairing, that would be great. Let me know.

BillH

Bob Clayton Old Hand Points: 362 More actions · Answer 6

If these strategies don't work, you could export the data to another table, drop the table, re-create it with the column defined the way you want it, then copy the data back using DTS.

Also, your servers have a lot of RAM. Doesn't SQL Server use a maximum of 2 gigs of RAM unless you upgrade to the Enterprise Edition?

Bill Hair SSC Enthusiast Points: 156 More actions · Answer 7

I was successful in using QA to increase the field size from 30 - 50 characters. It took about an hour to complete the process. When we bought the server, it was intended to run (still is) SQL Server 2000. We purchased SQL Server 2000 Enterprise Edition, but installed SQL 7 back to the server until we got to a point where we were able to fix the db. I'd love to fix it right now and move to 2000, however, I don't get to make that call. I am still stuck at SP1 for the same reason. Correct on the standard edition, 2 gb max. SQL Server ignores the rest of the memory. Doing the export to a new table would probably have worked and taken the same amount of time (the table is 5.5 gb in size).

Thanks to all for your help.

BillH