Showing posts with label SQL Server Optimization. Show all posts
Showing posts with label SQL Server Optimization. Show all posts

Friday, July 6, 2018

How to shrink SQL Server Database log file


Shrink SQL Server Log Files.


I have been asked this question lot of time that my log file size is increased and I want to shrink it but don't know how to perform this.

So I decided to make a post for this task so it can help people.

Lets start on the process.

1) Log in to the SQL Server using SSMS  (SQL Server Management Studio) with administrator credentials.

2) Select the database whose log file size is increased.

3) Take the full backup of that database so that in case of any issue during shrink operation we will have the original backup and it can be restored. Make sure the recovery model should be "Full"as displayed in image below.



4)  Right click the database and click properties it and select the "Files" option from left menu of Database Properties dialog box. You can see the log file size here as displayed in image below.



 5) Now you need to change the recovery option to Simple instead of full. So click the "Options" tab in Database Properties dialog box. Now choose the Recovery model to "Simple".

 


 6) Now Click OK and close this dialog box. Right click the database and select Tasks > Shrink > Files menu

7) It will open the Shrink File dialog box. Select the "Log" from File Type drop down and select the "Release unused space" option as displayed in image below.



8) Now click the OK button and it will start the Shrink process and after some time it will shrink the log file size and you will notice the log file size would be minimized.

9) Now you can revert back the recovery model to "Full" as it was earlier. To do this follow the step number 5 and select option "Full" and hit OK button.


Hope this will help you. 

Be happy any stay optimized.

Thanks.