2

I have a SQL Server database and the .MDF file is almost 9GB in size. However, the data and indexes only seem to be taking up a little over 1GB. I've shrunk the database (with and without reorganization) and I've shrunk the database files.

What's next? How do I find what is taking up so much space?

migrated from stackoverflow.com Oct 27 '14 at 21:03

This question came from our site for professional and enthusiast programmers.

  • What does "seem to be" mean? How are you measuring this? How did the data file get to 9GB in the first place? If you have removed a bunch of data but it is going to grow again, shrinking it in the short term is a complete waste of time. – Aaron Bertrand Oct 27 '14 at 16:59
  • I'm using this script to measure table sizes. sqlteam.com/forums/topic.asp?TOPIC_ID=10932 I'm shrinking the database after removing a decade of legacy records. Sure it will grow to that size again, but not for another decade. And in the meantime we can migrate servers without 10GB file transfers. – dubloons Oct 27 '14 at 20:13
  • And in the meantime we can migrate servers without 10GB file transfers --> Have you looked into backup compression ? I assume that you are not transferring the mdf files over the network. Backup restore (with compression) is the best way to move your databases to new server. – Kin Shah Oct 27 '14 at 21:28
  • Thanks for the tip, @Kin. I do, however, still think the question is still valid: how do I find out why the DB takes up so much disc space? – dubloons Oct 28 '14 at 1:45
0

This is a bit long for a comment so I'm putting it here.

First try running this:

exec sp_msforeachtable 'sp_spaceused [?]'

Yes I realize it uses sp_msforeachtable and there are a number of better methods, this is just quick and hopefully "good enough".

At a guess you have a heap (no clustered index) that has a large amount of "unused" space. I wrote about how to duplicate it here. If in the list of tables you find one that has a large amount of space shown in the unused column then try running this:

ALTER TABLE tablename REBUILD

Fair warning it may take up some space to run the operation but it should free up the excess space in the table.

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.