How to Shrink a SQL Server Log File

In this FAQ we will see how to shrink database log files.

When we feel a application is slow in insertion, updation, or deletion is slow. This happens when log file(.ldf) grows more than the size of data file(mdf).we can check this by using the command SP_HELPDB to check the size of database. and DBCC SQLPERF(LOGSPACE).  If database is in FULL RECOVERY MODE then take a full backup or set the database to SIMPLE Recovery mode.

If database is in SIMPLE Recovery mode then execute the following command:

alter database <dbname> set single_user with no_wait
USE <dbname> GO DBCC SHRINKFILE (N'<dbname>' , 20)--in place if 20 specify the size of log file to be shrink in MB
GO
alter database <dbname> set multi_user with no_wait