sql server – Free disk SPACE USING SHRINK

Understanding shrinking the log

If you need to recover disk space from the transaction log file, consider shrinking the log file.

Shrinking logs helps after you perform an action that creates a large number of logs. You can only shrink the log if there is free space on the log file.

Shrink the transaction log

Use the following steps to truncate the transaction log file:

  • Right-click the database and select Tasks -> Shrink -> Files.
  • Change the type to Log.
  • Under Shrink action, select Release unused space and click OK.

What if the log size does not decrease?

If the size of the log does not change much after this, it means that your database has the recovery mode set to Full and there is not enough unused space to recover.

At this point you can consider the option to delete all logs by “temporary” set the database Recovery mode to Simple and try another shrink.

But Keep in mind that following these steps might result in data loss and you’ll not be able to recover data at a point in time 

  • Right-click the database and select Properties -> Options.
  • Set the recovery model to Simple and exit the menu.
  • Right-click the database again and select Tasks -> Shrink -> Files.
  • Change the type to Log.
  • Under Shrink action, select Release unused space and click OK.
  • When the process completes, switch the recovery model back to Full or Bulk-Logged and take a full database backup.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s