Cannot shrink SQL Server 2005 Database

Posted on 2007-10-10
Medium Priority
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20048113
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

ID: 20048134
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?

Accepted Solution

assyst earned 2000 total points
ID: 20048501

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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

807 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