Need advice for logs

Question

Need advice for logs

jbeclapez

SSC-Addicted

Points: 405
More actions
September 24, 2019 at 2:33 pm

#3682954

Hello,
I am new to SQL server management. There is a lot of info on the internet but some seems contracdictory.
After listening to a member of this forum I ended up installing ola hallengren scripts. I run full backups and log backups everyday.
I would like to keep my log files small.
I did read that we should not shrink databases because of the loop of reindexation due to fragmentation. I understand that. But what about shrinking the logs? Is it the same? Can I shrink a log after a back up with creating potential issue?
I am surprised to see that the scripts of ola hallengren do not include shrinking of the logs, so it is a bit worrying me. Maybe I should not shrink logs.
What do you recommend for the administration of those logs?
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

There is no point shrinking the log file as it will need to grow again. The only time I would consider shrinking them is if there has been a one off large insert/update/delete executed.

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 2

You would want the log to be at the size it needs for regular operations. If you shrink it regularly you take away the space it needs regularly and in the process end up wasting resources that are used when the log file needs to grow again.

Sue

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Both of the replies above are absolutely right, but just to add that if you want to try to prevent the log files from growing too big in the first place, you could try increasing the frequency of your log backups. This will truncate the log more frequently, (i.e. mark space available for re-use), which in many cases means that the file does not need to grow so much.

jbeclapez SSC-Addicted Points: 405 More actions · Answer 4

Dear all,

Thanks for your help and time. It is appreciated.

I am a bit lost still as my logs are backed up daily but they keep on increasing. I was reading your point Beatrix when you write that it will truncate the logs... but it does not seem to do it then...

The LDF on the server is 5.2 giga now.

Here is the history of my backup logs :

D-4 : 580 Mega

D-3 : 3.8 Giga

D-2 : 5.5 Giga

D-1 : 13 Mega

What kind of pattern is that? yesterday the backup of the logs was 13 Mega but today the LDF is 5.2Giga. I am totally lost.

Please can you guide me?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Assuming you haven't shrunk the log file, its size will be the size of your biggest log backup so far (plus maybe a little bit more depending on what your autogrowth is set to).

If you're backing up your transaction log only daily, then you really ought to consider whether you need Full recovery mode at all - maybe Simple would be more appropriate. If you do require point-in-time recovery, a more common log backup frequency is every hour or fifteen minutes, or even more. That decision will be driven by the RPO (recovery point objective) - the most data your organisation can afford to lose in the event of a disaster. That's a business decision, not a technical one. As Beatrix noted, the more often you backup your log, the less space your log file is likely to need (although it still needs to be at least large enough for the largest single transaction, which will often be the rebuild of your largest index, assuming you're performing index maintenance).

As you can see, this stuff isn't simple. You might wish to download this free e-book and read through it at your leisure.

John

jbeclapez SSC-Addicted Points: 405 More actions · Answer 6

Thanks John for your help.

You pointed at me something I should have mentionned in my first post. Sorry : "That's a business decision, not a technical one"

We are OK to loose a day of work. So if you were me, what kind of plan would you do? I now do a daily FULL backup and then LOG daily too. I am happy with that but still have difficulty understanding the size of the logs. I never shrunk any log before as I was reading it is not good practice. Should I shrink the log once and then run the backup? It will not regrow that big as the backup is done and we do not use the DB a lot... (it is just that it is like this for 10 months now...)

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 7

If you don't need to do point in time recovery then you may be as well just keeping your daily full backup and putting the database into simple recovery.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Yes, set the database to Simple and shrink the log file to the size of the largest index in the database.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

jbeclapez wrote:

You pointed at me something I should have mentionned in my first post. Sorry : "That's a business decision, not a technical one"
We are OK to loose a day of work. So if you were me, what kind of plan would you do?

If it were me, my kind of plan would be to get people to understand that's probably a really bad business decision.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)