Tags: allocated, char, customers, database, erasing, limit, microsoft, msde2000, mysql, old, oracle, reclaiming, server, shrinking, space, sql, varchar

char vs varchar - reclaiming free space

On Database » Microsoft SQL Server

2,817 words with 2 Comments; publish: Mon, 02 Jun 2008 00:26:00 GMT; (250140.63, « »)

One of our customers is using MSDE2000 and has reached 2 gb limit. After

erasing some old data and shrinking database he still had 1938 mb

allocated. After that, I have noticed that some of the larger tables

(around 1,5 - 2 million rows spread across a few tables) had column

defined as char(256) and char(1280), and a lot of fields were just space

filled or filled with around 40-100 chars only. I have changed those

columns to varchar(256) and varchar(1280) and rtrimmed those columns using:

alter table sometable alter column somefield varchar(256) not null

update sometable set somefield = rtrim(somefield)

and after another database shrink it seems that it hasn't reclaimed any

space - what's even worse - the database seems to have grown to 2100 mb.

I expected to gain at least 200 mb, but it didn't happen. Is there a way

to reclaim that space?

Tnx in advance

Dragan Matic

All Comments

Leave a comment...

  • 2 Comments
    • Hi

      Check if ANSI_PADDING is ON or OFF.

      From BOL:

      When set to ON, trailing blanks in character values inserted into varchar

      columns and trailing zeros in binary values inserted into varbinary columns

      are not trimmed. Values are not padded to the length of the column. When set

      to OFF, the trailing blanks (for varchar) and zeros (for varbinary) are

      trimmed. This setting affects only the definition of new columns.

      You LOG file may have also grown and it may require shrinking see

      http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp

      John

      "DRagan Matic" wrote:

      > One of our customers is using MSDE2000 and has reached 2 gb limit. After

      > erasing some old data and shrinking database he still had 1938 mb

      > allocated. After that, I have noticed that some of the larger tables

      > (around 1,5 - 2 million rows spread across a few tables) had column

      > defined as char(256) and char(1280), and a lot of fields were just space

      > filled or filled with around 40-100 chars only. I have changed those

      > columns to varchar(256) and varchar(1280) and rtrimmed those columns using:

      > alter table sometable alter column somefield varchar(256) not null

      > update sometable set somefield = rtrim(somefield)

      > and after another database shrink it seems that it hasn't reclaimed any

      > space - what's even worse - the database seems to have grown to 2100 mb.

      > I expected to gain at least 200 mb, but it didn't happen. Is there a way

      > to reclaim that space?

      > Tnx in advance

      > Dragan Matic

      >

      #1; Mon, 02 Jun 2008 00:27:00 GMT
    • Also be sure to run DBCC UPDATEUSAGE(0)

      Roy

      #2; Mon, 02 Jun 2008 00:28:00 GMT