The database is almost of size 55GB. This database should have never grown to this size as some of the maintenance jobs were not running.
I have deleted almost 30GB of data. Now the database size is almost 45GB and Space Available is almost 20GB .
Which shows that Actual size of the database is now almost 25GB and there is some Unused space that needs to be returned to Operating System.
I have executed DBCC commands for both Database and individual files (8 of them). And I only managed to release a couple of GBs.
The Commands I used are as follows
DBCC SHRINKDATABASE (N'MyDB'); GO DBCC SHRINKFILE('Filename1', 0 , TRUNCATEONLY) GO DBCC SHRINKFILE('Filename2', 0 , TRUNCATEONLY) GO and so on....
But this seems to have no affect on database size and the available space.
I read an article which suggested rebuilding indexes and then shrinking database will solve this issue.
But once I have shrunk the database I would have to rebuild indexes anyway as shrinking database would have made a mess of indexes.
My requirement is to only leave 10% space for db to grow and release the unused space. Then I plan to rebuild Indexes and update statistics after that.
What would be the best approach to this problem. Any suggestions or pointers in the right direction are much appreciated.
Thank you in advance