October 13, 2009 at 2:02 pm
I have ran into an interesting problem that I am at a loss for an answer. Is there any reason to NOT have a big (and I'm talking HUGE) log file? Is there a performance hit that is taken for having a log file that is too big?
I.E.
I support a production instance of SQL Server that houses our emailing database, Mimosa, and the application administrator setup the database as well and he sized the log file to be 100GB. I have kept an eye on the "used" vs. "allocated" sizes and "used" is only 500MB, 1GB at the highest level.
So, again, my question, is it "too" big? BTW, no other files are on the SAN drive that this log file resides on. I know I see all kinds of threads about shrinking a log and growing it as needed and keeping it at a lower size being bad and all, but I've never seen anything to say that keeping it at a large size is bad. Wondering what everyone thought...
Thanks in advance.
October 13, 2009 at 3:00 pm
The only "bad" that I can think of is that if you need to restore a backup of this database to another location, then that location would also need to accomodate the 100GB log file. You typically want to size your log file to accomodate the largest volume of transactions that won't be cleared until the next log backup completes. This varies, but it's usually related to batch processing and / or database maintenance activities.
You might also want to run DBCC LOGINFO to see how many VLF's are contained in that one file.
Look at this: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
and this: http://www.sqlskills.com/blogs/kimberly/post/8-Steps-to-better-Transaction-Log-throughput.aspx
For more detailed information.
October 13, 2009 at 3:20 pm
There really is nothing wrong with having a very large transaction log - but, it really depends on how and why it got that way in the first place.
If the log file grew to that size because you were not performing transaction log backups - then you probably can shrink it as a one time operation down to the normal usage between your now implemented transaction log backups.
If the log file grew to that size because you have a monthly/quarterly/yearly load that needs that much log space to load the data - then I would just leave it that way.
If you have been monitoring it long enough to be confident that you will never need that much space again, I would shrink it to a reasonable size and just keep monitoring. If it does grow again, then you can identify the process that needs the additional log space and determine if that process needs to be updated.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 14, 2009 at 7:50 am
However large the log file is, it is. There's nothing wrong with a large file. But... it does raise the concern that you're logging operations that should not be, that the operations occurring in the system may be inefficient, that you might not be backing up the log quite frequently enough. I'd be sure to answer as many of these questions as possible before I just said to myself, oh well, it's just a big file.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 14, 2009 at 8:05 am
To clarify, this log file was sized this way before I became in charge of it. It did not grow to this size on it's own. I suggested shrinking it and the application manager told me not to do this. So, for whatever reason, he has kept it this large. As i said, I have never run into a situation where anyone wanted an unnecessarially large file; usually everything is done to keep it of reasonable size. And I was asked if there was any reason to shrink it if this log was the only thing on this drive. I could not think of anything. But the restore issue is a very good point.
Thank you.
October 14, 2009 at 9:26 am
Paul S. Huebner (10/14/2009)
To clarify, this log file was sized this way before I became in charge of it. It did not grow to this size on it's own. I suggested shrinking it and the application manager told me not to do this. So, for whatever reason, he has kept it this large. As i said, I have never run into a situation where anyone wanted an unnecessarially large file; usually everything is done to keep it of reasonable size. And I was asked if there was any reason to shrink it if this log was the only thing on this drive. I could not think of anything. But the restore issue is a very good point.Thank you.
As a guess, I would say that there was a process at some time that needed that large of a log file and they anticipate that situation to occur again.
Since that is the only thing on that drive - no problem with keeping it that size.
And yes, if you have to restore from backups - you will need the same amount of storage available.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 15, 2009 at 7:40 am
I have a piggy back question on the large log files. If something did happen to the primary server and a restore has to happen, wouldn't it take a long time to restore to a point in time with a log file 100GB even though only 500MB is being used? Once we had a log file grow extremely large - a rare occasion due to issues - and then when we tried to failover to a mirror and back it took a long time for our database to become synchronized and come back online.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply