I have a table in a database in Microsoft SQL Server 2005 server that is 16gb in size and has 58 million rows.
It has a column called 'balance_forward' that is char(10) (Unusual choice for a numerical column but I didn't design the table) I need to increase its size to accommodate balances that are more than ten characters in size (xxxxxxxxx.xx)
I tried changing it to char(12) (risky, I make no excuses) but I think* this caused the database's log file to grow many gigabytes larger (and fill up the log drive) and the operation failed anyway (data type is still char(10))
I later realized that it would make more sense to change to at least varchar(12), that way the column won't forcibly take up more space, but it will have more room to fit larger data.
My question is – will this also cause the log file to grow again (I managed to free up some space by moving other files off the log drive)
And am I correct to assume that using varchar instead of char will prevent the existing data from taking up more space?
(Ideally the data type should be changed to the most appropriate type for financial balances, but I figure that will be a more drastic change on 58 million rows)
*I can't be sure. I thought I saw plenty of space on the log drive before the operation, but I might have seen 'mb' and mistook it for 'gb'. So it may be that the log had already filled the drive. Apparently logs can grow until they've filled their allocated drive/space
Best Answer
Based off this and this post I would say, yes, the change from
char(10)tovarchar(12)will touch every row and hence grow the log. My reasoning being that the column has moved from the fixed-length to the variable-length part of the row structure and the storage engine will have to persist this information straight away.Variable length columns will have a two byte overhead to track the actual length (see above). If the average length of your data is 8 bytes or fewer you are on to a winner with this chage. Otherwise you will be taking up more space with a
varchar.The digits you require could be stored in 9 bytes with a numeric type. This would be too much of a change to your application, I suspect, even if it is the proper way to hold this data.
I'd suggest you add a new nullable column of the right type and length. From the above post this will be a meta data only operation with minimal logging. Put triggers in place to keep the new column in sync with the existing one. Then your application can keep working while the following steps happen. Copy the existing data across in chunks. Test on your development box to find what chunk size gives the right balance of elapsed time and log size for your system. In my experience 10,000 is about right. Once everything's in place, drop the old column and triggers, and rename the new column. An index rebuild will be necessary to reclaim space. Here are some SO posts which deal with this subject.