May 28, 2010 at 6:20 am
My personal view on all this is that transaction log auto-growths are there only as a last resort.
It makes sense to me to over-size the transaction log initially (while being careful about virtual log file size). If subsequent monitoring makes it clear that it is (massively) over-sized and if you really need those few GB, it might make sense to do a log shrink in a maintenance window at some point (again with care over VLFs).
I would regard a 9002 error in production as the result of poor planning or monitoring (or both). The DBA team should be alerted to unusual log growth well before it could cause that situation - whatever the cause.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 6:55 am
James_DBA (3/17/2010)
To start off with an improperly sized log file such as that of which you are describing can actually hurt your performance in a magnitude that is unacceptable. You may find that resizing that TLog will increase your performance for that database by tenfold or better!
A too-small log file can certainly hurt performance badly if auto-growths are frequent enough.
I can't think of a way for a (properly created) "too large" transaction log to cause a performance problem.
If the 192GB log file is on a large dedicated disk (or even a 192GB one!), if it was properly created (to optimise virtual log file size), and if no more than a few tens of GB (say) are ever used, this seems like a sensible set up to me. I certainly can't see any justification for saying "hurt your performance in a magnitude that is unacceptable".
In a discussion on one of my other articles I actually provided some concrete testing that you can reproduce to see (as well as show the vendor) how an improperly sized log file affects your system. Go to: http://www.sqlservercentral.com/Forums/FindPost817363.aspx to see the testing and results; as well as the post I added immediately afterwards on how to create the testing yourself.
I read that in detail and, while quite interesting, I must say I did not find it at all persuasive.
The differences recorded are relatively small (a few percent) and there are all sorts of factors in play which make the conclusions unsound.
You indicate in your comments that you are aware of some of them (log on the same drive as data for example).
Once TLogs are getting regularly backed up, or the model is set to SIMPLe then you want to measure the TLog capacity before each backup or on a regular basis to deterimine how much space is actually required. I'd then resize it to that plus 10-20% for spikes.
Why 10-20% for spikes? Depending on what is logged and when, natural growth of the business might very quickly reduce that 'buffer' to nothing. Why not 100% or even more? Too much depends on the circumstances to say.
Also show them the results of timing of queries if you can get some. 1 set of times using their 192 GB of space and 1 of yours with say 4 GB of TLog space. You'll blow them away in time, and in how silly they are for not figuring out this gaping black whole for time!
I see no reason to expect a 4GB log to out-perform a 192GB log.
And, let us say the log is on a dedicated 192GB disk and you size the log to 4GB with auto-grow.
A colleague, Bob, decides he needs some temporary space for some imports he's pre-processing. Oh look! Drive L has loads of space! Not only might your next auto-growth fail for lack of space, the random I/O from Bob's work is going to hurt log latency - a lot.
I can't even imagine what the backup time is for that TLog
Only the number and size of log records that require backing up affects the backup time.
If you have 400MB of log used, it will take the same time to backup regardless of whether the total log file size is 400MB or 400GB.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 7:21 am
Kevin Landry (5/28/2010)
I have also considered the Bulk-Logged option, but I do not see how switching to Simple is any more dangerous than using the Bulk-Logged option?Switching to any other recovery model (including Bulk) from FULL breaks the point-in-time recovery sequence. So using Bulk and Simple are equally "dangerous" in this switching scenario as both require backups before and after the switch.
The BULK_LOGGED recovery model provides greater protection and is more compatible with other engine features (like log shipping).
One the index rebuild is complete, full point-in-time recovery is restored once the second log backup is complete. Although this log backup will include an image of every page changed by a minimally-logged operation, it is still typically a fast operation.
By contrast, switching to SIMPLE to perform the index operations requires at least a differential backup. Depending on the number of extents that have changed since the last full backup, this operation may take quite some time (it might even be quicker to perform a full database backup).
It is also possible that damage to a database physical file (in an area unaffected by the minimally-logged operations) might prevent a successful differential or full backup from completing, where a log backup (only possible under BULK_LOGGED) would succeed. Recovering a damaged page might well be possible without taking the database off-line (Enterprise only) whereas the SIMPLE scenario would require a full restore sequence in this circumstance.
Switching to SIMPLE exposes you to more risk, for a longer time, and is less compatible with other features.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 7:23 am
Did I understand correctly that if you went to bulk logged ( or simple?) before reindexing, you could then resume full recovery and log backups afterwards without doing anything else? Or was it: switch to bulk logged, reindex, resume full recovery, take differential and resume log backups?
The main concern for us is reindexing following major data loads ( conversions), not the normal weekly reindexing.
One other oddity in our environment is our Systems Team has taken over all backups/restores using the Commvault idata agents. Since they really don't know sql, they use it in "wizard" mode. We saw that switching to simple recovery during the "reindex after conversion" caused Commvault to launch a differential backup on it's own -- it assumed log backups were no longer desired. We really didn't want that backup running at that point in our conversion weekend.
May 28, 2010 at 7:36 am
Indianrock (5/28/2010)
Did I understand correctly that if you went to bulk logged ( or simple?) before reindexing, you could then resume full recovery and log backups afterwards without doing anything else?
Yes! Nice and easy. See these Books Online links:
Backup Under the Bulk-Logged Recovery Model
Considerations for Switching from the Full or Bulk-Logged Recovery Model
...and related pages.
The main concern for us is reindexing following major data loads (conversions), not the normal weekly re-indexing.
The BULK_LOGGED recovery model was designed to optimise large loads and the related index creations.
Normal re-indexing can also benefit, as I hope I have indicated.
One other oddity in our environment is our Systems Team has taken over all backups/restores using the Commvault idata agents. Since they really don't know sql, they use it in "wizard" mode. We saw that switching to simple recovery during the "reindex after conversion" caused Commvault to launch a differential backup on it's own -- it assumed log backups were no longer desired. We really didn't want that backup running at that point in our conversion weekend.
It seems to be doing the sensible thing - you cannot take a log backup after switching to SIMPLE recovery (without taking a full or possibly a differential backup first).
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 7:43 am
It would be pretty easy for me to add a switch to bulk-logged before reindexing every Saturday night and switch back to full-recovery afterwards, but I have no idea what commvault might do in that scenario. The differential it started when we manually went to simple recovery for reindexing during a conversion weekend, was unwanted. Our playbook called for a full backup and resumption of log backups at the end of the conversion.
We do have the idata agent installed on a dev box so I guess we'll have to test that and see what it decides to do. Moving responsibility for backups/restores/DR to people who refuse to even open Management Studio was done against my advice. 🙂
May 28, 2010 at 7:56 am
Indianrock (5/28/2010)
We do have the idata agent installed on a dev box so I guess we'll have to test that and see what it decides to do.
Yes definitely worth testing.
Moving responsibility for backups/restores/DR to people who refuse to even open Management Studio was done against my advice. 🙂
I am not surprised! There seems to be a small but noticeable trend in that direction, which worries me no end.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 28, 2010 at 7:57 am
James_DBA (1/5/2009)
Yes, as mentioned in my article, there is no performance benefit to multiple log files. It seems the only benefit is file management for disk space and other administrative tasks where you would want the log file in a smaller size (i.e. disk mirroring, or limited hard drive space).
I wonder how multiple smaller logs could improve manageability in a mirroring environment?
May 28, 2010 at 8:48 am
Kevin Landry (5/28/2010)
Hi Paul,I have also considered the Bulk-Logged option, but I do not see how switching to Simple is any more dangerous than using the Bulk-Logged option?
Switching to ANY other recovery model (including Bulk) from FULL breaks the point-in-time recovery sequence. So using Bulk and Simple are equally "dangerous" in this switching scenario as both require backups before and after the switch.
The problem with switching to SIMPLE recovery model is that you break the log chain. Once the log chain is broken, you cannot restore to a point in time past that break. To start transaction log backups again, you have to perform a full or differential backup to reestablish the log chain.
Switching to bulk-logged does not break the log chain and you don't have to perform a full or differential to continue backing up the transaction log.
Let's say we backup the database every morning at 1am. It takes 2 hours to backup the database - and you have a process in your maintenance plan that rebuilds indexes. In that step, you switch to simple recovery, rebuild the indexes, switch back to full - and then run your backup. Let's also say your rebuild process takes 1 hour, so the plan finishes at 4am every morning.
At 9am the following day your system crashes. You have to restore from backups - so you grab the current backup and start to restore and find out that the backup is corrupted. So, you try to go to the previous backup - it's good and restores, so then you start applying the transaction logs to bring you current. You try to apply the transaction log backup that was taken at 4am (right after your current backup) and it fails, because you have broken the log chain.
You now have lost 5 hours of data - and that could cripple your business.
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
May 28, 2010 at 9:16 am
Jeffrey is right. Don't play with simple mode, unless you are going to run a full backup first, AND make sure you have a second copy off the machine before you make a switch.
Mr. Murphy tends to strike when you least expect it. Don't leave a long window for him to do that in by switching to simple mode.
May 28, 2010 at 10:03 am
Don't know why this popped up first with today's date. I enjoyed, but thought I'd comment.
I'd like to see more realistic numbers for a production database. Autogrowth at 1 MB can cause poor performance on a database with even moderate activity.
Also, I like to set the log size to as large as I expect it to grow between backups.
May 28, 2010 at 10:10 am
With a pad. Set the log as large as it will grow with some pad in there in case the data activity is different.
May 28, 2010 at 10:11 am
Jeffrey Williams-493691 (5/28/2010)
Kevin Landry (5/28/2010)
Hi Paul,I have also considered the Bulk-Logged option, but I do not see how switching to Simple is any more dangerous than using the Bulk-Logged option?
Switching to ANY other recovery model (including Bulk) from FULL breaks the point-in-time recovery sequence. So using Bulk and Simple are equally "dangerous" in this switching scenario as both require backups before and after the switch.
The problem with switching to SIMPLE recovery model is that you break the log chain. Once the log chain is broken, you cannot restore to a point in time past that break. To start transaction log backups again, you have to perform a full or differential backup to reestablish the log chain.
Switching to bulk-logged does not break the log chain and you don't have to perform a full or differential to continue backing up the transaction log.
Let's say we backup the database every morning at 1am. It takes 2 hours to backup the database - and you have a process in your maintenance plan that rebuilds indexes. In that step, you switch to simple recovery, rebuild the indexes, switch back to full - and then run your backup. Let's also say your rebuild process takes 1 hour, so the plan finishes at 4am every morning.
At 9am the following day your system crashes. You have to restore from backups - so you grab the current backup and start to restore and find out that the backup is corrupted. So, you try to go to the previous backup - it's good and restores, so then you start applying the transaction logs to bring you current. You try to apply the transaction log backup that was taken at 4am (right after your current backup) and it fails, because you have broken the log chain.
You now have lost 5 hours of data - and that could cripple your business.
I actually take a FULL before and directly after switching as my backups do not take long so my scenario is not as dire as the one you describe, but I see your point and will switch to Bulk rather than Simple. Thanks everyone for the education.
May 28, 2010 at 10:15 am
Thanks for the article.
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
May 28, 2010 at 10:59 am
Hi. What can someone do if they don't want a big transaction log? Our db is 7GB and the log is 10GB. There's no way we're going to rollback any further than 60 minutes into the past so 95% of the log file is a waste of space, especially when it comes to taking a file backup each night.
Everytime I truncate the log down to 1GB it jumps back to 10GB the next day (after the scheduled db backup). The log has 92% space used.
The reason we'd never use the log to rollback is because our app is a website which has 1000's of users so we'd never want to undo the good transactions for the sake of 1 user who may have screwed up their data. I've followed various tutorials on truncation and log backups but ours always bloats up to 10GB. Any help would be very useful. Thanks.
Gary.
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply