rik_deere
asked on
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!
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!
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?
Is the database handling transactions while you are performing the shrink operation?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Assyst!
This worked like a champ. I will just leave the DB in Simple Recovery mode as it's a small data warehouse.
This worked like a champ. I will just leave the DB in Simple Recovery mode as it's a small data warehouse.
then, issue some small transactions.
next, see the shrink work fine.