Databases and Files¶
Flags in this category fall into the below groupings:
- Allocation –> Flags that modify how pages in MDF/NDF files are allocated to individual objects.
- Other –> Flags not related to page allocation.
- Fixes and Past Relevance –> Fix flags and flags which are no longer relevant.
See also: 3449 and 8903 ([Disk and Network IO](https://github.com/AaronMorelli/SQLServerTraceFlags/blob/master/Categories/DiskNetworkIO.md))
(TODO: ensure 1802 is present in the See Also for Security) (TODO: find other entry for 9394 and place an in-line link to its other home. Ditto for the other section) (TODO: 10207 should be moved to the corrupt Checkdb section and placed here in the see also section) TODO: is 9929 in multiple locations? e.g. the special features one? TODO: should 1482 move to a t-log focused page? TODO: 2330 should have a reference in one of the optimization pages
Short Descriptions¶
| Flag | Short Description |
|---|---|
| See Also | |
| 205 | Reports to error log when recompile occurs due to auto-update of stats. |
| … | |
| Allocation | |
| 670 | (or 671) Disables deferred deallocation. |
| 1106 | Creates a new ring buffer to track TempDB allocations. |
| * 1117 | When one files grows, all files in filegroup grow with it. |
| * 1118 | Eliminates most single page allocations. |
| 1165 | Prints calculations used for proportional fill algorithm. |
| 1180 | Changes the way text/image data is allocated |
| 1197 | Prevents use of worktable cache for certain TempDB allocations. |
| 1806 | Disables instant file initialization |
| … | |
| Other | |
| 272 | Reverts identity column generation behavior to pre-SQL 2012 behavior. |
| 647 | Skips a new-in-2012 data check that lengthens ALTER TABLE durations. |
| 1140 | Allows reversion to an older algorithm for mixed page allocs. |
| 1482 | Prints info on a variety of t-log management operations. |
| 1808 | Ignores auto-closing DBs even if auto-close property is set. |
| 1816 | Provides more details around IO errors for stretch database. |
| 2330 | Stops data collection for sys.db_index_usage_stats. |
| 2548 | Causes DBCC SHRINK and other options to skip LOB_COMPACTION. |
| 3861 | Allows DB startup code to move system tables to primary FG. |
| 9851 | Disables Hekaton auto-merge process for checkpoint files. |
| 9929 | Reduces Hekaton checkpoint files to 1 MB each. |
| 10204 | Disables merge/recompress of smaller columnstore delta rowgroups. |
| 10207 | Disables skipping of corrupt segments for columnstore scans. |
| … | |
| Fix/PastRel | |
| 1802 | Workaround for permissions change upon DB detach |
| 1807 | Allows creation of DB file on UNC paths |
| 9394 | Enables fix for AV re: Japanese characters |
| 10202 | Enabled new column store DMV in pre-release SQL version. |
Allocation¶
- 670 (or 671)
CSS: Disables deferred deallocation. But note Paul White’s comment on the post! The flag number may actually be 671.
- 1106 (Info)
Creates a new RB in sys.dm_os_ring_buffers that tracks allocations made in TempDB.
- 1117
Doc2014 BOL 2014: “When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.
Note: Beginning with SQL Server 2016 this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no affect.”
This flag is commonly associated with tempdb but applies to all databases when on. The flag is typically used to ensure that all files grow evenly to maintain a well balanced proportional-fill allocation algorithm. Nacho gives a very special/rare edge case for sysfiles1. Chris Adkin has some interesting screenshots on its effect under certain workloads. (PRand_1 doesn’t reference this flag but its info is highly relevant to this topic.)
ALTER DATABASE file and filegroup options | BobWard_Pass2011 | PRand_4 | Nacho_2 | CAdkin_2 | SQLArticlesDotCom
- 1118
Doc2014 BOL 2014: “Removes most single page allocations on the server, reducing contention on the SGAM page. When a new object is created, by default, the first eight pages are allocated from different extents (mixed extents). Afterwards, when more pages are needed, those are allocated from that same extent (uniform extent). The SGAM page is used to track these mixed extents, so can quickly become a bottleneck when numerous mixed page allocations are occurring. This trace flag allocates all eight pages from the same extent when creating new objects, minimizing the need to scan the SGAM page. For more information, see this Microsoft Support article.
Note: Beginning with SQL Server 2016 this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, and trace flag 1118 has no affect.”
ALTER DATABASE SET Options | 328551 | 837938 | 936185 | 2154845 | CSS_3 | CSS_4 | PRand_5 | CAdkin_2
- 1140
Allows reversion to an older, more aggressive form of the mixed-page-allocation algorithm. The flag was introduced as a workaround for a bug in 2005SP2/SP3 and SQL 2008 where mixed page allocations climb continually in tempdb for workloads that use tempdb extremely heavily. That behavior was due to a change in the way that mixed-page allocations are done. KB has a great description of both the “old” and “new” way that free pages are found for a mixed-page allocation to be performed.
- 1165 (Info)
Outputs the recalculated #’s (every 8192 allocations) for the proportional fill algorithm when multiple files are present. Requires TF 3605, output goes to SQL error log.
- 1180
(Very old, may not be functional) KB notes that after a SQL 7.0 fix is installed, this flag will cause text/image data to be placed in free pages in partially-allocated extents; w/o the flag, text/image data is placed in newly-allocated extents until the file size limit is reached; only then will partially-allocated extents be used for new data.
- 1197
Bob Ward uses to prevent allocation of TempDB pages (by Work Tables) from being pulled from a worktable cache (see around 1:25:00). The (very old) KB references for use w/1180 in reclaiming space from inefficiently-stored text/image data.
Other¶
- 272
Connect: “In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability. If you require the same identity generation semantics as previous versions of SQL Server there are two options available:
- Use trace flag 272. This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
- Use a sequence generator with the NO CACHE setting. This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.”
Later in the Connect discussion, one commenter notes that when adding the TF as a startup flag, the flag only appears to work when using the “lowercase t” syntax rather than the more common “uppercase T” syntax.
- 647
Avoids a new-in-SQL 2012 data check (done when adding a column to a table) that can cause ALTER TABLE… ADD <column> operations to take a very long time. The KB has a useful query for determining the row size for a table.
- 1482 (Info)
Prints info to the Error Log (3605 not necess.) for a variety of transaction log operations, including when MinLSN value is reset, when a VLF is formatted, etc. (First discovered in Bob Ward’s PASS 2014 talk on SQL Server IO, and then tested for myself.)
<links needed>
- 1808
Directs SQL Server to ignore auto-closing databases even if the Auto-close property is set to ON. Must be set globally. Present in 2005+.
- 1816 (Info)
Bob Ward briefly references this flag in his PASS2014 IO talk, saying it “could provide more details around errors” that occur with IO done to SQL data files in Azure Storage (stretch/http IO, I think he means).
<links needed>
- 2330
Stops the collection of statistics for sys.db_index_usage_stats. CAdkin: also disables sys.dm_db_missing_index_group_stats, and thus is useful when seeing high waits on the OPT_IDX_STATS spinlock.
2003031 | PRand_3 | BrentOzar_1 | CAdkin_1
- 2548
“SQL 2005 has a –T2548 dbcc tracon(-1, 2548) that allows shrink* and other LOB_COMPACTION actions to be skipped. Enabling this returns shrink* behavior to that similar to SQL 2000.”
- 3861
Allows the DB startup code to move system tables to the primary filegroup. Introduced for a bug in SQL 2014 upgrade process, where system tables could be created in a secondary filegroup (if that FG was the default).
- 9851
Disables Hekaton’s auto-merge process; if this flag is enabled, the various merge-related procedures will need to be called manually. First seen in a Sunil Agarwal session at PASS 2014, also present in Kalen Delaney’s book on Hekaton.
<links needed>
- 9929
Enables an update that reduces the “disk footprint [of In-Memory OLTP] by reducing the In-Memory checkpoint files to 1 MB (megabytes) each.”
- 10204
Doc2016 - BOL 2016: “Disables merge/recompress during columnstore index reorganization. In SQL Server 2016, when a columnstore index is reorganized, there is new functionality to automatically merge any small compressed rowgroups into larger compressed rowgroups, as well as recompressing any rowgroups that have a large number of deleted rows.”
- 10207
When a CCI is corrupt, allows a scan to skip corrupt segments and suppress errors 5288 and 5289, thus enabling the copy-out of data in a corrupt CCI.
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.
- 1802
Workaround for: “after you detach a Microsoft SQL Server 2005 database that resides on network- attached storage, you cannot reattach the SQL Server database… This problem occurs because SQL Server 2005 resets the file permissions when the database is detached. When you try to reattach the database, it cannot be attached because of limited share permissions.”
It sounds like this flag disables functionality in changing permissions on database files after the DB is detached, thus has security implications.
922804 | StorEng_1 (comments, though Kevin likely means 1807)
- 1807
Allows the creation of a database file on UNC paths, and is a workaround for errors 5105 and 5110. The KB describes MSFT policy towards DBs on network locations.
- 9394
(9394 is either doing double-duty or there’s a typo. See other entry for 9394) Apparently enables a fix for an access violation when a table with Japanese characters has an indexed changed.
- 10202
- Sunil Agarwal PASS 2014 demo script: enables new DMV named sys.dm_db_column_store_row_group_physical_stats. DMV was not in 2014 at the time of the demo, thus appears to be in a future (or internal) version of SQL Server.