June 11, 2012 at 12:04 pm
I used the alter command to reduce the field size of a column from varchar (2048 ) to varchar(256) thinking that it would increase the Space Available for the database.
The Space Available actually decreased. Can someone explain why that would happen?
Thanks
June 11, 2012 at 12:21 pm
Possibly work space for the alter or other concurrent activity. You'll need to rebuild the clustered index before the table size shrinks, if it will shrink at all, which it probably won't. Varchar only uses the space required for the data, so if you have 25 characters in the column a varchar(30) and a varchar(3000) will take exactly the same amount of storage space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2012 at 12:22 pm
I wouldn't expect it to change the space available at all, so I'm guessing other activity reduced the space available in the table.
Just to be sure, you did use an ALTER TABLE command to change the column and did not do it thru the SSMS GUI, right?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply