Identify which index's rebuild operation is causing the log file to grow

Question

We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow. I have log file growth event alerts enabled, it always happens at the same time and continues for a few hours and stops later, so quite sure it is the same index all the time. I'm not sure whether it is a clustered or non-clustered index but suspect a clustered index on a large table, of which there are a couple, is causing this. After the maintenance job completes, I shrink the log and it stays stable for the whole week. How can I identify which index is causing the file to grow? Prefer to avoid logging solutions such as extended events or audit.

Answer 1

We have a database, in full recovery model, which goes under index rebuild maintenance once a week. There is an index which is causing the log file to grow.

Yeah, when you rebuild items it's going to be pretty terrible. It's why it shouldn't be done unless absolutely necessary, not something to setup to run weekly. You can read my opinion on it, but you're living the issues.

After the maintenance job completes, I shrink the log and it stays stable for the whole week.

Why shrink after? If the log is going to blow out each week from doing random index rebuilds for seemingly no reason, why even care about the size of the log? Doesn't seem to make sense.

Prefer to avoid logging solutions such as extended events or audit.

This is also odd, why not? If you’re blowing out the log and using a bunch of cpu and disk to do random index rebuilds, why would it matter implementing logging?

How can I identify which index is causing the file to grow?

Ask the log what it has in it:

SELECT COUNT(*) AS [Records]
        , SUM([Log Record Length]) AS [TotalBytes]
        , AllocUnitName AS [Name]
        , AllocUnitId AS [ID]
FROM sys.fn_dblog(null, null)
WHERE AllocUnitName IS NOT NULL
    AND AllocUnitId IS NOT NULL
GROUP BY AllocUnitId, AllocUnitName
ORDER BY TotalBytes DESC

I doubt log_reuse_description will be of help as it will most likely show active transaction.

If you have an active transaction then it's going to grow, this is not a mystery. Asking which index rebuild caused it to grow is moot, it could be any given there is an active transaction holding up the growth, so I'd argue it'd be extremely helpful.

It could be the rebuild that is active or it could be something else, we don't know and the statement you made about next week the log will double makes me believe it's not the rebuild.

Thank you but I don't think this is a solid answer. It does not pinpoint the index causing the file to grow.

Then you're not understanding how SQL Server works or how log files work. Not trying to be mean here, but you're arguing an XY problem. If the log reuse reason is ACTIVE_TRANSACTION then the reason it's growing is because it's hitting the end and can't reuse space due to an active transaction, which has nothing to do with the index rebuild running as that rebuild is generic workload which could be anything. Thus, finding the index being rebuilt (which, again, doing so for no other reason than to do it based on some fragmentation level) which "caused" the log to grow is an effort in futility as it'll always grow since the active transaction which is the root of the issue has not been addressed. Hence your XY problem.

"you shouldn't be rebuilding indexes regularly": This is totally out of scope of the question. I am not trying to solve an issue on the frequency of index rebuilds nor did I ask for it.

I mean it's a question on index rebuilds and it's definitely in scope. Just because you don't want to hear it doesn't make it untrue or unhelpful. If you're going to ask random people on the internet for help and then immediately dismiss the help because you don't like it - that's on you. It doesn't make the help incorrect or out of scope.

If you asked an electrical question and someone said don't do it because it's grounded wrong and you'll electrocute yourself, I would hope you wouldn't say you didn't ask that question and it's out of scope. Same thing applies here.

Read carefully and you will see no contradictions. To reiterate, I need to identify after the fact by some means of logging, alerts etc. which index caused the file to grow. Hope this makes it clearer.

You do contradict yourself or, at a minimum, show that it's an XY problem depending on how you want to look at it. You state that you want to know the index rebuild causing the log to grow and then also state that the log reuse description is active transaction. Since the log is growing due to an active transaction then that would have nothing to do with which index is making it grow but rather the active transaction should be investigated. Thus asking someone to stick on point to identify indexes is moot and advice for not blanket rebuilding is helpful, though still not the root cause.

I'm just a guy on the internet trying to help you for free. If you don't want it, that's fine, but I'm not going to tell you what you want to hear or placate you like some manager in a support org.