Is it possible the column was populated and it is has since been cleared out, or the data type was recently changed to varchar(max)? Have you tried rebuilding the clustered index (which will touch every row, unlike some ALTERs)?
EDIT
Since rebuilding the clustered index didn't help, I'm at a loss, and since the size of the existing data is so small, I recommend just creating a new version of the table and moving the data over. You can do this by explicitly re-creating the table and then copying the data, or you can use SELECT INTO
to minimize the steps.
SELECT ... other cols ...,
varchar_max_col = CONVERT(VARCHAR(MAX), NULL)
INTO dbo.newtable FROM dbo.oldtable;
CREATE CLUSTERED INDEX ... ON dbo.newtable( ...cols... );
CREATE INDEX ... ON dbo.newtable( ...cols... );
...
GO
BEGIN TRANSACTION;
EXEC sp_rename 'dbo.oldtable', 'oldtable_backup', OBJECT;
EXEC sp_rename 'dbo.newtable', 'oldtable', OBJECT;
COMMIT TRANSACTION;
There is the Percona Tool called pt-online-schema-change
If you can tolerate downtime, you can try the following:
Suppose you have the following
- datadir is
/var/lib/mysql
- MyISAM table named
mydb.mytable
- You have large disk volume mounted on /backup
You could do the following
mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
#
# Create Symlinks Pointing to Another Disk
#
cp /var/lib/mysql/mydb/mynewtable.MY[ID] /backup/.
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
chown mysql:mysql /backup/mynewtable.*
ln -s /backup/mynewtable.MYD /var/lib/mysql/mydb/mynewtable.MYD
ln -s /backup/mynewtable.MYI /var/lib/mysql/mydb/mynewtable.MYI
mysql -u... -p... -e"INSERT INTO mydb.mynewtable SELECT * FROM mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mytable RENAME mydb.myoldtable"
#
# Erase Symlinks
#
rm -f /var/lib/mysql/mydb/mynewtable.MY[ID]
#
# Move Newly Made Table Back
#
mv /backups/*.MY[ID] /var/lib/mysql/mydb/.
mysql -u... -p... -e"FLUSH TABLES"
Seems like a lot of work, eh ??? Try the pt-online-schema-change first.
UPDATE 2012-08-22 11:56 EDT
I am not sure what would happen, but please hear me out on this suggestion: Try executing a repair on an empty .MYI
file.
How do you do that? Take my idea from my first suggestion and augment to swap the new and old .MYI
.
mysql -u... -p... -e"CREATE TABLE mydb.mynewtable LIKE mydb.mytable"
mysql -u... -p... -e"ALTER TABLE mydb.mynewtable ADD INDEX (acctnum)"
cp /var/lib/mysql/mydb/mytable.MYI /backup/.
rm -f /var/lib/mysql/mydb/mytable.MYI
cp /var/lib/mysql/mydb/mynewtable.MYI /var/lib/mysql/mydb/mytable.MYI
mysql -u... -p... -e"REPAIR TABLE mydb.mytable"
This should perform an in-place linear index rebuild.
Give it a Try !!!
Best Answer
The whole point of an INDEX REBUILD is to make the processing of data more efficient. This means that when possible it reorganizes the data to reduce the number of pages (according to the fill factor and other limitations) used for that index (whether it is a PK, unique, or non-unique index)and to order the rows of data more efficiently within the used pages and extents.
Therefore, it will reuse existing pages as the first choice. This should result in less storage being used. Of course if the indexes are modified, this could expand the amount of storage needed, depending on the modification.
Using tempdb to rebuild indexes can be a performance boost, but is unlikely to dramatically affect the required storage.
In this case you should definitely rebuild the indexes.
Note that this will not reduce the physical size of the .mdf and .ndf files. It will free up space that can be used as tables in the database expand in size.