DRAFT
Here is a lot of info about SQL Server Trace Flags and their usage:
Links
Finding out who dropped a table using the transaction log
Trace Flag List for SQL Server 2012
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
Trace Flag Description KB Startup DBCC 205 Report when a stored procedure is being recompiled. This trace flag will write the a corresponding message to the SQL Server errorlog. 195565 Y Y 260 Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(). BOL Link Y Y 610 Minimally logged inserts for indexed tables for fast load scenarios. Read article for limitations. Article Y Y 652 Disable pre-fetch scans 920093 Y Y 661 Disable ghost removal process 920093 Y Y 806 Audit failure during a read operation is reported in the errorlog as a 823 message on enabling this trace flag 841776 Y Y 815 Detect unwanted changes to in-memory SQL Server data pages, latch enforcement is enhanced with this trace flag enabled Article Y Y 818 Detect stale reads. Very helpful in analyzing recurring data corruption issues. Article Y Y 830 Suppress stuck-stalled I/O warnings Article Y Y 834 Use Microsoft Windows large-page allocations for the buffer pool. Only applies to 64-bit instances. 920093 Y N 836 Use the max server memory option for the buffer pool. Only applicable for 32-bit SQL Server 920093 Y N 840 On Standard edition, allows larger I/O extent reads to populate the buffer pool when SQL Server starts 912322 Y Y 1106 Enable tempdb allocation trace. Not suggested for production use. 947204 Y Y 1117 Even growth of all files in a file group Whitepaper Y Y 1118 Prevents allocation of pages from mixed pages for all databases 328551 Y Y 1204 Returns the resources and types of locks participating in a deadlock and also the current command affected. Useful for SQL Server 2000. BOL Link Y Y 1205 Prints information about each deadlock search 832524 Y Y 1211 Disabled lock escalation BOL Link Y Y 1222 Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. To be used for SQL Server 2005 and above. BOL Link Y Y 1224 Disables lock escalation based on the number of locks BOL Link Y Y 1260 Disabled mini-dump for non-yield conditions Whitepaper Y Y 1262 Dump everytime a non-yielding scheduler condition is detected. Article Y Y 1439 Trace database restart and failover messages to SQL Errorlog for mirrored databases 983500 Y Y 1448 Prevent replication latency with database mirroring enabled. Apply KB 983480 to prevent issues after enabling -T1448. 937041 Y Y 2301 Enable advanced decision support optimizations 920093 Y Y 2340 Disable batch sort to reduce CPU consumption Blog Y Y 2371 Use a modified threshold for auto update statistics 2754171 Y Y 2389 Automatically generated quick statistics for known ascending keys 922063 N Y 2390 Automatically generated quick statistics for all columns that include known ascending keys or unknown ascending keys 922063 N Y 2528 Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. Article Y Y 2544 Put maximum information in a dump file (all memory in the process). Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y 2546 Dump all threads in a process. Can lead to a very large dump file size for 64-bit and 32-bit systems with AWE enabled. Article Y Y 2551 Generate a filtered dump of the SQL Server process. Reference Y Y 3004 Write extended information about backup/restore to Errorlog Blog N Y 3014 Write extended information about backup/restore to Errorlog Blog N Y 3023 Automatically enable CHECKSUM for BACKUP command 2656988 N Y 3042 Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size Article Y Y 3205 Disable hardware compression for tape drives. Article N Y 3213 Display backup/restore configuration parameters Blog N Y 3226 Suppress successful backup messages in the logs Article N Y 3502 Displays start and end of checkpoint. Can be very verbose and increase your transaction log size very quickly 815436 Y Y 3504 Print Checkpoint summary Blog Y Y 3505 Disable Checkpoint 815436 Y Y 3604 Send trace output to client Y Y 3605 Send trace output to SQL Errorlog Y Y 3608 Prevents SQL Server from automatically starting and recovering any database except the master database Article Y N 3625 Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages. Article Y N 3688 Needed to enable fix for issue mentioned in KB Article. 922578 Y N 4010 Allows only shared memory connections to the SQL Server Blog Y Y 4022 Skips launching automatic stored procedures when SQL Server starts. This is equivalent to setting “scan for startup procs” configuration option to 0. Article Y Y 4029 Push more verbose errors to the errorlog when read+write errors occur in the network layer on the server side. Blog Y Y 4199 Controls multiple query optimizer changes previously made under multiple trace flags. Refer article for more details. 974006 Y Y 4606 Disables password policy check during server startup Blog Y Y 4610 Increases the size of the hash table that stores the cache entries by a factor of 8. 959823 Y Y 4616 Makes server-level metadata visible to application roles Article Y Y 4618 Limit the number of entries in the TokenAndPermUserStore store 959823 Y Y 4621 Control TokenAndPermUserStore store size using registry value 959823 Y Y 6527 Disables generation of a memory dump on the first occurrence of an out-of-memory exception in CLR integration Article Y Y 7806 Enables a dedicated administrator connection (DAC) on SQL Server Express Article Y N 8002 Treat affinity mask like processor affinity 818765 Y Y 8004 Create memory dump for the first occurrence of out-of-memory condition Article Y Y 8011 Disable the ring buffer for Resource Monitor 920093 Y Y 8012 Disable the ring buffer for schedulers 920093 Y Y 8018 Disable the exception ring buffer 920093 Y N 8019 Disable stack collection for the exception ring buffer 920093 Y N 8020 Disable working set monitoring 920093 Y N 8024 Performs additional checks before generating non-yielding condition dump Whitepaper Y Y 8032 Reverts the cache limit parameters to the SQL Server 2005 RTM setting which in general allows caches to be larger Article Y N 8048 Forces CPU based partitioning Blog Y N 8602 Ignore all index hints Whitepaper Y Y 8721 Will dump information into the error log when AutoStat has been run. 195565 Y Y 8744 Disable pre-fetching for ranges 920093 Y Y\ |
Views – 3566