Cannot shrink my DB after backup logs: Seems the backup marks last VLF

Question

Hi,

I have a hard used DB (the log files grows about 5Gb per day), that I cannot shrink the log file after backup.

This is my backup sql file:

GO
print 'Set single user';
ALTER DATABASE [My DB name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BEGIN TRY
print 'backup log files and shrink';
BACKUP LOG [My DB name] TO DISK = N'D:\DbBkp\DbBackup_LOG.BAK' WITH NAME = N'Backup Log 1 di Tuesday 20/05/2025';
BACKUP LOG [My DB name] TO DISK = N'D:\DbBkp\DbBackup_LOG.BAK' WITH NAME = N'Backup Log 2 di Tuesday 20/05/2025';
BACKUP LOG [My DB name] TO DISK = N'D:\DbBkp\DbBackup_LOG.BAK' WITH NAME = N'Backup Log 3 di Tuesday 20/05/2025';
dbcc ShrinkFile(2, 10);
print 'Backup completed';
END TRY
BEGIN CATCH
  print 'Error trown: ' + Cast(ERROR_NUMBER() as varchar(10)) + ' (' + ERROR_MESSAGE() + ')'
END CATCH
print 'set Multi user';
ALTER DATABASE PANTH01 SET MULTI_USER

I wanted to be sure that the shrink would be done, then setted single user mode and done more than a single backup, because in some cases I saw more than a single page was backed up the second backup (I think because the large files needs to write something in the VLF during the backup). The TRY construct is to avoid, in case of errors during the backup, to left the DB in single user mode.

This is the result (I didn't translate system messages it because I cannot write the exact text would be in english, Non è possibile compattare il file di log 2 (db_log). Il file di log logico situato alla fine del file è in uso. means cannot shrink log file 2 (db_log). The logic log file at the end of the file is in use):

Set single user
Rollback di transazioni non qualificate in corso. Completamento del rollback stimato: 0%.
Rollback di transazioni non qualificate in corso. Completamento del rollback stimato: 100%.
backup log files and shrink
Elaborate 5398798 pagine per il database 'My DB name', file 'db_log' nel file 1.
BACKUP LOG ha elaborato 5398798 pagine in 2326.956 secondi (18.125 MB/sec).
Elaborate 15 pagine per il database 'My DB name', file 'db_log' nel file 2.
BACKUP LOG ha elaborato 15 pagine in 0.171 secondi (0.671 MB/sec).
Elaborate 1 pagine per il database 'My DB name', file 'db_log' nel file 3.
BACKUP LOG ha elaborato 1 pagine in 0.049 secondi (0.029 MB/sec).
Non è possibile compattare il file di log 2 (db_log). Il file di log logico situato alla fine del file è in uso.
DbId   FileId      CurrentSize MinimumSize UsedPages   EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
     5           2     2862776        2225     2862776           2224

(Righe interessate: 1)
Esecuzione DBCC completata. Se sono stati visualizzati messaggi di errore DBCC, rivolgersi all'amministratore di sistema.
Backup completed
set Multi user

Naturally I don't want set the DB in not recovery mode.

Answer 1

My English is very poor, so communicating with you using a translator might not be accurate.

I once encountered a similar problem. There was no retractable space after backup, but at this time log_reuse_wait_desc was nothing. However, I haven't tried single-user.

My processing experience is that the contraction is performed immediately after backing up the transaction log, and it needs to be processed in a loop.

The speculated reason is that other users or system transactions will be initiated immediately after you complete the truncation of the last vlf. They will immediately make the last vlf active again, resulting in the inability to contract even if a large number of VLFS are inactive.

Answer 2

(the log files grows about 5Gb per day), that I cannot shrink the log file after backup.

Why do you want to shrink the log file, when it grows expected again and again? That are only expensive I/O opertions.

Run more often log backups to keep the size small.

Answer 3

It's very simple: Your database keeps growing because you insist on shrinking it. Stop shrinking it, and the log file will assume the sizes needed to support your workload.

Keep in mind that growing the log requires the new log space to be zeroed out, which takes resources from the system, so it is really counterproductive to shrink it.

Also, there is no need to set the database in single user to shrink the log.