>Hi Keith,
>
>There are many inserts and updates to the tables and some of the tables has a clustered index on a non-identity column. As I observed, the database just keeps on growing tremendously. Do you have any suggestion on how to fix the problem?
>
>Please advice...
>Thanks...

Mike,

Just to make sure that you don't waste your time trying to fix a page split problem that may or may not be the root cause, have you done the following?

Check on the recovery model of the database. If it is set to full recovery than you MUST perform regular backups or log shipping or the database will continue to grow forever.

Database configured for autoshrink? If not, use DBCC SHRINKDATABASE after the backup or log ship in the maintenance plan.

I strongly suspect that one of the above is the cause of the expanding size. The whole char -> varchar with updates problem is pretty rare because after the first page split, there will be plenty of room for future updates in the half-empty page. But if those items all check out, run DBCC SHOWCONTIG on the tables in the database to find out which ones are highly fragmented.

The best fix at the table level is to create a clustered index on an identity column. The changed clustered index will not prevent page splits when an expanding varchar column pushes data out of the page, but it will make better use of that empty space on future inserts.

Dropping and recreating the clustered index will also compact the database. After it finishes, run DBCC SHRINKDATABASE to release the empty pages back to the OS.