How To Shrink Database Log File In Sql Server

Select size of database files before shrink

SELECT Name AS Logical_Name,(size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'MyDB' GO

 

USE MyDB; GO

Truncate the log by changing the database recovery model to SIMPLE.
 

ALTER DATABASE MyDB SET RECOVERY SIMPLE WITH NO_WAIT; GO

Shrink the truncated log file to 1 MB.
 

DBCC SHRINKFILE(MyDB_log, 1); GO

Reset the database recovery model.
 

ALTER DATABASE MyDB SET RECOVERY FULL WITH NO_WAIT; GO

Select size of database files After shrink
 

SELECT Name AS Logical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'MyDB' GO