>Hi Guys,
>
>I used to have a database that is about 6GB in size. Most of the datatypes on the tables are char() and I decided to change them into varchar().
>I was able to shrunk a 6GB database to 3GB by changing the Char datatypes to VarChar. I did this by creating an identical database with the fields data type set to VarChar and then Exporting the data from the original database.
>
>The setup was fine for about a week until we found a glaring increase in the size of the database. in a span of 3 days, the database grew up to 5 GB. Is there a logical explanation on how it ended up acquiring to much space?
>
>Please advice
>
>Thanks....

Mike,

Does the 5GB size include the transaction log? If so, then you may have created the new database with the Full Recovery model where the old database was set to Simple Recovery. This will cause the transaction log to not release the space it uses until it is backed up or manually truncated.

If it isn't a transaction log problem then it may be that the table contains many varchar columns and there are a lot of updates to existing rows. With char columns, SQL Server allocates space for the declared length of the column. With varchar columns, SQL Server only allocates enough for the physical size of the data. It doesn't care whether a column is declared varchar(10) or varchar(2000). If the column contains only 'Hello', then 5 bytes are allocated in both examples.

Rows were packed neatly into their 8k data pages when you created the new table, but after an update that increases the physical size of a varchar column, there might not be enough room for a row without shifting the last row in the page beyond the 8k limit. When this happens, SQL Server will perform a page split. It moves about half of the data in the original page to a new page. Both pages are now half empty.

If there are also many inserts occurring after the updates, then this is not too much of a problem because the unused space in the half-empty pages get filled with new rows. But if you also have a clustered index on a non-identity column, then inserts may still leave the pages half-empty for a long time.