[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Reclaiming Space on SQL Server 2008 R2

Posted on 2012-08-13
5
Medium Priority
?
1,075 Views
Last Modified: 2012-08-13
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
Comment
Question by:Bodhi108
  • 2
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
jetskij16 earned 1400 total points
ID: 38290025
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
 

Author Comment

by:Bodhi108
ID: 38290046
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 38290058
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
 

Author Comment

by:Bodhi108
ID: 38290097
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 600 total points
ID: 38290144
then you probably need to wait for the ShrinkFile to finish, there is no way to speed up that process.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question