Cannot shrink SQL Server 2005 Database
Posted on 2007-10-10
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!