How to release unused space in a SQL Server database

Question

I have a SQL Server database of size 270GB. Almost 91% of the space is showing up as UNUSED in space report. I know unused space is space allocated to a particular object. App team did a purge operation on the DB but we couldn't really claim space with that.

I have checked index fragmentation on the table but they look good.. What else i need to be checking and what is that i should do to release that space. The DB is not going to use that space anytime in future.

As per my understanding DBCC SHRINKFILE releases only unallocated space. I've read about DBCC CLEANTABLE and DBCC SHRINK DATABASE but not sure if it's good enough to perform.

How to effectively release unused space from objects.

Answer 1

So to my understanding, you first need to shrink the files, then you can shrink the DB:

Using SQL Server Management Studio:

1. Shrink the files: r-click on DB, 'Tasks', 'Shrink', 'Files': Then pick a new file size value slightly larger than the 'Minimum is' value given in the dialog.

2. Shrink the database: r-click on DB, 'Tasks', 'Shrink', 'Database' (tick the 'Reorganize files checkbox)

With this, I was able to reduce the percentage of 'unused' space in the Disk report. There is still quite some left though (20%) .. I think 'reorganizing' some indices might help.