We help IT Professionals succeed at work.

Reclaiming Space on SQL Server 2008 R2

I have a database in which I dropped several large tables.  I would like to reclaim the unused space.  The size of the database was 51gig.  Then I backed up the database.  The size of the database backup went from 41gig to 15gig.   Then I restored the database from the new backup of 15gig.  The database size did not change from it's original size.  It was still 51gig.

When I run sp_spaceused, I get the following results:
database_name     database_size      unallocated space
Aspx2_DW              51703.25 MB       37062.80 MB

reserved            data                      index_size         unused
14948552 KB    13621488 KB       1307704 KB      19360 KB

I also ran dbcc updateusage (aspx2_dw) which did nothing.  How can I reclaim the unused space?

Thanks!
Comment
Watch Question

use aspx2_dw
dbcc shrinkfile(1) - databasefile
dbcc shrinkfile(2) - log file

you could also use string names as the file names.

If they do not shrink you may be because of initial size settings or in the case of the log file it needs to be backed up before the space can be marked for clearence.

Author

Commented:
The log file was not big but I did shrink it and it only took a few minutes.  I have been shrinking the data file and it has been running for over an hour.  Should it take that long?

Thanks!
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Don't shrink the database file, it can lock the tables and will take a while.

http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx

Author

Commented:
It's a test system which no one is using but myself so locking the tables is not an issue.  I just need more space to do my work, and, I am trying to figure out a way without asking for my space on the server.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
then you probably need to wait for the ShrinkFile to finish, there is no way to speed up that process.