Cannot shrink SQL Server 2005 Database

Posted on 2007-10-10
Last Modified: 2010-03-19
I am simply trying to shrink a SQL Server 2005 database, but it's not letting me.  Here is the story....

This database was being used in conjunction with SQL Server Integration Services (SSIS).  We were loading large tables from another database in a "near-real-time" fashion.  To accomplish this, the SSIS jobs were loading temporary tables in the background.  Once the load was completed, we would drop the current live table and rename the one that had been loading.  This created the illusion that data was always current in the the data warehouse.

Everything was going well, until I discovered that the disk had filled up.  This was my fault for not paying closer attention to the relatively small disk.  In any case, I tried to shrink the database and it kept coming up with out of disk space errors.  So we hooked up a 500MB USB drive to the server and performed a backup.  We then setup another SQL Server 2005 instance on another server (with larger disks) and restored the database there.  The database restored just fine, and was the same size as on the original server.  

So now I just want to shrink the database, make another backup of it (in smaller form factor), and restore it on the original server.  However, the shrink is not working.  When I go to the shrink utility, it says that I have 69% free space on the data and 88% free space on the logs.  When I click to OK button it quickly does it's thing and has no effect.

Thanks for your help in advance!
Question by:rik_deere
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    change the recovery mode of the database from full to simple.
    then, issue some small transactions.
    next, see the shrink work fine.
    LVL 27

    Expert Comment

    Have you tried shrinking it by file instead of shrink database? The advantage is that you can set a number in the Shrink File to (in MB), which may be smaller than the original size?

    Is the database handling transactions while you are performing the shrink operation?
    LVL 7

    Accepted Solution


    Change the Recovery mode to SIMPLE as suggested by AngelIII. Then run the following script to clear the commited transaction log.

    Now you can run the DBCC SHRINKFILE to reduce the size.


    Author Comment

    Thanks Assyst!
    This worked like a champ.  I will just leave the DB in Simple Recovery mode as it's a small data warehouse.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    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

    11 Experts available now in Live!

    Get 1:1 Help Now