Shrink Bloated Development Databases – Last night I noticed that the space on my C drive was running slightly low. When this happens, I usually go and look in the temp folder to see what unnecessary space hogs are hiding there. After clearing this folder I realised that the amount of data removed from this folder didn’t make much of a difference. I then went hunting. Using a very nifty tool called WinDirStat, I found the problem files. It turns out that some of my development databases were absolutely huge. The LDF files were several gigs in size. Here is what I did to save a little bit of space.
Shrink Bloated Development Databases
Many times I use a copy of a customer’s database in order to test applications with ‘real world’ data. I usually create a database from a backup I’m sent.
Looking for something else? Try these links instead:
- Prevent Accidental Table Data Deletion In SQL Server Management Studio
- Customizing SQL Server Status Bar – Know Your Connection
- Script Table Data In SQL Server Management Studio
- Query Stack Overflow With T-SQL – Give Your Queries A Workout
This backup would then often contain very large LDF files. In a production environment you might need to use a full recovery mode for your SQL Database. Since I am only using this for testing, I don’t need to recover anything and can modify the recovery model with confidence. As you can see below, the LDF file was 36 Gig in size.
Shrinking the LDF File
To shrink this LDF file, go to the object explorer in SQL Server Management Studio and right click your development database. From the context menu, select properties. Under the Options group, you will see the recovery model. If your LDF file is big, you can expect this setting to be set to ‘Full.
As I mentioned above, I’m not concerned with recovering any data so I can go ahead and set the recovery model for this development database to ‘Simple’.
After clicking on OK, right click the database again and select Tasks -> Shrink -> Database.
On the following screen, check the option to reorganize files and leave the maximum free space setting to 0%. Click on OK. What follows depends on the size of your LDF file. The larger your LDF file is, the longer the shrinking process will take.
When the process completes, head on over to your LFD file again and see how much space you have saved.
This quick and nifty trick can save you loads of space on a development machine. If you work with many different databases on your development machine, chances are there are a few large LDF files lurking there.
