Does a fragmented database cause the transaction log to grow faster?

Question

Hoping to get some clarity here as I've reached the limits of my SQL knowledge. The company I work for sells products storing data in Microsoft SQL Server databases, but our focus isn't really on the storage and management of this data; we normally leave that for on-site IT to manage. In this case though, IT don't really do anything and now I have to look at it when I'm more of an application specialist.

Our customer ran out of space on the disk they use to store their transaction log. This was resolved by giving more disk space to the drive; however, they're convinced that "the log used to be way smaller and now it's growing much faster than it used to".

I understand that the speed at which the transaction log grows is determined the by number of transactions carried out, if any index rebuilds/reorganisations happen etc., but as I mentioned, this customer does nothing to manage their database, so I would expect this speed to just reflect how much data they insert but not much else.

To get to the point, after checking the indexes, I noticed that nearly all of them are over 90% fragmented. I was wondering if this smack to performance could create more logging than usual — or would it just affect the speed at which any queries/transactions execute?

I tried viewing the SQL Server logs, but I just run out of memory each time become of the insane amount of errors saying:

The transaction log for database "Name of Database" is full due to "LOG_BACKUP"

This stops me from seeing the transaction leading up to this error.

Note

• I can only shrink the database by 9MB (database is around 580GB)
• Recovery Model = Full

From my own research, I think what I'm seeing is quite normal and the DB just needs better maintenance in general; however, in my industry (industrial manufacturing), databases are seen as a mystical entity no one wants to touch, so I'm trying to find an explanation for what they believe (if any).

We have a service and application that adds data to the database over time (collecting data etc.). The server admin doesn't take any backups, reorganize or rebuild any indexes or even check what disk space is free for ever-growing databases. Now a disk was filled and the service was blocked they want to know "why did logs grow so rapidly".

I'm realistically just sanity checking to see if the reason due to their lack of maintenance as it seems like a likely cause for increased amount of logging but I don't have the competences to confirm that.

Thanks all for any help/guidance and please excuse any inconsistent jargon/noobish points (still self-learning).

Answer 1

As you've theorized, index fragmentation is not going to make the transaction log grow faster. The volume of transactions executed against the database will be the main driving factor for how fast the transaction log grows.

Validate they are taking log backups and have enough drive space to accommodate the current transaction volume. Also, as Erik Darling pointed out, you may also want to check auto-growth settings on the transaction log and make sure it's set to a fixed growth setting rather than a percentage. If it's set to a percent value, it will grow at a larger rate during each growth event. Typically 100-200 MB as a fixed amount is good here.

With that said, it sounds like their database is in need of some good maintenance routines and health checks.

I would suggest you start by having them download and install the First Responder Kit and run sp_Blitz @CheckServerInfo = 1. Review the top priority findings (priority <= 50) and help them to get those resolved.

While you're at it, if they're also suffering from high index fragmentation, have them implement Ola Hallengren's maintenance solution. These are great for index maintenance, as well as backup jobs.

I'd suspect if they have the problems you've mentioned thus far, you've only scratched the surface of what your going to find if you run sp_Blitz. They're probably not doing integrity checks either. These steps should provide a good starting point to help stabilize their SQL Server.