October 17, 2014 at 8:47 am
Hi Everyone,
Long time lurker, first time poster. I have a simple question that has been keeping me up at night.
It's my understanding the log files are circular, in that once the end of the log is reached, activity will begin at the beginning of the log and overwrite what's in there currently.
If that's the case, why let tlog files autogrow? If I'm familiar with the write activity to the db, why not set a fixed size a few times larger than the anticipated growth of the db and leave it?
I would imagine that setting autogrowth would mean that the log files would always grow and regardless of tlog backups I would need to shrink the file (not the database!) at some point in time.
Would love to hear some feedback from the experts. Thanks!
October 17, 2014 at 8:56 am
ap-mode (10/17/2014)
Hi Everyone,Long time lurker, first time poster. I have a simple question that has been keeping me up at night.
It's my understanding the log files are circular, in that once the end of the log is reached, activity will begin at the beginning of the log and overwrite what's in there currently.
If that's the case, why let tlog files autogrow? If I'm familiar with the write activity to the db, why not set a fixed size a few times larger than the anticipated growth of the db and leave it?
I would imagine that setting autogrowth would mean that the log files would always grow and regardless of tlog backups I would need to shrink the file (not the database!) at some point in time.
Would love to hear some feedback from the experts. Thanks!
You're confusing WINDOWS log behavior with SQL SERVER log behavior.
SQL Server transaction logs are nothing like Window server logs. They don't recycle at all like you describe. It's a rather large topic, so you really should brush up on what they really are:
http://qa.sqlservercentral.com/articles/Stairway+Series/73775/[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2014 at 8:56 am
Safety net.
Autogrow allows for the log to not fill up if something happens to prevent log reuse. Something like an active transaction, unusually large data modification (eg archiving), failed log backups, replication problems, etc. If something like that happened and autogrow isn't enabled and the log can't be reused then the log can fill and prevent any other data modification.
If the log is allowed to autogrow and there's space on the drive, then it can grow if something unexpected happens.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 9:12 am
I personally don't like to leave the logs with autogrow on. The basic reason is because if you don't have each log in a different partition/drive then one log can grow out of control and affect all other databases. Setting the max value is basically the same as placing the log files in different partitions.
Usually, an operation that makes the log grow out of control should be killed. For that reason I prefer to get a notice if a problem is happening and have the possible affected databases reduced only to that one with the unusual transaction running.
October 17, 2014 at 9:23 am
I like to have the safety net. Allow for the log to grow just in case so it doesn't bring down an application.
If the log file does grow, you can have a monitoring solution in place to alert you in that event. Then you can address the problem and find the query that caused the growth to occur. Better to fix the offending query and limit downtime imho.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2014 at 9:37 am
Autogrowth allows for a safety net, but it is also good to set up alerts to raise an alarm when the log grows over a threshold. This would mean you wont end up in a situation where autogrowth fills up the disk and you potentially have no room to perform a log backup, so, under pressure to get the application running again, you end up resorting to changing the recovery model to simple and shrinking the log to get you out of a hole!! 😉
Having an alert allows you to investigate and address the reason for the unusual log growth whilst still allowing for the database to be in a usable state.
October 17, 2014 at 9:39 am
I made a mistake in my first post that can give the idea that I'm against autogrow. That's not the case. While I'm not a big fan, what I'm against is to have no max size.
@SSCoach
Your idea is totally understandable, but the problem arises when you have users (like it happened to me) that, accordingly with management, need to have write access to production and that decides it is a good idea to delete more than 100 million records in a single transaction. The problem here is that the grow of the log is so fast that the DBA doesn't have enough time to prevent the disaster. Now, the thing is, when you have a max size in your log, only the affected database is going to suffer the effects, if you don't have a max size, all databases that you might have in the same volume are going to be affected in the same way.
October 17, 2014 at 9:44 am
homalves (10/17/2014)
I made a mistake in my first post that can give the idea that I'm against autogrow. That's not the case. While I'm not a big fan, what I'm against is to have no max size.@SSCoach
Your idea is totally understandable, but the problem arises when you have users (like it happened to me) that, accordingly with management, need to have write access to production and that decides it is a good idea to delete more than 100 million records in a single transaction. The problem here is that the grow of the log is so fast that the DBA doesn't have enough time to prevent the disaster. Now, the thing is, when you have a max size in your log, only the affected database is going to suffer the effects, if you don't have a max size, all databases that you might have in the same volume are going to be affected in the same way.
Surely, all the other databases would only be affected if they had to autogrow at exactly the same time as when the disk was filled by the database with the long transaction?
I understand what you are saying though, but if all databases have their logs presized to their correct size initially, then no logs should grow during normal running, unless something like the above scenario happens and perhaps over time as the database get busier or larger.
October 17, 2014 at 9:57 am
Maddave (10/17/2014)
Surely, all the other databases would only be affected if they had to autogrow at exactly the same time as when the disk was filled by the database with the long transaction?
Correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 10:56 am
Thanks everyone. Very enlightening discussion.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply