November 19, 2018 at 12:35 pm
Hi all,
Recently, we've had an issue with disk space on the server that houses our .mdf and .ldf files. As it turns out, the log files were becoming rather large because of an automated reindexing job that runs every sunday using dbo.IndexOptimize. This was set up long ago by my predecessor, and it would seem like it'd be a good thing to keep going as the application is slow as F*** as is (and there's NOTHING we can do about that - seriously) and I'd rather not have DB performance issues exacerbate that. Granted, maybe we're shooting ourselves in the foot by trying to avoid it.
Anyway, the reindexing job seems to be the reason why the transaction logs are growing so much (most DBs are set to grow unrestricted at 10% or 4 MB and all are on a simple recovery model) and we need that disk space. So, the 'obvious' solution would be to set up another job to shrink the log files after the reindexing job.... but that doesn't seem like a best practice, or even a good or OK practice.
I am trying to do my own research regarding more effective strategies, but I'd like to hear your thoughts on this. Any suggestions? Am I wrong that it would be bad practice to set up a shrink job?
Thanks!
November 19, 2018 at 12:48 pm
No, it's not necessarily a good idea. In fact, stop it first, then see what's it doing. If it's using generic rules, like 10% and 30%, then don't restart the job at all until you've reviewed the indexes and decided which ones really need optimized, if any.
The most likely place to get fast, effective tuning is to review the index usage stats and missing index stats, focusing on the clustered indexes. Often determining and implementing the best clustered indexes reduces the "problem" queries dramatically so you can concentrate on the really serious ones.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 26, 2018 at 8:58 am
Okay, well the reindexing job was not causing the issue. The job only runs once a week yet we keep experiencing the same autogrowth (at night at ~2330) even on days that the job does not run. Is there anyway to track this down besides starting a trace around that time and letting it run all night?
November 26, 2018 at 9:14 am
Might be worth trying querying the plan cache. Have a look at the query on this page, and put total_logical_writes in the query. Then, in the result set, look for queries with a last_execution_time of around 23:30. You might find something useful. There could be reasons why this doesn't work for you - perhaps it's a slightly different query every day and you have optimize for ad hoc workloads turned on. Or whatever code is causing this uses WITH RECOMPILE. Perhaps your plan cache is very small and queries don't stay cached for very long. Maybe the process runs as a series of small queries within a single transaction. Whatever happens, let us know how you get on!
John
November 26, 2018 at 10:02 am
scarr030 - Monday, November 26, 2018 8:58 AMOkay, well the reindexing job was not causing the issue. The job only runs once a week yet we keep experiencing the same autogrowth (at night at ~2330) even on days that the job does not run. Is there anyway to track this down besides starting a trace around that time and letting it run all night?
Actually, it probably is. Most stuff setup for index maintenance follows the supposed "best practice" of not doing anything up to 10% logical fragementation, REORGANIZEing between 10 and 30% fragmentation, and rebuilding only after exceeding 30% fragmentation.
REORGANIZE is fully logged and can easily cause the log file to grow to almost 200% of what you're largest index is.
I recently had a 146GB Clustered Index that reached 12% logical fragmentation and did a REORGANIZE on it to see what would happen. The only substantial thing that happened was the log file exploded. It went from a mere 37GB to 227GB and took 1 hour and 21 minutes to execute. In the BULK_LOGGED recovery model, the rebuild of a copy of the table on a "clone" of the box only took 12 minutes and the log file experienced no growth beyond the original 37GB.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2018 at 10:42 am
Jeff Moden - Monday, November 26, 2018 10:02 AMActually, it probably is. Most stuff setup for index maintenance follows the supposed "best practice" of not doing anything up to 10% logical fragementation, REORGANIZEing between 10 and 30% fragmentation, and rebuilding only after exceeding 30% fragmentation.
REORGANIZE is fully logged and can easily cause the log file to grow to almost 200% of what you're largest index is.
I recently had a 146GB Clustered Index that reached 12% logical fragmentation and did a REORGANIZE on it to see what would happen. The only substantial thing that happened was the log file exploded. It went from a mere 37GB to 227GB and took 1 hour and 21 minutes to execute. In the BULK_LOGGED recovery model, the rebuild of a copy of the table on a "clone" of the box only took 12 minutes and the log file experienced no growth beyond the original 37GB.
Maybe I'm misunderstanding your post, but the reindexing job wasn't running, yet the logs still grew, so that makes me skeptical that it's the job. Is that not what you mean?
November 26, 2018 at 11:22 am
scarr030 - Monday, November 26, 2018 8:58 AMOkay, well the reindexing job was not causing the issue. The job only runs once a week yet we keep experiencing the same autogrowth (at night at ~2330) even on days that the job does not run. Is there anyway to track this down besides starting a trace around that time and letting it run all night?
Check SQL Agent jobs for a job that runs every night that rebuilds tables. If nothing there, check for any jobs running over a linked server.
Also check for any SSIS jobs on other systems that populate a table in this instance.
Ifyou still cannot identify the process then a trace may be needed.
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
November 26, 2018 at 1:01 pm
scarr030 - Monday, November 26, 2018 10:42 AMMaybe I'm misunderstanding your post, but the reindexing job wasn't running, yet the logs still grew, so that makes me skeptical that it's the job. Is that not what you mean?
Ah... I misread what you posted. My apologies (although the information is still accurate about index maintenance... just not for the times you're having problems).
Getting back to your problem, I did a quick check on Yabingooglehoo to see if anyone came up with something new and found nothing. It seems like everyone wants to use profile, extended events, or you simply need to get lucky and be ready to run some code as the growth is happening. In other words, all the methods that I could find in 20 minutes have a bit of a suck factor to them. 😉
What you might want to try is pick your favorite method for seeing code that's currently running and create a database alert for log file growth to run the proc and capture the information every time the log file grows.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2018 at 8:02 am
Jeff Moden - Monday, November 26, 2018 1:01 PMAh... I misread what you posted. My apologies (although the information is still accurate about index maintenance... just not for the times you're having problems).
Getting back to your problem, I did a quick check on Yabingooglehoo to see if anyone came up with something new and found nothing. It seems like everyone wants to use profile, extended events, or you simply need to get lucky and be ready to run some code as the growth is happening. In other words, all the methods that I could find in 20 minutes have a bit of a suck factor to them. 😉
What you might want to try is pick your favorite method for seeing code that's currently running and create a database alert for log file growth to run the proc and capture the information every time the log file grows.
No worries! I'm always happy to receive helpful advice, even if it's not immediately pertinent.
As it so happens, there was a scheduled task that runs a process every night to calculate a bunch of stuff for reporting through the application (which we don't have the source code for). The application developers didn't mention this caveat, hence why I was running around with my hair on fire. And yes, only way to find that one out was through a trace or by sheer luck 🙂
Thanks for the help, everyone.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply