asked on
What actually makes a SQL log file grow?
ASKER
In general however log files are for transaction logging (data changes from inserts, deletion, updates) so I would say that your upgrade activities aren't logged or that your activity is very light on inserts/updates/deletions of the table data so the log files don't increase noticeable.
ps: I'll add the SQL Server topic so perhaps a specialist DBA will know much more than I do on this. (by inference you will note I'm not a DBA nor a specialist in SQL Server, much more of a "generalist" in matters such as logging.)
The setup usually . Does not auto shrink as that will add overhead.
Usually mdf should be allowed to grow unlimited, the log ldf should always be restricted.
The full db backup frees up inactive transactions while the log file size remains the size it reached .....
ASKER
10-20 the transaction logs would not grow larger than the simple one you have now if properly managed through the transaction log backup.
Few ask, until the need is there...
ASKER
Personally I'd suggest taking a look at differential backups.
You could do them at, say, 4 or 6 or 8 or 12 hour intervals between full backups, whatever works for you. They don't allow point-in-time, but would allow recovery to the last differential. Even a single differential 12 hours after each full backup would reduce loss to 12 hours max instead of 24. And diffs are generally small, so they don't require much resources to take or manage.
In fact, for most of my dbs, I only take full backups on weekends and do diffs during the week, to save time and resources on backups.
ASKER
Transaction log backups are only possible if the db is not in simple mode. You need to verify the recovery model for the dbs too.
If you're not using the tran log backups, and don't plan to, you'd be far better off switching to simple recovery.
ASKER
"Trashing about"? I don't follow. The log file is sequential, period.
without that once the LDB is expanded based on demand even in simple recovery mode, it releases the space within the binary file, it does not auto shrink to release space on the filesystem.
Never set auto-shrink on, never, period.
ASKER
I understood that, I knew you were talking about other IT people. I've run into many myself. Some even try to tell me I don't know what I'm talking about! Very frustrating.
In a binary "storage" stuff gets added and when is no longer used is released but the raw size of the file is set based on the largest demand of transaction it had to process. What does "thrashing" mean to that IT if not letting the file grow and autoshrink?
ASKER
I am still at a loss what this IT's recomendation was.
ASKER
Exact opposite. Full recovery does some logging that simple recovery does not do. That is, changing from simple to full will typically significantly increase the amount of logging that occurs. You need to make allowance for that overhead.
ASKER
Full recovery and the transaction log backup scheduling helps manage the size of the log file.
I suspect that your IT had someone who configured their DB to autoshrink in simple mode when they started in their career and that "trauma" is carrying through.
ASKER
https://docs.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver15