Attix5 Pro FAQ
Search:     Advanced search

How to truncate data or log files using the Script plug-in

Article ID: 282
Last updated: 15 May, 2015

The Attix5 Script plug-in can be used to automate the truncation of log files using the DBCC SHRINKFILE command. This is typically required when the log files get too big when using a non-SIMPLE recovery model in SQL Server.

If you are running a non-SIMPLE recovery model, you would normally be planning to perform point-in-time recovery, and would use a separate transaction log backup to achieve this.

Warning

Warning:  Database log files hold status information on transaction logs. If a backup of the transaction logs is not run it can prevent the log file from being truncated and will continue to grow in size regardless of attempts to shrink the log file. See the section below "Backing up transaction logs"

Step 1: Create the script

To create a script with the appropriate commands:

  1. In the SQL Server installation folder, create a folder called "scripts".
  2. Within the "scripts" folder, create a file called "ShrinkDatabaseName.bat" containing the following line (This will connect to the SQL Server and run the "ShrinkDatabaseName.sql" file):
    sqlcmd -S SERVERNAME\SQLINSTANCENAME -i "ShrinkDatabaseName.sql"
    Replace the following values:
    • "SERVERNAME": Use your SQL server name
    • "SQLINSTANCENAME": Use your SQL server instance
  3. Within the "scripts" folder, also create a file called "ShrinkDatabaseName.sql" containing the following lines:
    USE DatabaseName;
    GO
    -- If Transaction Log Backup needs to be run to clear status in log file, uncomment next two lines and specify change the path of the "DISK" variable:
    -- BACKUP LOG DatabaseName TO DISK='E:\DBBACKUP\fileName.TRN'
    -- GO
    DBCC SHRINKFILE (DatabaseName_log, 0);
    GO

    Replace the following values:
    • "DatabaseName": Use the name of your relevant database
    • "DatabaseName_log": Use the LogicalName for the log file as found in the Database Properties. For example, for "Attix5CloudLogDatabase" the log file's logical name is "Attix5CloudLogDatabase_log":
  4. Test the batch file by running it from a command prompt.

Step 2: Automate the script execution

To automate the script using the Script plug-in:

  1. Ensure the Script plug-in is installed in your Backup Client. (Consult the "Script" section of the Plug-ins User Manual on how to achieve this and for more information on the plug-in.)
  2. In the Backup Client, open the Tools menu, select Plug-ins and click on Scripting.
  3. Click on Add to add the script created in the step 1 above.
  4. Enter a name for the script
  5. Set Script run on event to “Backup End”.
  6. Set Execute to your “ShrinkDatabaseName.bat” file.
  7. Set Execute in folder to the filepath where the .bat file is saved e.g. C:\Program Files\Attix5 Pro\Backup Client SE\scripts\” as in the example above.
    • Optionally, tick the Wait for process to complete checkbox to make the backup client wait for the script to run.

You can now run backup in the backup client. The VSS backup will run as normal, and the script will run at the end of the backup process, reducing the size of the database log file if possible.

Backing up transaction logs

As mentioned in Step 1 above, the DatabaseName.sql file contains the lines that can be used to run a transaction log backup prior to truncating the log file. Simply remove the "--" from the beginning of both lines to run them as part of the script:

-- BACKUP LOG DatabaseName TO DISK='E:\DBBACKUP\fileName.TRN'
-- GO

If you find that transaction log backup are necessary, consult your SQL administrator as it may indicate one of the following:

  • Existing transaction log backups are not functioning as intended
  • SIMPLE recovery model would be more appropriate if point in time recovery is not required.

Article ID: 282
Last updated: 15 May, 2015
Revision: 7
Views: 1504
Comments: 0
Posted: 07 Jan, 2015 by Du Plessis S.
Updated: 15 May, 2015 by Du Plessis S.
This article was:  
Prev   Next
Article 302 - Restoring System State in Windows 2008, Windows 2008 R2 (using a...     Article 306 - Compression efficiency of the MS SQL and VSS plug-ins