April 23, 2022 at 9:43 pm
Hello SQL Experts,
I saw some messages stating that transactional log is full. We have a database, we didn't enable the auto growth. We have Hallengren Index job running & some other jobs running at the same. How can we calculate the transactional log required for the Index maintenance job? How can we ruled out the which one causes the transactional log filling, if you have 3-4 scheduled activities running on that time?
Thanks,
Ramana
April 23, 2022 at 10:11 pm
Step 1 would be to stop using REORGANIZE unless you actually know what it does on each and every index you have and, to be brutally honest, most people don't. Testimony to that is simple... they keep using it for anything and everything that has logical fragmentation between 5 and 30% according to some supposed "Best Practices" that people have adopted that are NOT a best practice and were never meant to be a best practice. In a lot of cases, it actually perpetuates fragmentation of indexes.
In most cases, it's more important to make sure that your statistics are up to snuff especially on "ever-increasing" indexes.
I was "suckered" into the supposed "Best Practices" for more than 2 decades the same as everyone else simply due to some poor wording on the original documentation, which was (FINALLY) taken out of the MS documentation on the 2oth of April, 2021.
Doing index maintenance wrong is worse than doing none at all (I found out the hard way Monday, the 18th of January of 2016 with massive blocking that was the direct result of doing supposed "Best Practice" Index Maintenance the night before. I went for 4 years after that without doing any index maintenance. The only ill effect was some tables got larger than they should but I had the disk and memory to let that grand experiment to continue. The ONLY thing I did for those 4 years was keep statistics in great shape.
REORGANIZE is so bad that it turns out that it's the real reason why Random GUIDs fragment so badly even with a reduced Fill Factor. The thing that you also need to realize is that most of your non-clustered indexes are also highly randomized even with no GUIDs in sight.
To see the results of now more than 6 years of study on the subject, please see the 'tube at the following link with the understanding that everything I say about Random GUIDs and how I make them nearly fragment free quite literally for MONTHs at a time is also applicable to a great many of your other indexes.
Seriously... you need to watch the entire video because I explain a whole lot about fragmentation, the effects on the log file, and a ton more. In the mean time, STOP USING REORGANIZE FOR EVERYTHING! There are places where it's good to use but, in most cases, it's just gonna kill ya for performance and log file usage.
Here's the link... remember that it's not just about GUIDs.
https://www.youtube.com/watch?v=rvZwMNJxqVo
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2022 at 2:54 pm
If the transaction log had to grow during this process - you can find out what process initiated that growth using the default trace. If there was no auto growth involved - then you need to look at the output from all jobs that were running at that time. One or more of those jobs will have failed once the transaction log was full.
The size of the transaction log will need to be at least 1.5 times the size of the largest index in that database. It may need to be much larger than that depending on how often you run your transaction log backups. If you find that you don't have enough space available, then you need to request more space - no other option if you want to perform index maintenance.
I hope you are not shrinking the log and/or database - because all that does is cause the files to have to regrow back out during maintenance.
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
April 25, 2022 at 12:46 am
Ah... careful now... you can't rely on the default trace on a busy system for things like this. The lifetime expectancy of any row in our default trace is on the order of just a couple of minutes.
I have no great love for extended events but that's the tool to use to find this particular issue. I don't know of any way to do this using a server side profiler trace.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2022 at 2:42 pm
Are you doing frequent trans log backups ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply