0

I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ...

What I want is just one full (simple) backup everyday. Do I need to do step 8 (Backup Transaction Log)? Or can I just run some kind of query like "TRUNCATE Transaction Log" after the backup is done? And am I missing any steps, or running something I shouldn't?

"Management" - "Maintenance Plans" - "My_Maint_Plan"

  1. Check Database Integrity
  2. Shrink Database
  3. Reorganize Index
  4. Rebuild Index
  5. Update Statistics
  6. Clean Up History
  7. Backup Database (Full)
  8. Backup Database (Transaction Log)
  9. Maintenance Cleanup Task

This plan creates 2 files daily ... .BAK (1.7 gig), and .TRN (8 gig) ... Do I really need the 8 gig TRN file?

Improve this question
3
  • a transaction log is need for reconstruction. As you don't seem to need them. rebuilding index is usually not needed pn a daily basis.
    – nbk
    Mar 19, 2021 at 21:10
  • What are you recovery objectives? Ie how much data can you afford to loose? How long should it take to restore your database? Your transaction log backup probably wouldn’t be so big if you didn’t rebuild your indexes (and you generally shouldn’t be doing that anyway)
    – Andrew Sayer
    Mar 20, 2021 at 10:06
  • Your transaction file would be tiny if not for steps 2, 3 and 4, which should probably be removed. Why are you shrinking the DB, it clearly needs to be the size it is. And reorganize and rebuild are duplication of efforts. A rebuild (the better option) is usually not needed very often on modern SSD drives. I have a big heavily used DB, I do tran log backups every 15 minutes, diff every 6 hr, full once per day, and the log backups are pretty small
    – Charlieface
    Mar 21, 2021 at 1:55

2 Answers 2

Reset to default
2

If you don't want point-in-time restore for your databases, switch them to SIMPLE recovery, and simplify your maintenance to:

  • Check Database Integrity
  • Update Statistics
  • Backup Database (Full)
Improve this answer
0

@DavidBrowne has mentioned the option of SIMPLE recovery mode if you don't need point-in-time recovery.

But if you do need FULL recovery, you can and should still simplify your scripts:

Your transaction file would be tiny if not for steps 2, 3 and 4, which should be removed.

  • There is no need to shrink the DB, it clearly needs to be the size it is. Shrinking is only useful if there was a sudden increase in data, which is now removed and will not return.

  • REORGANIZE and REBUILD are duplication of efforts. Even a rebuild, which is the better option to pick normally, is not usually needed very often on modern SSD drives.

  • It's also unclear what "Clean Up History" and "Maintenance Cleanup Task" do, and why they are needed.

So you are left with the same three steps outlined by @DavidBrowne:

  • Check Database Integrity
  • Update Statistics
  • Backup Database (Full)

These you should do nightly.

Then you can do an hourly (or even more often) transaction log backup, and just a log backup, nothing more. These will be very small, as the shrink and index rebuilds are what was making them blow up so much.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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