1

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

Improve this question

1 Answer 1

Reset to default
3

Is there a particular reason you are specifying TRUNCATEONLY? As per the documentation on DBCC SHRINKFILE:

TRUNCATEONLY

Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.

target_size is ignored if specified with TRUNCATEONLY.

If that is not the intended behavior you were looking for (which it doesn't sound like it is), then you could try without that option set.

Also, there are other factors that hinder database file shrinking as well, such as text`ntext\image` BLOB data. So it is worth looking into the possibility of the existence of data with these data types.

Improve this answer
8
  • I have used ShrinkFile commands without this option and it executed for almost 8 hours and then error out, saying Cannot obtain locks on the resources..... On the other hand 16GB of data was deleted from one table which has a text field. I think your second suggestion will lead me to a solution. :)
    – M.Ali
    Commented Sep 25, 2014 at 11:40
  • Ok, well then that would make perfect sense why you would specify TRUNCATEONLY :-)
    – Thomas Stringer
    Commented Sep 25, 2014 at 11:46
  • @M.Ali did you delete all of the data from the 16GB table? Have you tried rebuilding just that table (or dropping and re-creating it)? That won't shrink the file obviously but may make those pages available to be wiped.
    – Aaron Bertrand
    Commented Sep 25, 2014 at 11:59
  • @Aaron should we not consider Ghost records here in picture and unless the background operation Ghost cleanup finishes its task it would not release space.sqlskills.com/blogs/paul/…
    – Shanky
    Commented Sep 25, 2014 at 12:05
  • 1
    Online rebuild or wait for a maintenance window to perform an offline one. And actually an online rebuild (or as online as possible) would be preferable over drop/create anyway. Worst case scenario: create a copy of the table, in a transaction rename the original table and create a synonym for it, then the inserts can go to the copy until you rebuild the original, then point the inserts back (rename the table back, drop the synonym), then move the data that temporarily went to the copy, and drop the copy. This only works if everything is an insert though.
    – Aaron Bertrand
    Commented Sep 25, 2014 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.