Configuring Azure backup to truncate the SQL Server log files after successful Azure VM backup?

EnterpriseArchitect 4,761 Reputation points
2022-09-03T12:55:50.68+00:00

People,

How can I configure Azure backup to truncate the SQL Server log files after a successful Azure VM backup?
Because at the moment, my App server SQL logs are filling the disk and crashing the apps due to 0 bytes free left.

Thank you.

SQL Server on Azure Virtual Machines
Azure Backup
Azure Backup
An Azure backup service that provides built-in management at scale.
1,132 questions
Azure SQL Database
Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,158 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. JimmySalian-2011 41,921 Reputation points
    2022-09-03T13:12:20.813+00:00

    Hi EA,

    Welcome back.

    At this moment the process does not truncate logs as per MS and I guess you will have to configure the log truncate via the SQL Process

    237428-image.png

    back-up-a-transaction-log-sql-server

    ==
    Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 101.4K Reputation points MVP
    2022-09-04T09:52:22.42+00:00

    If you are only relying backing up the VM as such, and you are not backing up the SQL Server database as such, you should put the database in simple recovery. With this recovery model, SQL Server truncates the transaction log every time it runs a checkpoint. (And if the log file has already grown to large, you need to truncate it).

    However, I am not sure that relying on the VM backup only is a wise strategy. When backing up SQL Server database, it is important to get a consistent backup, so that you don't restore something that is corrupted.

    The safest way to do this is to use the BACKUP DATABASE statement in SQL Server. Backing up the files or the VM should be OK, provided that there are no writes while the backup process is running. This can be achieved through the VSS service, which freezes writes to the database files while the backup is running. I don't know how the Azure VM backup works, so I can't speak to this. But this is something you need to verify, before you start relying on this method.

    You should also ask yourself, how much data you are prepared to lose in case of a disaster. If you are content with restoring the most recent backup of the VM, and lose a couple of hours of work, you can stick to the current pattern and have the database in simple recovery. But if you want to lose as little as as possible, you need to back up the transaction log regularly with the BACKUP LOG statement.

    1 person found this answer helpful.
    0 comments