Scenario: We host a lot of small databases on an Microsoft SQL 2005 server. Occasionally the transaction (.ldf) files will become extremely large so we will need to shrink them. As we only take nightly backups we are happy to make the recovery model “simple”. If you require a more mission critical backup solution then do not use the simple recovery model.

In this example I will change the model from Full to Simple then shrink the the transaction logs.

As you can see from the following transaction log has grown to 5GB.

 

Open up SQL Server Management Studio -> select instance -> expand Databases -> right click database -> select Properties -> Choose Options from the Select a Page menu on the left -> set Recovery model to Simple -> Then press OK

When that is complete we can now shrink the log files.

Right click the database again -> select Task – > Shrink -> Files. When the Shrink File menu appears select “Log” in the file_type field then press OK.

 

As you will see from the transaction log has now shrunk to ~100KB

 

Job done.

As mentioned previously, shrinking will not work if the recovery model is set to full. If you want a more robust backup/recovery solution then investigate the options. This method is good for our needs though.