Thanks Amit for sharing same on http://troubleshootingsql.com
SQL Server 2012
Trace Flag
|
Description
|
Article
|
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 |
No comments:
Post a Comment