SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Shrinking Truncate Log File – Log Full

This blog post would discuss SHRINKFILE and TRUNCATE Log File. it looks impossible to shrink the Truncated Log file. Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName
GODBCC SHRINKFILE(<TransactionLogName>, 1)BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLYDBCC SHRINKFILE(<TransactionLogName>, 1)GO

USE [master]
GOALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAITDBCC SHRINKFILE(TestDbLog, 1)ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO

I suggest you stop this practice. There are many issues included here, but I would list two major issues:

1) From the setting database to simple recovery, shrinking the file and once again setting in full recovery, you are in fact losing your valuable log data and will be not able to restore point in time. Not only that, you will also not able to use subsequent log files.

2) Shrinking database file or database adds fragmentation.

There are a lot of things you can do. First, start taking proper log backup using following command instead of truncating them and losing them frequently.

BACKUP LOG [TestDb] TO  DISK = N'C:\Backup\TestDb.bak'GO

Remove the code of SHRINKING the file. If you are taking proper log backups, your log file usually (again usually, special cases are excluded) do not grow very big.