July 18, 2025 at 7:00 pm
Comments posted to this topic are about the item When INCLUDE Columns Quietly Inflate Your Transaction Logs
July 19, 2025 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 21, 2025 at 5:14 am
Thank you for taking the time to experiment and share your findings. This is important to note. I wonder though, if you would temporary switch the recovery_model to bulk_insert , run the update, and switch back.... if this would produce minimal logging. After all this was a simple update you performed.
----------------------------------------------------
July 21, 2025 at 9:49 pm
Hi @MMartin1
Thank you — that’s a great question!
You're right that BULK_LOGGED recovery model can enable minimal logging in certain scenarios. However, unfortunately, regular UPDATE statements do not benefit from minimal logging, even under BULK_LOGGED.
Only the following operations can be minimally logged in BULK_LOGGED:
BULK INSERT
INSERT ... SELECT into a table with TABLOCK
SELECT INTO
Index creation/rebuilds
In contrast, UPDATEs, DELETEs, and regular INSERTs still generate full log records — especially for wide indexes with INCLUDE columns, as I demonstrated.
So in this experiment, even if I had switched to BULK_LOGGED, the update would have still generated significant log volume because SQL Server logs the before/after image of the index leaf row — and with INCLUDE columns, that row is very wide.
Great thought though — and it's a subtle detail that trips up a lot of people. I might even add a note about this in a follow-up or update. Appreciate you taking time to dig into it!
July 22, 2025 at 10:36 am
For log shipping, this means the secondary server now has to restore bigger log backup files, increasing replication lag. Your DR site that was just a few seconds behind could now be minutes behind, exposing the business to data loss risks during failovers.
your logshipping secondary is as far behind as the copy and restore jobs dictate, you wouldn't usually configure these on a too regular basis. They would typically lag behind the primary's log backup job.
Similarly, in AlwaysOn availability groups, larger transaction logs mean more data to synchronise between replicas. If your network bandwidth or replica performance cannot keep up, the secondary replicas begin to lag. For business-critical systems, this directly translates into longer failover times, breaching RTO/RPO SLAs silently.
segregating mirror traffic to a dedicated network can help with performance, also, in later versions of sql server the redo queue is multi threaded. The transaction logs are not synchronised as such in the way log shipping would. The transactions are mirrored real-time between the replicas hence the Database Mirroring endpoints between the replicas
Beyond performance and availability, there’s a pure cost dimension. Backup storage isn’t free. When every log backup is inflated with unnecessary changes – often INCLUDE column updates that were never even queried – your backup size, storage bills, and even retention management complexity multiply quietly in the background.
In essence, an index meant to optimise one slow query can end up silently choking your HA/DR pipelines, increasing your backup storage bills, and delaying recovery times during critical incidents. That is why index design decisions should never be taken in isolation – their impact on the broader operational ecosystem must be evaluated with equal diligence.
sure it's a factor but wouldn't consider it as detrimental as specified, if you're backing up logs every 15 mins its probably like 1 or 2 log backups in a day, every couple days
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply