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

Short Descriptions

Flag Short Description
See Also  
205 Reports to error log when recompile occurs due to auto-update of stats.
 
Flags  
* 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.
 
Fix/PastRel  
3924 Enables a fix for “XA” trans w/in JDBC connections.
9024 Partitions a log pool memory object by memory node.

Flags

610

Enables minimal-logging under certain scenarios. Connect item indicate TF 610 soon may be a query hint.

Data Loading Perf Guide | StorEng_1 | Connect_1

2537

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.

PRand_1 | PRand_2

3412

Reports when each transaction is rolled forward or back, except for large transactions. (Needs verification for whether still current)

170115

8599

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

295027 | 903742

9038
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.”
9909

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.

3174963


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.

3924

Enables a fix where “XA” transactions started within a JDBC-connected Java app are not closed properly and stay open indefinitely.

3145492

9024

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

2809338