The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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.
Method 2
What type of backup did you take?
Only a Transaction Log backup will have any effect on the Transaction log.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0