David Waugh

Shrinking the Database Transaction Log

Blog Post created by David Waugh Employee on Sep 28, 2016

Hermes Bojaxi posted the following useful information which is well worth sharing. Normally a Maintenance Job runs every week which will shrink the Database Transaction logs. If for some reason the maintenance job repeatedly fails you may have very large database transaction logs.


You can shrink transaction logs with this script which is actually inside the Maintenance job. The script should be run against either the ECAT$PRIMARY or ECAT$SECONDARY databases.

DECLARE @LogicalLogFileName nvarchar(50)
, @SQL nvarchar(1000)
, @Continue bigint = 100
, @CurrentDate datetime2(3) = getutcdate()
-- Shrink Log file
SELECT @LogicalLogFileName = FILE_NAME(2) ;
SET @SQL = 'DBCC SHRINKFILE('+@LogicalLogFileName+', 1)' ;
EXEC ( @SQL) ;


After running this script go to database -> TASKS -> Shrink ->Files and chose the option to release unused space.


Further information:


Shrinking the Transaction Log