• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1139
  • Last Modified:

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!
0
Bodhi108
Asked:
Bodhi108
  • 2
  • 2
3 Solutions
 
jetskij16Commented:
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.
0
 
Bodhi108Author 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!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Bodhi108Author 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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
then you probably need to wait for the ShrinkFile to finish, there is no way to speed up that process.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now