2x SQL Server 2019 in AlwayOn group - how to remove transaction log

Question

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?

P.S.: This question is NOT a duplicate of the "Why Does the Transaction Log Keep Growing or Run Out of Space?"! When using Always-ON Group, the usual options are limited and most of them are NOT working. The "Always-ON Group" is not even once mentioned in the previous question. Also, when using the AO Group there may be multiple procedures that have to be done on multiple servers differently to achieve the goal. Basically, Microsoft made sure it's not just that simple as it is with one standalone SQL server.

Answer 1

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.

Answer 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?.