Transactions, Logging, and Recovery¶
All flags in this category are related to transactional behavior or transaction records being written to disk (to the transaction log).
See also: 9592 and 9567 High Availability/Distributed Technologies https://github.com/AaronMorelli/SQLServerTraceFlags/blob/master/Categories/HADRDist.md
|205||Reports to error log when recompile occurs due to auto-update of stats.|
|* 610||Enables minimal logging under certain scenarios.|
|2537||Enables fn_dblog() to read the inactive portion of the t-log.|
|3412||Reports when each tran is rolled forward or back.|
|8599||Allows the use of save points within distributed trans.|
|9038||Disables the multi-threaded log writer in SQL 2016.|
|9909||Forces single-threaded ALTER TABLE.|
|3924||Enables a fix for “XA” trans w/in JDBC connections.|
|9024||Partitions a log pool memory object by memory node.|
Enables minimal-logging under certain scenarios. Connect item indicate TF 610 soon may be a query hint.
Enables fn_dblog() to read all of the t-log, not just the active portion of the log. Argenis Tweet: fn_dblog + 2537 is a way to check your t-log file for corruption.
Reports when each transaction is rolled forward or back, except for large transactions. (Needs verification for whether still current)
(Very old, needs verification.) Allows the use of a save-point within a distributed transaction. Note that one reference to the flag in KB295027 is written incorrectly as 8588.
- CAdkin Tweet: “If you are using low-latency storage it appears that you can also cause high spins on LOGFLUSHQ in SQL 2016 with the in-memory engine and full durability. The multithreaded log writer is the cause of this and can be turned off via TF 9038.”
Turns off a logging optimization when an ALTER TABLE operation on a memory-optimized table would normally be done in parallel. This avoids the possibility of data loss b/c of a bug in parallel ALTER TABLE.
Fixes and Past Relevance¶
These flags either are old and irrelevant for modern builds, appear only in CTPs, or enable a fix in a CU but are baselined in a later service pack or release.
Enables a fix where “XA” transactions started within a JDBC-connected Java app are not closed properly and stay open indefinitely.
(On by default as of SQL 2014 SP1+) Enables a fix in SQL 2012/2014 that partitions (by memory node) a “pointer to a memory object” (PMO) to the log pool. The KB references this flag specifically in the context of Availability Groups, though the flag appears to deal with the design of the “logpool”. Note that 8048 is also potentially applicable here, as it will further partition the PMO to a by-CPU partitioning scheme. (See the KB).