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.
- 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).