0

I'm running SQL Server 2019 and I need to do manage my backups and logs better.

My main database (mdf) is only 9GB however the log (ldf) is 792GB. I've created back ups of these using SSMS > Tasks > Back up... I was expecting after a back up of the database the logs would reset. As this did not happen against googles advice I tried to shrink the log file, nothing happened.

I've tried setting the max size of the log file to something less, however it won't allow this as the log file is already too big. This is a dev server and very shortly I will be switching this to prod. And so don't currently need historical back ups, but will do once live.

In the meantime I've had to delete other databases and log files to free up space. I've put mdf and ldf on different partitions.

EDIT: I was in Full Recover model however I have now switched this to SIMPLE. I will want point in time in the future but for now this will do. I took a backup however in simple recovery model only full and differential backup types are available, I can not now back up the log. I did both options available, hoping this would reduce the size of the log, it did not.

Question: Now I have a backup, can I just delete the log file and will SQL server create a new one. I can just repeat this process, when the disk fills up?

Rhys
  • 103
  • 3
  • 1
    Does this answer your question? Why Does the Transaction Log Keep Growing or Run Out of Space? – Charlieface Mar 09 '22 at 13:36
  • 1
    Check select name, log_reuse_wait_desc from sys.databases that should tell you what's going on and why the log is needed. Also the log file will not shrink after growing automatically, you need to shrink it manually, and it won;t shrink if the log is needed. – Charlieface Mar 09 '22 at 13:37
  • @charlieface currently this returns NOTHING for the db in question. It does say LOG_BACKUP for some of my other smaller dbs that I haven't backed up. – Rhys Mar 10 '22 at 09:03
  • 1
    No, do not delete the transaction log, it is used constantly. However in SIMPLE the server will do automatic checkpoints regularly, so it can be much smaller. Shrink the log file size through SSMS, which will work as long as log_reuse_wait_desc says NOTHING. In SIMPLE mode you cannot do a log backup, so you only have the option of a full or diff restore. – Charlieface Mar 10 '22 at 10:30

2 Answers2

3

Point-in-Time Recovery

Do you need point-in-time recovery? If not, check your recovery model, and consider changing to SIMPLE. With the SIMPLE recovery model, transactions data is only retained in the transaction log for the duration of the active transaction, and then released.

If you do need point-in-time recovery, you're going to need to start taking Log Backups, in addition to Full Backups. Figure out what the business RPO/RTO is, and plan a backup schedule accordingly. My preference is to take log backups every 15-minutes as a good starting point. But, it's not uncommon for people to run them as often as every 5-minutes, or even every 1-minute.

Once you choose one of the above options, you should then be able to truncate your log file back down to a manageable size.

Brendan McCaffrey
  • 3,367
  • 2
  • 6
  • 29
  • I would prefer point in time recovery. I did try switching to SIMPLE, but I don't know what to truncate the log file? – Rhys Mar 10 '22 at 09:05
1

What type of backup did you take?
Only a Transaction Log backup will have any effect on the Transaction log.

Phill W.
  • 8,706
  • 1
  • 11
  • 21
  • That's not actually true. A Full backup does a CHECKPOINT before it starts, see eg https://www.sqlskills.com/blogs/paul/more-on-how-much-transaction-log-a-full-backup-includes/ – Charlieface Mar 09 '22 at 13:35