Is Shrinking Log Files Good or Bad?

Question

Is Shrinking Log Files Good or Bad?

scantoria

SSC-Addicted

Points: 498
More actions
November 10, 2014 at 9:59 am

#397624

We are faced with shortage of drive space and the majority of the space is taken up by very large log files. I have log files as large as 15GB. We have been shrinking the log files only when needed.
Question: Is there harm in shrinking the log files once a week?
USE UserDB;
GO
ALTER DATABASE UserDB
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (UserDB_Log, 1);
GO
ALTER DATABASE UserDB
SET RECOVERY FULL;
GO

Viewing 13 posts - 1 through 12 (of 12 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Please don't.

If you switch to SIMPLE recovery, you'll lose the ability to perform point-in-time recovery. You can do it temporarily, for instance during ETL loads, but right after switching back to FULL recovery you need a full backup to initialize the log chain.

If you want a smaller transaction log file, take more frequent log backups.

-- Gianluca Sartori

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 2

How often are you running full/diff backups compared to transaction log backups? That would determine how long you need to hang onto your old tlog backups before you can delete them.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Yes, LOTS of harm. Among the issues are VLF problems (search Kimberly Tripp VLF), stalls while you grow back out, you probably have default 10% growth increment which makes this even worse, OS disk frag too.

15GB is a SMALL file these days!! What is database size?

Have you tried to track tlog usage back to the source? Perhaps things are just done poorly in the app/etl process, etc.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

scantoria SSC-Addicted Points: 498 More actions · Answer 4

Thank you so much. I've over looked the point-in-time restore ability. We are currently performing transaction log backups every 15 minutes. I wonder if we are over looking other best practice steps.

Thank you for taking the time.

scantoria SSC-Addicted Points: 498 More actions · Answer 5

This is interesting. Currently, our Diff runs every Wednesday and Full every Sunday.

Base on our current scenario, what is the formula for how long we should keep our log file backups?

scantoria SSC-Addicted Points: 498 More actions · Answer 6

Thanks. I am researching "Kimberly Tripp VLF" now. Yes our growth is defaulted to 10% growth and I do not see a scheduled disk defrag process in place.

I will track usage back to the source. I'm sure you are correct and hit the nail in the head with poorly done app/etl processes.

Thanks again for taking the time.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Note that FAR worse than the 10% log file growth is the 1MB default data file growth increment. If you have that, well, you have a BUTT TON of little IO-performance-robbing 1MB file chunks spread all over your storage media! :w00t:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

I'd like to know where so many people got the idea that the database should be set to Simple Recovery Mode first.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

Alvin Ramard (11/10/2014)
I'd like to know where so many people got the idea that the database should be set to Simple Recovery Mode first.

Bad habits from DTS when I used to work in SQL 2k before I understood recovery properly and reading recommendations back then. I've learned better since then but a lot of those bad practices are still out there, percolating around the net. It's also one of the first things you run into when you look at log files is that recovery mode affects the size and you should decide what to do with it.

At first glance, set it to simple, do your work, switch it back sounds like a reasonable idea when you just don't know the impact.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 10

I think it goes back to before backup with truncate only was depcrecated. Switching to simple then shrinking the log file then switching back has pretty much the same consequences but requires a few more steps to make sure you really really want to do that.

scantoria SSC-Addicted Points: 498 More actions · Answer 11

Thank you for everyone's input. Y'all are very helpful.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 12

scantoria (11/10/2014)
This is interesting. Currently, our Diff runs every Wednesday and Full every Sunday.

With once a week fulls i'd prefer more regular diffs

scantoria (11/10/2014)
Base on our current scenario, what is the formula for how long we should keep our log file backups?

you should keep the logs back to at least the last full backup, to give your self the most flexible restore routes.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉