I've been working in Virtual Machines testing different upgrade scenarios, and found that I started running out of space on my VM hard drives. I think many of us have limited harddrive space as we test things in a VM environment, so I needed to find a way to continue testing with the limited space I had to allocate.
A quick check led me to the database log files that had grown so large that some were larger than the databases themselves. In this testing environment I don't have database maintenance plans to truncate the logs for me, so they just kept growing out of control. Here's an easy 3 step process that I followed to fix my storage space problem…
1. Truncate the log files:
In SQL Server Management Studio, I created a new query window and executed the following statement against each database:
Backup LOG {Database Name} WITH Truncate_Only
This effectively trucates the log, but does not return the empty space to the operating system yet — We'll get to that in step 3.
2. Set the database recovery model to Simple
In SQL Server Management Studio, right click on the database you are working with, and select properties. In the Database Properties window, select the Options Page and check the setting for Recovery Model. Mine was set to Full, which I really didn't need for testing purposes. Set the Recovery Model to Simple. This will only allow the log file to grow large enough to capture a single transaction, and then truncate it again. Note that this is likely not the setting you want in production as you may need the full log files for backup/restore purposes.
3. Shrink the database
In SQL Server Management Studio, right click the database you are working with and select, Tasks, Shrink, then Database. Then click OK. This process may take some time, and you may initally see the log file grow a bit before it shrinks it down to almost nothing.
By following these simple steps, I've freed up about half of my hardrive space, and have made my VMs much more usable.
gruz