SQL: How to shrink db log file effectively

Let DB Name DBNAME 

use DBNAME 

--Display DB log file Size

select (size*8)/1024 as FileSizeKB from sys.database_files  --here size of log file will show in MB

 

--End Display DB log file Size

 

 --Start to shrink

ALTER DATABASE [DBNAME ] 

SET RECOVERY SIMPLE;

CHECKPOINT;

DBCC SHRINKFILE (DBNAME _log,0);

ALTER DATABASE [sd]

MODIFY FILE (NAME=DBNAME _log,SIZE=2000MB,MAXSIZE=UNLIMITED,FILEGROWTH=1000MB);

--Optional if you want the database in full recovery mode 

--for point in time recovery going forward

ALTER DATABASE [DBNAME ] 

SET RECOVERY FULL;

 --End to shrink

Here Log file will be shrinked to 2GB . you may change this value as per your requirement...