The steps needed to shrink the transaction log file
Scenario: The database is in FULL RECOVERY mode and the transaction log file was growing to more than 2GB. We were taking the transaction logbackups in every 1 hour inteval. However the transaction log file still grew because at one point someone just set the recovery mode to FULL and there was no transaction log backups set up. The following steps were taken to reduce the siz of the log file.
--check the log file name
select * from sys.database_files
--Truncate the log by changing the database recovery model to SIMPLE. ( checkpoint occurs-frees up VLFs)
ALTER DATABASE ARCHIVEhcfsdatabase
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file the smalles size possible
DBCC SHRINKFILE (ARCHIVEhcfsdatabase_Log,0, TRUNCATEONLY);
GO
--reset the log file size to 64MB
ALTER DATABASE archivehcfsdatabase
Modify FIle(Name = 'ARCHIVEhcfsdatabase_Log',
,Size = 1024
)
-- Reset the database recovery model.
ALTER DATABASE ARCHIVEhcfsdatabase
SET RECOVERY FULL;
GO
--Do a full Back up - Until a full back up is done the database is not set to full recovery mode--
