Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

We have a need to fit database in 4G in order to use ms sql express edition. I started from 7G database, and found a lot of not needed records, and deleted them. After Shrink database size is 4.6G, and 748MB is free (according to database properties).

However, when i execute exec sp_spaceused i am having interesting results:

DatabaseName    Database_size   unallocation space
xxxxxx      4726.50 MB  765.42 MB

Reserved        Data          index_size      unused
3899472 KB      1608776 KB    1448400 KB      842296 KB

Any ideas, how can i bite at least some of this unused space? Also I know table, which occupied it.

update: is it worth to try to rebuild table indexes?

ALTER INDEX ALL ON Production.Product REBUILD
share|improve this question
add comment

migrated from stackoverflow.com Jan 22 '12 at 15:53

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

marked as duplicate by Mike Walsh, Max Vernon, Mark Storey-Smith, Paul White, ypercube Nov 24 '13 at 12:35

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers

First set recovery mode to simple, make backup and shrink all files (data and logs) seprately; use 'move pages to start of database' (or how exactly that is named) option for shrinking.

Then you may consider index defragmenting, helped us in similar circumstances.

share|improve this answer
add comment

If you've already shrunk your database, then give DBCC SHRINKDATABASE TRUNCATEONLY a try.

Reference.

share|improve this answer
add comment
up vote 0 down vote accepted

After index recreation, using this command:

ALTER INDEX ALL ON Production.Product REBUILD

and than I run SHRINK command again - end up with 2.5G database.

share|improve this answer
    
Using this command for every table, I finally get 1.4G database, which is good enough. –  Sergey Osypchuk May 5 '10 at 11:03
add comment