Reclaiming Space on SQL Server 2008 R2

Posted on 2012-08-13
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?

Question by:Bodhi108
    LVL 3

    Accepted Solution

    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 Comment

    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?

    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    Don't shrink the database file, it can lock the tables and will take a while.

    Author Comment

    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.
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    then you probably need to wait for the ShrinkFile to finish, there is no way to speed up that process.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now