Recently we wanted to shutdown a server for RAID rebuild and a copy of the MS SQL database was restored on my laptop machine. While checking the vitals of the database (3rd party configured), I noticed that the database was using FULL Recovery model and the Transaction logs were consuming 97.6% of the total reserved space. As I didn’t want to waste expensive SSD storage from my laptop, decided to change the recovery model to “Simple”, which doesn’t really release the free space.

MS SQL provides a functionality called shrinking, using that you can shrink both database and transaction log files. I will not recommend you to use shrinking in a production environment, however you can always try “stuffs” on a TEST environment.

Let us see how the shrinking is initiated and how does it change the disk space usage. Before shrinking, make sure that you have changed the database Recovery model to “Simple” from “Full”

Right click on the target database and select “Tasks->Shrink->Files”. Cross verify that you selected “Files”

From the Database Files and filegroups section, for File Type Select Log from the dropdown. Verify rest of the defaults and click “OK”. This will initiate shrinking the Transaction Logs and based on the size of the file(s), this may take a while to finish. You can re-run the reports to verify that disk space usage.

After the shrinking finishes, you will see that all the unused space reserved for the Transactional Logs were released by running the Disk Usage standard report.

Although I asked you not to use shrinking in a production environment, feel free to do it from your local system. Learn what breaks and what makes systems, so that you can take a better decisions while handling production instances.