0

I have a two-node SQL Server 2016 Always On Cluster. I also have a job that updates about 200 records in one table every 3 minutes. Even when there are no updates, it does change a TimestampDt field with the current time.

Previously my backup files were growing at a manageable 10-20 MB per day. However, now that I started making the TimestampDt field updates every 3 minutes (this was a recent change) my backup files are growing at a size of 200 MB per day.

I think that the backup is including the entire transaction log.

Here is the backup SQL Query:

SET @BackupUrl = CONCAT('https://backup.blob.core.windows.net/databasebackup/dbname-',GETDATE(),'.bak');
BACKUP DATABASE dbname
   TO URL = @BackupUrl
   ;

What is the recommended approach here? Should I purge the transaction logs somehow on backup? If so, how can I do that safely in an Always On Availability Group clustered environment?

Any help would be greatly appreciated.

3
  • Is backup compression enabled? If you're using BLOB, dirt cheap & plentiful, why is 200MB/day a problem? Commented Apr 23, 2020 at 16:45
  • @LowlyDBA Good question - I'm actually more worried that somehow the growth of the backup files means that the server could run out of disk space in a few months
    – A X
    Commented Apr 23, 2020 at 16:50
  • If you're worried about the size of your database relative to your local disks, I'd re-phrase the question as such. If you're backing up directly to BLOB as it seems, backup size on disk is a non-issue. I have some tips on space saving on my site if that is what you actually need. Commented Apr 23, 2020 at 16:53

2 Answers 2

1

When a database is operating in the FULL recovery model (which yours is, as that's a requirement for AGs), transaction log backups must be taken periodically. Without doing this, the log will grow until it's stopped by hitting the maximum log file size or your storage runs out of space.

You asked:

Should I purge the transaction logs somehow on backup?

Only if you don't care about point in time recovery, which you probably should if you're updating records every 3 minutes.

The recommended approach is to take both full and transaction log backups on a regular schedule, which will keep the TLog at a reasonable size (and probably a lot smaller than it currently is, so at some point you may want to right-size it).

The easiest way to get this up and running is by installing Ola Hallengren's Maintenance Solution, and have it create the Agent jobs to run these backups. This is totally free and used by tens of thousands of DBAs the world over. It's AG-aware as well. Schedule your full and TLog backup jobs to run on a schedule that meets your Recovery Point Objective (RPO) requirements; in my environment, that's full backups daily and TLogs every 15 minutes.

1

What type of backup are we talking about?

Full? This only include the log records produced while the backup command is running (basically).

Differential? Same as above, as for the log records. As for data, it include the modified extents since last full backup. I.e., it will grow and grow until next full backup.

Log? It includes the log records since last log backup.

Also, what options did you specify for the backup command? If you post the backup command you use, we have some something to go on...

2
  • thanks for your quick response! I added my query above. It is my understanding that on Always On availability groups backups are always done in Full mode - but I could be mistaken about that
    – A X
    Commented Apr 23, 2020 at 16:39
  • A database in an AG config need to be on full recovery mode, but that is not the same as what backup types you perform. You can definitely perform all backup types in an AG setup (full, diff, log), but you can only do full and log backup on a replica (of you want to offload the primary). Commented May 11, 2020 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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