managing sql log files - recommendations please


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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have any transaction log backup scheduled? If affirmative, how often it runs?
websssCEOAuthor Commented:
Hi Vitor
No I have nothing like this
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jose TorresCertified Database AdministratorCommented:
Ditto to Vitor's comments.
Scott PletcherSenior DBACommented:
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.
websssCEOAuthor 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 DBACommented:
Full backup every week.  Differential backup every 12 hours (or however many hours you prefer).  Tran log backup every 30 minutes.
websssCEOAuthor 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 DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.