Truncate log file of a MSSQL database

From time to time it’s required to clean up log file, but Managment Studio does not offer straightforward command to do that. This script will help in cleaning up.

LogFileName – a system name of the log file, which can be taken from DB properties or system table sys.database_files

USE DatabaseName
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (LogFileName, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

USE DatabaseName

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

ALTER DATABASE DatabaseName

SET RECOVERY SIMPLE;

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (LogFileName, 1);

-- Reset the database recovery model.

ALTER DATABASE DatabaseName

SET RECOVERY FULL;

Binary Notes