Thursday 15 October 2015

How to shrink / truncate a database log file within SQL Server

Firstly ensure that the database recovery model is set too 'Simple':

Right hand click on the database >> Properties >> Options >> Recovery Mode = Simple.

Then right-hand click on the database again and select 'Tasks' >> 'Shrink' >> Files - from here you should ensure that the file type is set to 'Log' and the Shrink action 'Reorganize pages before releasing unused space' is selected and enter a value in MB to shrink the log file too and finally hit OK.

Change if recovery model back to 'Full' (if applicable) and take a full backup of the database.

You can also do all of this via commands as follows:
 ALTER DATABASE MYDATABASE SET RECOVERY SIMPLE

 DBCC SHRINKFILE (MYDATABASE_Log, 1)

 ALTER DATABASE MYDATABASE SET RECOVERY FULL

0 comments:

Post a Comment