835 - How to truncate Microsoft SQL Server logs for backup

Instructions

To truncate (shrink) Microsoft SQL Server logs as part of a database backup using a script, add the following line to your backup script:

sqlcmd -S <SERVERNAME>\<SQLINSTANCENAME> -i "ShrinkDatabaseName.sql" [-U <Username>] [-P <Password>]

Examples:

Create a file called "ShrinkDatabaseName.sql" containing the following lines, replacing the database name and database name log file:

USE DatabaseName;
GO

DBCC SHRINKFILE (DatabaseName_log, 0);
GO

Alternatively, if a log backup is not being performed and is needed, use the following lines:

USE DatabaseName;
GO

Also, if a transaction log backup needs to be run to clear status in log file, include next two lines and change the path of the "DISK" variable:

BACKUP LOG DatabaseName TO DISK='C:\DBBACKUP\fileName.TRN'
GO
DBCC SHRINKFILE (DatabaseName_log, 0);
GO

If transaction log backups are needed for multiple databases, the steps above can be repeated in the script as required for each database.