January 7, 2020 at 1:35 pm
During our weekly index rebuilds (using Ola Hallengren's scripts), the log expands considerably. This isn't unexpected and there's plenty of space into which it can expand. I do have a task to look at minimising the growth but it's behind other things, including mitigating the growth.
The problem we actually have is further downstream. We have hand-rolled logshipping in place to keep some standby reporting databases in sync. It uses a set of PowerShell scripts to copy the log backups from the network share, restore them and then clean up. It usually works seamlessly but the weekend before last we had a disk space error on the reporting server. The ultimate cause of this was a 55gb log file that took 20 minutes to restore. This was greater than the 15 minute query timeout in the PoSh script and so it failed and the restore was rolled back. This meant that subsequent log files couldn't be restored, so they couldn't be deleted and so we ran out of space.
The quickest fix here was obviously to extend the timeout. After a bit of discussion, we thought that better than getting rid of it altogether. However, we're still getting the huge log files and we're concerned about hitting the new 30 minute timeout. Unfortunately changing the recovery model during the rebuilds isn't an option. We provide a managed service to a third party and what we do is 24 hour so there's always something happening on the servers.
What I'm thinking of doing next is to take the log backups more frequently during the index rebuilds. We currently backup every 15 minutes and I'm considering doing it every 5 minutes. My plan is to have a job running at 0355 on Sunday which runs sp_update_schedule to change the interval to 5 minutes and starts a WHILE loop to check whether the index rebuild is still running. When the rebuild job is finished, I'll run sp_update_schedule again to set it back to 15 minutes. Is this a wise idea?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 7, 2020 at 1:50 pm
Honestly, testing and experience is going to be the answer. Does it sound like a reasonable approach to me? Yes.
"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
January 7, 2020 at 1:58 pm
Thanks Grant. The next day or so is going to be spent testing things. I want to make sure I'm not planning something completely stupid before I spend any more time on it though.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 7, 2020 at 2:48 pm
I hear you. Thinking about it some more, while a bit of a pain, I don't see any way this is dangerous. Sure, you have a few more files, so any recovery action involves additional files. That may add a tiny amount to the recovery time, but not very much. You have more files to manage, again, slight pain, but not real pain, like timeouts. I think it seems reasonable. Although, as with ALL things, testing is your buddy.
"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
January 14, 2020 at 1:02 pm
You could also configure a SQL Server Agent Alert to run a transaction log backup job when more than a specified percentage of the log is used. I have done this for a similar situation to yours. Normally the log shipping transaction log backup runs every 15 minutes but during the index rebuild job it runs more often as the log file starts using more than 20%.
January 14, 2020 at 1:28 pm
You could also configure a SQL Server Agent Alert to run a transaction log backup job when more than a specified percentage of the log is used. I have done this for a similar situation to yours. Normally the log shipping transaction log backup runs every 15 minutes but during the index rebuild job it runs more often as the log file starts using more than 20%.
That's interesting.
Are there any big advantages to setting up the alert? I've already put the paperwork through to change the schedule and that took a bit of a sales pitch. I don't want to sound like I'm writing off your suggestion but I think it would need to be a much better approach to change direction now.
Of course, in future it's definitely something worth bearing in mind.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 14, 2020 at 1:41 pm
You wouldn't need to change the schedule and if the log file started to fill up beyond 20% (or whatever threshold you set) the tlog backup job would automatically run.
January 14, 2020 at 2:03 pm
I like the idea of it picking up automatically, I must say.
It's an out-of-the-box method too and I think the higher-ups will like that. It might be time for a bit of a discussion amongst the team.
We're about to start reviewing backup and restore policies and making sure things are standardised as far as possible. This could be something we add then.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 14, 2020 at 4:34 pm
I have my production databases set to be "exposed" to the possibility of a transaction log backup every 15 minutes, but it's stupid to do an actual T-Log backup if nothing has actually changed. When you do your laundry, do you wash the clean clothes as well as the dirty ones?
sys.databases has a column called "log_reuse_wait" for every database. If it contains the value of "Nothing", then don't do a backup. It's that simple.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 14, 2020 at 4:43 pm
p.s. And stop using REORGANIZE for your index maintenance. It doesn't work the way most people think (actually sets up indexes for perpetual fragmentation) and it's not the tame little kitty people think it is, especially when it comes to the burden on the log files.
For example, I did a test where I restored a database to a test box. I did a REORGANIZE on a 146GB Clustered Index that was "only" 12% fragmented. It took 1 hour and 21 minutes and caused the log file to explode to 227GB from 37.
I repeated the test using the same restore but did a REBUILD in the full recovery model. It also took 1:21 but the logfile only grew to 146GB.
If you don't have anything dependent on being in the FULL recovery model, you'll also be pleased to know that if you can temporarily shift to the BULK LOGGED recovery model (and, yeah... it take DIF backups before doing so), the index rebuilt in only 12 minutes and there was almost no log file growth.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2020 at 7:14 am
I have my production databases set to be "exposed" to the possibility of a transaction log backup every 15 minutes, but it's stupid to do an actual T-Log backup if nothing has actually changed. When you do your laundry, do you wash the clean clothes as well as the dirty ones?
sys.databases has a column called "log_reuse_wait" for every database. If it contains the value of "Nothing", then don't do a backup. It's that simple.
That makes total sense. However we're a truly 24 hour business and the database in question is one of our busiest. If nothing changed for more than a few tens of seconds I'd be surprised. If nothing changed for a couple of minutes, the users would be on the phone.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 15, 2020 at 7:34 am
p.s. And stop using REORGANIZE for your index maintenance. It doesn't work the way most people think (actually sets up indexes for perpetual fragmentation) and it's not the tame little kitty people think it is, especially when it comes to the burden on the log files.
That is worth knowing. I was surprised to see such high growth for a REORGANIZE. The numbers I was seeing were proportionally in the same ballpark as you got. It was a smaller index but but the changes were in similar ratios. Unfortunately, because I'd found a 'solution' further investigation was stopped from above.
How does that fit in to the Ola Hallengren scripts though? As I said above, we're looking at standardising the various tasks across the estate and part of the standardisation is to makes sure we're using them everywhere. If there's a case for saying don't, then I'm quite happy to make it. Apparently if you use 'the internet' you can find this sort of stuff out for yourself. What will they think of next?
With regard to the recovery model, I'd forgotten about Bulk-Logged. It's definitely something to consider. Another part of the standardisation is to review the backup policies and procedures so there's an opportunity to look at that.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 15, 2020 at 9:34 am
A couple of thoughts...
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 15, 2020 at 9:57 am
I somehow managed to miss having more than one schedule for a job. All the research I did was about varying the schedule rather than having more than one. The scheduling did seem less flexible than it could have been. There was an admitted failure of the Google-fu though.
The thinking behind having a separate job was to keep the maintenance jobs the same across the servers. We only needed to vary the schedule on of the servers so we decided against having one job that was different from the others.
There's quite a bit to think about here. But as I said, I've got a solution so there's no need to do any more investigation...
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 15, 2020 at 11:57 am
p.s. And stop using REORGANIZE for your index maintenance.
With the noted exception of columnstore indexes, but only when working in 2016 SP1 or greater.
"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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply