Alter varbinary(8000) column to varbinary(max)

Question

Post reply

Alter varbinary(8000) column to varbinary(max)

huskerwendy

Hall of Fame

Points: 3633
More actions
November 2, 2007 at 2:50 pm

#119253

I have a table with a varbinary(8000) column that was about 13 GB and altered the column to a varbinary(max) column. After the alter statement completed, the size of the table was about 21 GB. I thought that varbinary(max) only used the space for the actual length of the data plus 2 bytes. So I'm a little confused as to why the table grew so large. Can someone explain that or point me in a good direction to read up on it?
Thanks!

Wendy Schuman

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

What are you using to determine the size of the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

It's possible that the alter added extents and the size hasn't been properly adjusted. You might try updating the usage with DBCC

huskerwendy Hall of Fame Points: 3633 More actions · Answer 3

I determined the size of the table by checking the data space used in the table properties dialog box. Also, as the alter column was being executed I was watching the free space on the disk getting lower and lower as the alter statement was executing.

I tried using the DBCC UPDATEUSAGE, but it didn't change anything. Any other ideas?

Thanks for you help.

Wendy Schuman

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 4

fragmentation may also be the case.

try rebuilding the table's clustering index.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me