managing sql log files - recommendations please

websss
websss used Ask the Experts™
on
Hi

I have a asset tracking app which tracker people and cars, we use sql server 2014 enterprise
Currently the MDF is at 3gb and the LDF is at 33GB

the recovery model is set to full, and i've just left it that way, but now the log file is becoming too big, I need to manage that properly

One suggestion from the team was to set the log file to 10GB, and have a sliding thing so it only keeps the latest data (we only really care about the last 1 month/ 1 week but it we keep up to 1 year)

Does this sliding thing actually exist?

based on the brief above, how would you manage this large log file?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Do you have any transaction log backup scheduled? If affirmative, how often it runs?
websssCEO

Author

Commented:
Hi Vitor
No I have nothing like this
IT Engineer
Distinguished Expert 2017
Commented:
I've thought so. That's why the database transaction log is growing that much..
First of all you need to understand the Database Recovery Models so you can chose the best to fit your needs.
With Full Recovery Model you can restore the database to any point on time and mostly this is used in Production environments. The other side of the coin is that you'll need more storage to support this solution. You'll need to schedule a regular BACKUP LOG to keep the transaction log file small. For that you have some options:
1) Create a SQL Agent job with the necessary Backup Log command and schedule it.
2) Use the built-in Maintenance Plan Wizard that exists in SSMS
3) Use a more professional way to do it with Ola Hallengren solution
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jose TorresCertified Database Administrator

Commented:
Ditto to Vitor's comments.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You first need to decide, at a business level, how long a period of time you can afford to lose data for.  But be realistic, as the shorter the time frame, the more work it is on the database side. That decision will determine whether or not you need to backup log files, or if you can go to simple mode and skip the log backups completely.

For example, if you decide you just need a database backup no more often than, say, every six hours, meaning you could lose all activity between each backup, put the db in simple mode and do a full backup that often (for a db of only 3gb, I don't see a need to mess with differential backups at that time range).

If you decide on less than six hours, say, then you could use either differential backups, as above, or use full recovery model, including regularly scheduled log backups, or a mix of the two.  In that case, you would also need to do some "practice" recoveries using log files, since applying log files to a backup takes some getting used to.
websssCEO

Author

Commented:
Thanks all
We need FULL

and I need to keep the last 1 month and ideally I would like to keep as much new data as possible,  but I guess if I had to choose a number I could loose the last 30 mins

Does that mean I need to do a full backup (.Bak) every month,  and then do differental every 30 mins?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Full backup every week.  Differential backup every 12 hours (or however many hours you prefer).  Tran log backup every 30 minutes.
websssCEO

Author

Commented:
Great thanks
Do I delete the bak file every week and only keep latest?

Do I do the same with differential etc
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
It depends.  If you only need to retrieve data back to the current week, then yes.

If you would like to keep older copies "just in case" someone needs to recover, you can keep additional week(s).

At the last couple of places I've been, we did this:

1) Keep the full backups for 6 weeks.  You can see data as it was at the end of the week for the last 6 weeks.
2) Keep the differential backups for 2 weeks.  You can see data to any diff backup time during the past 2 weeks.
3) For critical dbs, keep the log backups for 1 week.  You can see data to any point in time for the past 1 week.

You can adjust that to meet your data requirements, but it gives you a rough idea.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial