2 min read

SQL Transaction logs allow you to restore a database to a specific point in time, and is a great option for a production database, but these logs must be backed up frequently enough to prevent them from filing up. However, you need to back up the transaction log itself, not just the database, if you want to use the Full or Bulk Logged recovery models. This article describes the right way to set this up: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-2017 

If you’ve already taken a full backup of your database, and find yourself in a crunch out of space, the following is a quick way of clearing the transaction log to recover space:

Log into Microsoft SQL Server Management Studio

Right click on your database.

Select Properties.

Switch to the Options tab.

Change the recovery model to Simple.


Click OK.

Right click on the database.

Select Tasks > Shrink > Files.

Select Log for the file type


Click OK.

Your transaction log should now be empty and the space reclaimed. Now you should change your recovery model back to Full or Bulk Logged.

Right click on the database.

Select Properties.

Click on Options.

Change the recovery model to Full.

Click OK.

Was this post helpful?