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.
Comments
0 comments
Please sign in to leave a comment.