-3

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.

5
  • 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
  • 1
    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
  • @TiborKaraszi interesting, could you send a link with some example with nul ? Commented Jul 3 at 21:27
  • 1
    @AlekseyVitsko if you are using Ola Hallengrens solution you can specify nul as backup path. Else just do BACKUP LOG dbname TO DISK = 'nul' Commented Jul 4 at 16:19

2 Answers 2

3

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.