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.
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/