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