In this SQL Tutorial I will show you how to shrink log file (ldf) to "zero" MB. For the purpose of this tutorial I will use SQL Server 2008 R2.
Usage: As a developer I often work with development environements and is it common task for me to either shrink a log after I perform database restore from live environement or just reduce my current log file. The common problem is that shrink command is not always shrinking the log file and and this tutorial I will cover this particular issue as well:
NOTE: This method is used to permanently remove log data which is useful in development environment but it is not recommended in production environments.
See below an example of a database with log file (ldf) that is slightly over 200MB.
In order to successfuly reduce the size of a log using shrink method we will have to make a change to database recovery model. To reduce log file to almost "zero" MB we need to change recovery model to SIMPLE. If you don't do that you might have an issue of shrink database not shrinking.
I opened SSMS and connected to my local instance. I right clicked my database and selected properties.
In Database properties I went to options page (on the left side) and you can see on the right side recovery model that in my case is set to FULL and which I will change to SIMPLE.
See below example of my change. After the change I clicked ok.
It's time to shrink our database. I right click the database go to Shrink and select files. See example below.
A dialog box appeared and I changed File type to Log, select reorganize pages before releasing unsed space and shrink file to 0 mb.
NOTE: Remember full shrink does not work when you are NOT in simple recovery mode so you might be left with much larger log file than 0 mb.
Below you can see that my ldf which is log file was reduced to "zero" mb more precisely 1mb (1024KB).
NOTE: Do you need script? Remember you can use SCRIPT option at the top of the dialog box to get SQL code.
I hope this step by step will help you to shrink log file of your database.
Take care
Emil