-1

We have a problem with our Microsoft SQL Server 2019. We have 2 servers in an Always On group with one database. Problem is the actual (real) database size which is about 1 GB, but it takes up around 15 GB on the server. The problem seems to be the transaction log which logs every change in the database.

There is a lot of data pouring into the database – small data, but a lot. This means a lot of SQL updates, inserts etc.

Both SQL Servers are in VMs and those are backed up every day, so we don’t need any other backups, just small databases. I understand that the log is there to make sure both servers in the AO group has the same data, but jesus, just synchronize databases between servers and delete the log, no?

Nobody will ever use the backlog to revert the database to previous state, because we need the latest data and previous version would be a problem.

We tried to shrink the log, but since there is AO group it was unsuccessful and AI came up with some shady script which we are afraid to run. There are supposed to be some way how to purge the log by doing some kind of backup, but never found real help on that.

Simply put, we don’t need the log, we don’t want the log, how to get rid of that?

3
  • Aside... VM backups, even with VSS, aren't guaranteed to produce reliable backups from which you could restore functional SQL Server databases. I recommend reading up about SQL Server's built-in backup and restore procedures, especially around database recovery models and what happens with transaction logs after backups in each recovery model. Commented May 26 at 5:32
  • Backup the log following the instructions here: learn.microsoft.com/en-us/sql/relational-databases/… Commented May 26 at 20:28
  • If you don't need log backup for restore purposes, then just schedule a log backup every hour to the filename nul. That is what I typically do for my AGs in test environments. Commented May 26 at 20:58

2 Answers 2

2

Simply put, we don’t need the log, we don’t want the log, how to get rid of that?

You don't. SQL Server requires it to run and Always On requires it to function properly.

2

There are supposed to be some way how to purge the log by doing some kind of backup, but never found real help on that.

They're called Transaction Log backups. For a database that's set to the Full Recovery Model, routine Transaction Log backups are necessary to mark the Transaction Log file for re-use so it can be overwritten by new transactions that occur, as opposed to growing the Log file like you're seeing. Note, minimally a Full backup is required prior to taking Transaction Log backups, for them to be valid.

As per the Microsoft docs, Transaction Log backups should be taken frequently (relative to how frequent your database changes). For reference, I run them every 5 minutes on a low transactional set of databases. Some people even run them every minute. Part of the determination on how frequent to run them depends on how granular of point-in-time recovery one needs (which sounds like is not really a factor for you in this case). But you should run them frequently enough based on how quickly you need the Transaction Log marked for re-use to be overwritten by new transactions.

Simply put, we don’t need the log, we don’t want the log, how to get rid of that?

In general, the Transaction Log is needed (even if you don't want point-in-time recovery) by the database system so that if an active transaction was cancelled / killed and rolled back, the database system is able to correctly return the database to its state prior to that transaction starting, in a valid manner.

For further information specific to why an AlwaysOn Availability Group needs the Transaction Log, please see this DBA.StackExchange question: Why is Full Recovery model a requirement for an Availability Group?.