How to shrink transaction logs in SQL Server

Owned by Arvin John Salandanan
Aug 22, 2022
2 min read

Some of our customer has a lot of records in the database that could consume most of the disk size. Shrinking transaction logs could free up some space.

You can check the disk usage in SQL Server then right click to Adonis database > chose Reports > Standard Reports > Disk Usage.
Sample report from the database:

Follow the steps below to do the shrinking process.
STEP 1 (IMPORTANT): Full database backup - make sure you have a full backup before doing the shrink.

STEP 2: Truncate logs - We need to make sure we have an empty log right before shrinking. To do this:

  • Right click on the target database\Properties

  • Go to Options

  • Set Recovery mode from Full to Simple. Click OK

     

Then you can now perform the shrink. Please make sure you are shrinking the file type Log to avoid huge issue.

 

 

You can also choose to reorganize pages from Shrink Action and set the size the same as your .mdf file(data file).

Once you are done with the shrinking process, you can set maximum log size to avoid huge log file in the future. (Below is just and example).

Then you can now return the DB recovery mode from Simple to Full.