My database size was very big so I truncated the audit tables and that means half of the data is gone from the database. Still the database size didn't reduce. Then I backed up the database. The backup file was way less than the previous day backup size. Then I restored that file but still it takes on the same size. The data file didn't get the free space from the data truncation operation.
Can you please tell me how to reclaim that free space? I tried everyway but no luck.
From my experience I have seen that REBUILDing index on huge databases after shrinking, log file will grow huge(which can be minimal with simple recovery model though) and data files also. It takes large number of hrs to complete it.
I agree, the only downside with Paul's alternative is that it uses more disk space, and if you are truly out of disk it's not a viable option. And unless you have Enterprise Edition, your indexes and data may be inaccessible during the rebuild.
Any truncate/delete/drop on Sql Server doesn't release space back to disk. Truncate/Delete/Drop should follow DBCC Shrinkfile to reclaim the space back to disk.
Note: Do not use UI interface, instead use DBCC Shrinkfile query.
Perform these operation 1. Defragment you database 2. If in SQL server any object or file is taking large space switch it into a new filegroup 3. Partitioning of the table & the database if require Or try this http://www.sqlservermanagement.net/
To be more precise; Drop/Truncate/Delete on Sql Server will not release space from Disk. You need to perform DBCC Shrinkfile when ever you do these operations to get space back to disk.
Reclaim file space
Posted - 01/10/2013 : 05:11:29
