4

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?

Improve this question

5 Answers 5

Reset to default
9

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!

Improve this answer
2
  • Wow that is a bit scary. I have a few databases with auto-shrink on and I think I'll be remedying that in the near future. I've noticed that I can reduce the file size of my database drastically using dbcc shrinkfile (from almost 2GB down to 835MB). Is it safe to get my database as small as possible using this and then rebuild my indexes afterwards?
    – Shane
    Jun 5, 2009 at 20:47
  • Not really. You could shrink and then defrag using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE, but if you rebuild the indexes, the new index needs to be built before the old one can be dropped, meaning extra space is needed, and so the file will grow again. Also, if you shrink it right down, are you ever going to insert more data? If so, it will grow again...
    – Paul Randal
    Jun 5, 2009 at 21:32
1

See DBCC SHRINKFILE

Improve this answer
1

SqlACID is right and look specifically at TRUNCATEONLY or target_size

Improve this answer
0

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.

Improve this answer
1
  • The main reason I want to shrink the db is because I have a 2GB limit due to MSDE. The db contains a lot of images which I recently had to extract and compress to free up some space. Now the db size plus the size of the unallocated space is more than 2GB so I'm just worried how it will work for my end users.
    – Shane
    Jun 8, 2009 at 17:20
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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