Home / server / Questions / 20801
Accepted
Sean Howat
Asked: 2009-06-06 11:58:34 +0800 CST

How can I remove unallocated space from a SQL Server database?

  • 772

I have a database that was recently shrunk and when I run sp_spaceused I see that it has 500MB of unallocated space. I'm trying to keep this database to a certain size (do to MSDE size restrictions for my desktop users) and I'm not sure if the unallocated space affects the overall database size. Is there a way to remove this unallocated space from the database?

sql-server database

5 Answers

  1. Best Answer

    Be very careful running shrink for data files, it causes index fragmentation because of the algorithm it uses (I used to own the code when at MS). This blog post I wrote contains an example script that shows what I mean Auto-shrink – turn it OFF!. Although the blog post title is about auto-shrink, manual data file shrink uses the exact same code in SQL Server and so has the same problems.

    If you're only using the TRUNCATEONLY option as Kyle says, you won't do any data movement and won't cause fragmentation.

    If you have indexes in the database, you have two options:

    1. Run the shrink and remove index fragmentation afterwards. Don't do it using index rebuild though (as it will need to grow the database again for space for the new index). Use my old DBCC INDEXDEFRAG or it's replacement ALTER INDEX ... REORGANIZE. That only needs one data page to operate and so won't grow the database again.
    2. Create another filegroup in the database, move all indexes into it using the CREATE INDEX ... WITH DROP_EXISTING syntax, and then drop the old filegroup.

    Hope this helps!

    • 9
    • 1

  3. SqlACID is right and look specifically at TRUNCATEONLY or target_size

    • 1

  4. Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature. There's nothing wrong with having unused space in the data file. In fact, SQL needs some free space in the file.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded (especially if you don't have instant initialisation on). Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    • 0

  5. one might want to shrink if we see huge unallocated space lets say 150 GB or so. I have seen one case where the sp_spaceused says high unallocated, but DBCC SHRINKFILE doesn't help. So is it necessary or I must say compulsory to use the target side in that scenario

    • 0