Sql-server – Help to understand ‘log space used’ metric

backupsql servertransaction-log

The chart below comes from a monitoring tool. The metric shown is "log space used" for SQL Server 2016 for the last 7 days.

I'm looking for help to understand what's going on here.

enter image description here

What I do know is that the IT department is using a 3rd party tool (Veeam) to "back up" regularly. I'm advised full backups are performed nightly and log backups are performed every 15 minutes (but I am not sure if these are disk image backups of the database files or commands issued to SQLServer by Veeam to perform a backup). One of the steps includes "Process logs" for SQL Server and I believe one of the steps there is to truncate the logs.

I understand truncating the log means to mark as inactive those portions of the log file that may be re-used. At the moment, I understand (rightly or wrongly) that a log file portion may be re-used if SQL Server knows it has been written to disk – ie via backup.

What I see in this chart is

  1. An abnormally high usage of the log file for one DB at far left – ignore that.
  2. Daily growth and then daily clearing of each log file (3 humps).
  3. Growth over 3 days (Fri, Sat, Sun), then truncation.

My interpretation here is that log backups must be occurring, otherwise the usage would not drop back near to zero.

I suspect the log backups are occurring only once per day – although I don't think this chart proves it. For example, there is the possibility that there are more frequent log backups during business hours, but truncation does not occur at that time due to a long-running transaction. However at the nightly backup by Veeam, the truncation command is issued and succeeds. Despite this possibility, I still think it more likely that log backups are being carried out only once per day by Veeam.

Part of the issue here is that Veeam is a bit of a black box to me – I am not sure how IT has this configured. I am also not clear on whether it is taking snapshot images of the hard disks (ie database files), or instructing SQLServer to execute a backup.

My goal is to ensure we are taking frequent log file backups (and to work out a good frequency) in order to support a full recovery model across essentially all of these databases. If I determine that currently we are taking log backups no more frequently than nightly, then I also need to ensure that if I put measures in place for frequent log backups, that I'm not competing with Veeam such that Veeam and any scheduled task within SQLServer are not breaking log chains for each other – any suggestions on how to check and plan these things would be most appreciated.

Best Answer

You can check your backup history by using a standard report:

enter image description here

... by using some scripts: Script to retrieve SQL Server database backup history and no backups

--------------------------------------------------------------------------------- 
--Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date

... or having a look to your server's ERRORLOG file and searching for Source = Backup

Related Question