Drop those columns which are no more in use.

For example, After Upgrade from MOSS 2007 to SharePoint Server 2010, we have noticed that all of the upgraded databases in SharePoint Server 2010 were almost 40% – 60% larger in size than before the upgrade.

We’ve decided to DROP those eligible columns and reclaim unused space out of it

Note: We ran this query against 130 GB size table and it took 10 hours to complete its operation online.

Space allocation details of a table can be found using below query:-

sp_spaceused ‘AllDocVersions’

Run the below query and monitor the resource consumption. I would recommend to run it during non-business hours

SELECT GETDATE() /* start time*/

GO /* Actual clean up operation – Online*/

DBCC CLEANTABLE(‘Content_ABCD’,'AllDocVersions’, 100) WITH NO_INFOMSGS

GO

SELECT GETDATE() /* End Time*/

Parameter details:-

DatabaseName= Content_ABCD

TableName= AllDocVersions

Batch_size =100 /Note:- Smaller the size, better the resource governance*/

Is the number of rows processed per transaction. If not specified, or if 0 is specified, the statement processes the whole table in one transaction. Its always better to keep it to small number so that transaction log will not grow abruptly and locks can be handled effectively. You can do it online.

Gather space allocation details one more time and compare it with the previous values

sp_spaceused ‘AllDocVersions’

Its not a recommended practice to shrink the database. In case, the allocation has more than 50% of free space then check for file shrinking option.

Check for available space of the database by issuing the below query.

SELECT name ,file_id,(size/128.0)/1024 ActualSizeofFileinGB,((size/128.0) – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0)/1024 AS AvailableSpaceInGB FROM sys.database_files;

If AvailableSpaceInGB more than 50% of allocated space, then execute

DBCC SHRINKFILE (4,140000)

File id=4

TargetSize = 140000

This can be stopped at any time(Online Operation).

Note: Perform Index Rebuilding Operation once above mentioned steps are over by checking it’s fragmentation level.