Configuring SQL Server database transaction logs truncation regularly?

Asked Sep 03 '22 at 12:53

Active Sep 03 '22 at 13:00

Viewed 54 times

How can I configure SQL Server 2014, to allow truncating the logs by itself so that it doesn't fill up my database drive?

I need some help as I'm not a DBA myself and this SQL Server is installed within Azure VM backed up with Azure backup which does not truncate log files.

Thanks in advance.

edited Sep 03 '22 at 13:00

Paul White

83,961
28
402
634

asked Sep 03 '22 at 12:53

Senior Systems Engineer

3

Do you need point in time recovery in your database? – J.D. Sep 03 '22 at 13:45
1

Are you taking log backups to use Point in Time Recovery? If not, the DB could be set to 'simple' mode so the log auto truncates. – Ali Razeghi - AWS Sep 03 '22 at 15:45
1

Azure Backup should perform the log backups. https://learn.microsoft.com/en-us/azure/backup/faq-backup-sql-server – David Browne - Microsoft Sep 03 '22 at 17:34
2

If you don't need Point In Time recovery then just switch to Simple Recovery Model – Charlieface Sep 04 '22 at 03:17
No, I do not need Point in Time. So I assume I can just set it to Simple recovery mode so the tx logs can be under the control ? – Senior Systems Engineer Sep 05 '22 at 07:08
1

@DavidBrowne-Microsoft, based on the link you've provided, https://learn.microsoft.com/en-us/azure/backup/faq-backup-sql-server#can-i-use-azure-backup-for-iaas-vm-as-well-as-sql-server-on-the-same-machine- "copy-only full backup on the VM to not truncate the logs." – Senior Systems Engineer Sep 05 '22 at 07:09
Thank you @DavidBrowne-Microsoft for your guidance :-) – Senior Systems Engineer Sep 20 '22 at 06:42

Configuring SQL Server database transaction logs truncation regularly?

0 Answers0