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

Cannot shrink SQL Server 2005 Database

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!
1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change the recovery mode of the database from full to simple.
then, issue some small transactions.
next, see the shrink work fine.
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?

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.

rik_deereAuthor Commented:
Thanks Assyst!
This worked like a champ.  I will just leave the DB in Simple Recovery mode as it's a small data warehouse.
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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