September 6, 2017
How to Truncate Log Files for Microsoft SQL Server with NAKIVO
Microsoft SQL Server is one of the most popular and powerful database servers. In general, it stores and manages plenty of business-critical data. To ensure data consistency, the SQL Server logs every database transaction completed by the server and creates log files called transaction logs. Any changes in the database are first written to the RAM, then written to the transaction log, and in a very short time committed to the database. SQL Server can accumulate a very large number of transaction log files. These files can be stored for a very long time filling up all of the available storage space, which may disrupt the normal operation of the server and even become the cause of data loss. To avoid such an unfavorable situation, you need to find a way to get rid of the SQL transaction logs.
So why not remove these transaction log files, but have their copy stored in a safe place for recovery in case of a disaster? One of the easiest ways to do this is to use a 3rd party backup solution, such as NAKIVO Backup & Replication, which has a very useful and easy-to-use SQL Log Truncation feature.
Microsoft SQL Server Transaction Log Truncation Step by Step
First, to be able to perform SQL log truncation, you need to use the application-aware backup mode. This mode makes it possible to perform tasks like log file truncation without any risk of losing data in the running applications. Application-aware mode can flush any application data out of RAM and pending I/O operations to disk, so that backups remain application consistent.
Follow the steps below to perform SQL log truncation in the application-aware mode:
Log in to the product’s Web interface and then click Create in the Jobs dashboard.
Select VMware vSphere backup job.
- At the first step of the Wizard – VMs – select the VM you need to backup. In our case, it is SQLSrv.
- At the second step of the Wizard – Destination – select the target Backup repository.
- At the third step of the Wizard – Schedule – schedule your backup job.
- At the fourth step of the Wizard – Retention – set your retention rules.
- At the fifth step of the Wizard – Options – you need to pay attention to the following settings:
- Job name: Indicate the name for your backup job;
- App-aware mode: Select the first default option – Enabled (proceed on error).
NOTE: Enabled (proceed on error) option allows finishing a backup job, even if an error occurs during the application-aware processing. Enabled (fail on error) option stops the backup process, if an error occurs during the application-aware processing. The third option – Disabled – disables the App-aware mode.
- Click Advanced options…
- Check the checkbox to the left of Truncate SQL Server logs.
- In the drop-down list to the right of Truncate SQL Server logs, select On successful VM processing only, and SQL log truncation will be performed only after the VM application-aware backup is successfully completed.
- Further to the right, click settings. In the appeared window, either select the existing credentials to the instance database of NAKIVO Backup & Replication or add the new ones. These credentials are necessary for proper authorization to the VM and getting access to the SQL database.
- When all the steps described above are completed, click Finish.
After finishing job creation, find this job in the Jobs dashboard, click Run Job and then click Run.
When the backup job is successfully finished and the SQL database with all of the log files is backed up in the application-aware backup mode and sent to the backup repository, NAKIVO Backup & Replication will send a command to truncate the transaction logs. The SQL Server will at that point start removing all of the unnecessary SQL Server transaction log files, which were committed to the database by the point of time the backup job was finished.
Application-aware backups allow ensuring transactional consistency with applications inside the VM. In case of Microsoft SQL Server, log file truncation with NAKIVO Backup & Replication is the way to maintain and keep the SQL Server file system in a good level with the sufficient free disk space. With proper SQL server credentials, you are able to perform the application-aware backup and truncate SQL logs. Even if such errors occur during the backup process, you will still be able to truncate SQL Server logs. With NAKIVO Backup & Replication, you can easily truncate logs of your SQL Server even without special skills.