UVB 8.x and Microsoft SQL Server Log Truncation

SUMMARY

Options for SQL Server Log Truncation with UVB

ISSUE

MS SQL Server Log Truncation

When managing SQL Server logs, you have several options. The first option described below is generally recommended, but two additional options are presented which may be selected if they fit the needs of your business. 

Option 1 (Recommended): Back up your SQL Server database transaction logs. 

Transaction log backups allow a database administrator to recover a SQL Server database to a point in time that is very close to when the failure occurred, maximizing the recovery point objective (RPO).  Thus, more data can be recovered when a failure occurs. 

Transaction log backups can be scheduled without Unitrends Virtual Backup, but Unitrends Virtual Backup may also be leveraged to trigger log backups and therefore log truncation, if desired.

Two supplied scripts can be used to backup up SQL Server transaction logs. They are:

  • 10-microsoft_sql_server.bat
  • SQLServer-BackupLogs.sql

 

Details about using these scripts are included in the next sections.

1.a.  Using the supplied batch file

 

Copy the supplied batch file, “10-microsoft_sql_server.bat” to the Unitrends scripts directory. The Unitrends scripts directory is located in %windir%\PHD\.  

Edit the batch file in the Unitrends scripts directory called “PostBackup.bat” so that it calls the batch file “10-microsoft_sql_server.bat“. 

The contents of the supplied batch file (10-microsoft_sql_server_.bat) follow: 

@ECHO off

 

REM call T-SQL script

sqlcmd -S localhost -i SQLServer-BackupLogs.sql -V16

 

REM set return message and return value

if %ERRORLEVEL% NEQ 0 echo An error was reported by SQL Server.  Check the SQL Server Logs for more details. 

 

exit %ERRORLEVEL%

 

In the line, sqlcmd -S localhost -i SQLServer-BackupLogs.sql -V16, the string localhost refers to the SQL Server instance that this script will act upon – edit this as needed. If more than one database instance must be handled, copy this line and include the additional database instance.

The string SQLServer-BackupLogs.sql refers to the included file that contains the T-SQL commands that sqlcmd.exe will run.  More details on this file are in the next section, 1.b. 

1.b.  Using the supplied T-SQL script file

 

The script file “SQLServer-BackupLogs.sql” contains Transact-SQL commands to be run against the database(s) that are going to have their transaction logs managed here. This file should also be copied to the Unitrends scripts directory, which is %windir%\PHD\. 

This file is referenced in the batch file described in section 1.a.
The contents of the T-SQL command file (SQLServer-BackupLogs.sql) follow:

DECLARE @databaseName VARCHAR(50)

DECLARE @fileDate VARCHAR(20)

DECLARE @filePath VARCHAR(256) 

DECLARE @fileName VARCHAR(256)

DECLARE @recoveryModel SQL_VARIANT

 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

 

DECLARE db_cursor CURSOR FOR 

SELECT name FROM master.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb') 

 

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @databaseName

 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       SET @fileName  = 'C:\temp\' + @databaseName + '_log_' + @fileDate + '.BAK' 

 

       SELECT @recoveryModel = DATABASEPROPERTYEX(@databaseName, 'Recovery')

       IF @recoveryModel = 'FULL'

       BEGIN      

           BACKUP LOG @databaseName TO DISK = @fileName WITH INIT

       END

       FETCH NEXT FROM db_cursor INTO @databaseName

END  

 

CLOSE db_cursor  

DEALLOCATE db_cursor

 

The lines starting with SELECT name might need to be edited. This is a Transact-SQL statement that filters the list of databases to be included.

The line starting with SET @fileName = specifies the transaction log folder/path and file name. It may also be edited as necessary. 

1.c.  Set your backup job to Truncate logs

 

Your Unitrends Virtual Backup backup jobs have a setting used to indicate that you want the guest VM to run scripts. In the case of Microsoft SQL Server transaction logs, we are dealing with the scripts discussed above in sections 1.a. and 1.b.

 

Option 2:  Set the database recovery model to simple

Although the recommended configuration calls for backing up your SQL Server database transaction logs, if transaction logs are not needed or desired, it is recommended that the target databases' recovery model be set to simple.  SQL Server will not write transaction logs when the recovery model is set to simple. This is not only more efficient than allowing the logs to be written (then erased), the issue of log truncation no longer exists. 

The biggest drawback to the simple recovery model is that the ability to provide point-in-time recoveries is reduced, possibly making it more difficult to meet your RPO. You will only be able to recover your last full database backup.

For more information on changing your SQL Server database's recovery model, please see https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/view-or-change-the-recovery-model-of-a-database-sql-server   

 

Option 3: Run a SQL Server database log backup without saving the backup file

If log backups are not desired and a database cannot be set to simple recovery model, Unitrends Virtual Backup can still be used to truncate a SQL Server database's transaction logs. 

Steps 1.a., 1.b. and 1.c. will be used in this method so follow those instructions above. The script file in 1.b should be modified so that the text starting with SET @fileName  = reads as it does in the following example:

...

WHILE @@FETCH_STATUS = 0  

BEGIN  

       SET @fileName  = 'NULL'

 

       SELECT @recoveryModel = DATABASEPROPERTYEX(@databaseName, 'Recovery')

       IF @recoveryModel = 'FULL'

       BEGIN      

           BACKUP LOG @databaseName TO DISK = @fileName WITH INIT

       END

       FETCH NEXT FROM db_cursor INTO @databaseName

END  

...

 

This script leads the database to believe that its transaction logs have been backed up so it will truncate the logs. The backup data is diverted to a non-existent destination and thus thrown away. 

It is highly recommended that transaction logs be backed up, but in cases where transaction log backups are not desired and the simple recovery model cannot be used, this method can be used. 

One drawback to this method is the same as if the database was set to simple recovery model – the ability to provide point-in-time recoveries is reduced (reducing your RPO). You will only be able to recover your last full database backup.

A further drawback to this method is that it results in additional overhead by allowing the database to write (and manage) transaction logs that are ultimately thrown away.

Checking the Outcome of the Transaction Log Backup Request

The Unitrends Virtual Backup job history logs will contain information about failures that were reported back to the Unitrends VBA. At the application level, Microsoft SQL Server Management Studio can be used to read SQL Server's logs.

To review SQL Server logs:

  1. Open Microsoft SQL Server Management Studio, logging in to the database instance that contains the target databases. 
  2. Expand the database server instance node. 
  3. Expand the Management node. 
  4. Expand the SQL Server Logs node. 
  5. Double-click on the Current node (or other node as appropriate). 

 

Figure 1 – SQL Server Management Studio with Current Logs selected.

 User-added image

 

You will see the log entries in a table on the right side of the window. Note the records that have the Source specified as Backup and Message content that begins with “Log was backed up. If a signal was not sent (or received) while the backup was running, a communication error occurred. There should be a corresponding error in the backup job log that experienced the issue. If there is an error message that indicates a problem occurred when doing the transaction log backup, the details of that failure should be in the SQL Server logs.

Figure 2 – SQL Server Management Studio – Log File Viewer

 User-added image

 

The details in the Message column will show which device the log backup was written to, as seen in the following figure:

Figure 3 – Log File Viewer example messages.

 User-added image

The DBCC SHRINKFILE Command

The DBCC SHRINKFILE command does not need to be run at any point in any of the options described above. The space in the transaction log files that was made available when the logs were truncated will be used for future log entries. 

Transaction log truncation and log file shrinking have very different meanings for SQL Server. In fact, DBCC SHRINKFILE should not be run unless the implications are very well understood and its effects mitigated. 

For further information on this topic, search dbcc shrinkfile bad in your favorite search engine.

Using VMware Tools Instead of Unitrends VB Backup Scripting

The provided scripts may also be used with VMware Tools. 

1.  Put the batch script (10-microsoft_sql_server_Logs-VMwareTools.bat) in the appropriate folder. 

ESX/ESXi 3.5 update 2 or later

C:\Program Files\VMware\VMware Tools\backupScripts.d\

ESX/ESXi 4.x

C:\Windows\backupScripts.d\

ESXi 5.0 or later

VMware Tools looks in both of the above locations for scripts

 

2.  Put the T-SQL script file () in the folder that is the parent to the folder chosen in #1 above, e.g., C:\Program Files\VMware\VMware Tools\. 

The batch script (10-microsoft_sql_server_Logs-VMwareTools.bat) is different from the one provided for the Unitrends Guest Tools script (10-microsoft_sql_server_Logs.bat) in that it needs to run only when the first argument passed to it from the VMware Tools is freeze. 

@ECHO off

 

if %1 == freeze goto doFreeze

goto EOF

 

:doFreeze

 

REM navigate to scripts directory

cd /d %~dp0

cd ..

 

REM call T-SQL script

sqlcmd -S localhost -i SQLServer-BackupLogs.sql -V16

 

REM set return message and return value

if %ERRORLEVEL% NEQ 0 echo An error was reported by SQL Server.  Check the SQL Server Logs for more details. 

 

exit %ERRORLEVEL%

 

:EOF

For more information about running custom scripts with VMware Tools, please see VMware's documentation (http://kb.vmware.com/selfservice/microsites/search.do?cmd=displayKC&externalId=1006671).

Troubleshooting

  • A request to back up transaction logs for a database that has not ever had a database backup will fail.  This is also true when a database has been switched from simple recovery model to full (a database backup needs to be performed before a transaction log backup will succeed).

 

The SQL Server Log item will appear as shown below.  In this log, there will be two line items for each database that failed for this reason.

 

Figure 4 – SQL Server Management Studio Log File Viewer

 User-added image

 

  • If a database is using the simple recovery model, a command to back up its transaction logs will fail. The provided sample script will not try to back up a database that uses the simple recovery model. No log entries will appear for this case, as it is not an error condition. 

SQL Server Sample scripts attached

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Contact us