How to reduce size of SQL server table?

[former_member211576]

Hi experts,

I have a table with 99.9% of unused size(9GB). How do I reduce the size?

I have tried those commands below but they do not work.

ALTER INDEX [VBDATA~0] ON qa2.VBDATA REBUILD

DBCC CLEANTABLE (QA2,"qa2.VBDATA", 0)

WITH NO_INFOMSGS;

GO

ALTER INDEX ALL ON qa2.VBDATA REBUILD

[former_member211576]

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/68af32bb-eefa-494a-b62d-1ebd1...

[former_member188883]

Hi Dennis,

You need to run the command

update <table name> set MyValue= <New value >

<New Value> value to which you want your table size to be.

Regards,

Deepak Kori

[former_member211576]

Hi deepakkori,

What does that mean? I don't understand. It does not work.

update qa2.VBDATA set MyValue = 30000

Msg 207, Level 16, State 1, Line 2

Invalid column name 'MyValue'.

[former_member188883]

Hi Dennis,

Post your rebuild commands , you need to run the following command

DBCC UPDATEUSAGE ('<DB NAME>','<TABLENAME>')
GO

This will update the correct used space for required tablename.

Regards,

Deepak Kori

[former_member211576]

Hi deepakkori,

Thanks for your help. I have found the solution.

"Also other option which you could try is :

ALTER INDEX Index_name on Table_Name REORGANIZE WITH (LOB_COMPACTION=ON)"

you can check full discussion here:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/68af32bb-eefa-494a-b62d-1ebd1...