ASE Traceflags
| Flag | Type | Description |
|---|---|---|
| -1 | Not truly a trace flag, but passed to DBCC TRACEOFF to disable all active flags in all processes. | |
| 100 | Print the parse tree after processing sql cmdtext | |
| 104 | Enable T/SQL lightweight procedures | |
| 106 | Disable the raise of the warning messages 11732 and 11733 11732 is "Warning : Ambiguous correlation names in the set clause" 11733 is "Warning : Ambiguous table to update; Using the table specified in the update clause." | |
| 107 | (obsolete as of ASE 12.0 GA). Used to be required to enable the Alter Table Drop Column command, but is no longer needed. | |
| 109 | (obsolete as of ASE 15.0 GA). Prior to 15.0, caused ASE to only allow ASCII characters to be recognized in SQL keywords, numeric literals, and punctuation and to not allow equivalence mapping of non-ASCII characters. See ASE Traceflag 114. | |
| 114 | Allow equivalence mapping of non-ASCII characters to be recognized in SQL keywords, numeric literals, and punctuation. Prior to 15.0 this was default behavior, but as of 15.0 only ASCII characters are allowed by default. | |
| 117 | (not actually implemented as of 15.7 GA) Return more than just the first syntax error encountered during parsing. | |
| 119 | Turn off the fix for CR 488239-1: Allow identity functions in SELECT INTO statements containing the UNION operation even though this might result in unexpected or duplicated identity values. See KBA 2738577. | |
| 130 | Disable the fix for 813547 which caused a regression that led to the sourcecode stored in syscomments.text having incorrect SQL for sequential conditional DML statements. See KBA 2749949 for details. | |
| 200 | Print the query tree before optimization | |
| 214 | Used for analysis of renormalization recompilation. Prints information on when and why renormalizing or recompiling was required. See also traceflag 7755 | |
| 243 | Disable column expansion (converting "select *" to "select col1, col2, ..., coln") in source code stored in syscomments. | |
| 244 |  | Cause the str_replace() function to return a result with a default length of 16384 rather than 255 when the result length is otherwise unknown at compile time. |
| 298 | Suppress error 11055 "Query contains an illegal outer-join request." for correlated Sybase outer joins. | |
| 299 | Turn off bugfix 55952. This traceflag causes ASE to try to open temp tables by name if they cannot be opened by id. This avoids the overhead of recompiling the stored procedure, at the risk of running into problems if the schema for the temp table is not what was expected. | |
| 304 | Turns off OR strategy for "special or" strings | |
| 327 | Disable at boot time the fix for CR 71058 which made "Not Equal" and "IS NOT NULL" operators sargable but not indexable. In ASE 11.0, the fix for CR 71058 caused ASE to use an less efficient OR strategy in some cases, so the traceflag was used to turn off that change. | |
| 328 | Disable reformatting. (From the Performance and Tuning Guide: "Reformatting refers to materializing the inner side of a nested join into a worktable, then creating an index on the joining predicate.") | |
| 408 | Make console output blocking (default is non-blocking). See KBA 2022013 | |
| 427 | If enabled, disables the fix for CR 560331 and will turn on eager evaluation of expression virtual VARs that have TXTPTRs as inputs. | |
| 446 | If enabled, turns off the basic_optimization path for queries which are unable to go through Compatibility mode (fastpath). Introduced in 15.0.3 ESD #1 | |
| 450 | Causes GROUP BY results on 32 columns or less to be sorted as if an ORDER BY had also been done, similar to default behavior prior to 15.0. | |
| 457 | Converts cursors that would have materialized their result sets in pre-ASE 15 and do not do so in ASE 15 into insensitive cursors to uncouple the result set data from the source tables as in ASE 12.5. | |
| 467 | When this flag is set, statements referencing temp tables will not be cached in statement cache. | |
| 478 | Turns off early placement of worktables in the query tree for subquery reformatting. | |
| 510 | Turn off subquery result caching. Used as a workaround for CR 814478. | |
| 512 | Fix subquery cache size at 5 rows. | |
| 547 | For internal debugging purposes, causes a 20 second delay after creation of a new query plan. | |
| 549 | ||
| 599 | Obtain the textptr home location exclusive lock earlier (before updating/deleting LOB) than would normally be the case. This should reduce the likelihood of deadlocks produced when two tasks simultaneously UPDATE and SELECT text/image data from the same row. For APL tables, use of this trace flag may be counter-productive because deletion of any non-clustered index entries will be done with this lock held. The exclusive lock may therefore be held for an unacceptably long time, and may hence cause more deadlocks | |
| 615 | By default, when a 694 or some variants of a 697 error occur ASE automatically turns on traceflags 829 and 830 to perform diagnostic checks for a corrupted page being read from or written to disk. The 615 trace flag stops ASE from automatically turning on these traceflags. | |
| 693 | Turn off some blocking optimizations for uncommitted inserts in datarows tables and uncommited updates on unmodified columns. i.e. causes uncommitted datarows inserts to block other sessions. | |
| 701 | (Introduced in 12.5.1, obsolete as of 15.0.1). Disabled the fix for CR 288522. | |
| 711 | Initialize freed memory blocks. Useful for diagnosing potential memory corruption. The extra processing will reduce performance to some degree, how much depends on the applications being run. | |
| 712 | Turns off engine local memory optimization | |
| 720 | Turn off the proc_hdr memory mismatch warning to avoid filling up the errorlog. | |
| 753 | Disable large allocation auto tune in memory manager | |
| 754 | Do not use this trace flag. If you find it on, turn it off. | |
| 757 | Reduce scale down for large allocation autotune by replacing a procedure before scaling down. This is expected to reduce a performance drop in an ASE with a large and almost full procedure cache. Introduced in 15.0.2 ESD 3. | |
| 758 | Enable large Engine Local Cache (ELC) memory (4k, 8k and 16k pages) feature. Introduced under CR 652106-2 in ASE 15.7 ESD 2 and higher. | |
| 766 | Do expensive engine local cache (ELC) checks. | |
| 767 | Protect Engine Local Cache (ELC) pages using mprotect (available on non-NT systems only). | |
| 828 | Validate the cache after a dynamic cache operation. | |
| 829 | Perform a sanity check after a read from disk. Print diagnostics on failure. ASE turns these flags on automatically after certain errors. See traceflag 615. | |
| 830 | Perform a sanity check after a write to disk. Print diagnostics on failure. ASE turns these flags on automatically after certain errors. See traceflag 615. | |
| 833 | While this trace is on, the QUIESCE DATABASE command will not raise message 880 "Your query is blocked because it tried to write and database '%.*s' is in quiesce state. Your query will proceed after the DBA performs QUIESCE DATABASE RELEASE". This is to prevent issues with applications that aren't yet written to handle this message. | |
| 838 | Diagnostic: output an error message when adding a new page for a log and it is discovered the buffer is already marked destroyed. | |
| 858 | Disable optimistic/lockless latching of buffers. | |
| 894 | Enable additional error checking when attempting to predirty a destroy buffer. Introduced under CR 819340 in 16.0 SP02 PL09 | |
| 897 | Enable LRU Linking Integrity check. Introduced under CR 737543 in 15.7 SP110. | |
| 900 | Allow logins with OPERATOR_ROLE use databases that are in DBO USE ONLY mode. | |
| 990 | Allow 'use db' to succeed, so that any login with sa role or 'own any database' previlege can do some diagnostics in a database that was not, yet, onlined. This allows one to peek at data structures in an older version db to debug upgrade issues. | |
| 1101 | Turn off large extent allocation (aka Large Scale Allocation (LSA) ). A possible workaround to CR 551797. | |
| 1102 | Causes 1105 errors to be sent to the errorlog. By default, 1105s are sent just to the client. The result is similar to running "sp_altermessage 1105, 'with_log', true" although the traceflag raises the error with state 1 while sp_altermessage shows state 2. The traceflag predates the sp_altermessage functionality. | |
| 1116 | Suppress runtime 1151, 1152 and 1153 errors which can result from running sp_spaceused (or other callers of OAM builtin functions) in a volatile database. | |
| 1134 | Suppress diagnostic message 1157 raised when a DOL datapage header field insert-free-space has been detected as incorrect and is recalculated. | |
| 1198 | To the extent possible, avoid taking SH_INT locks for functions that read an OAM prior to checking OAM for the relevant page counts. | |
| 1200 | Print process id and type of lock requested. | |
| 1201 | Obsolete. | |
| 1202 | Insert blocked lock requests in syslocks, which by default only displays granted locks. | |
| 1204 | Print out deadlock chains and victim. | |
| 1212 | Trace every lock acquisition and release. | |
| 1213 | Enable object and user level lock statistics monitoring. See SAP ASE 16.0 Reference Manual: Procedures > sp_object_stats | |
| 1214 | Acquire SH_PAGE locks on index pages even if the <for update> clause is specified for the cursor. | |
| 1217 | Trace every lock acquisition and release for user tables only. Like 1212 but less output due to ommitting the system catalog overhead for accessing tables, checking permissions, etc. | |
| 1218 | Print SHOWPLAN output to errorlog on deadlock. Only works if traceflag 1204 is also on. | |
| 1223 | Send deadlock information to errorlog with the line header containing engine, spid, timestamp, etc. Works only if 1204 is also on. | |
| 1533 | Enable parallel sort for work tables. This will bypass page count heuristics in psort_decision(). Useful for testing. | |
| 1642 | ||
| 1653 | Enable huge pages (feature available on Linux pSeries only). Traceflag no longer needed as of ASE 15.0.3 ESD#1 (CR 472272); ASE now uses huge pages by default | |
| 1670 | Disable updates to config file at shutdown to avoid writing the current bucket pool changes. | |
| 1716 | Disable the 590800-1 fix. With this trace flag, error msg 2116 is not raised when creating a trigger that selects from a TEXT/IMAGE/UNITEXT or off-row Java datatype column of an inserted or deleted table. Message 2116 is " CREATE TRIGGER failed because selecting from a TEXT, IMAGE, UNITEXT or off-row Java datatype column of the inserted or deleted table is not supported." | |
| 1804 | Allow data fragments in a template database (or model) to be copied onto a data segment in the target database. | |
| 2205 | Print steps in platform-specific high availability (HA) DLL. | |
| 2509 | Print row diagnostics when collocate() finds a bad column length. | |
| 2512 | Bypass syslogs when doing checkalloc(). This will avoid spurious errors on syslogs. | |
| 2779 | Prints additional diagnostic information when error 290024 "invalid col length" is raised. This error presents difficulties because it doesn't indicate which page or even which database the problem is happening in. The traceflag causes ASE to output the spid's current query plan and the contents of each open SDES, from which the current page number(s) being accessed can be determined (scur->pageid). | |
| 2781 | When creating a table, check that value supplied for columns defined with a default value matches the column type (i.e. raise an error for cases like a string being provided as the default for a numeric datatype column). | |
| 2792 | Enable fix for CR 475139-1 to sleep on the DES_CLONED_STATE of the DES. | |
| 2810 | On every read from sysprocedures, behave as if upgrading compiled object (i.e. rebuild query tree from source code text stored in syscomments). Having this traceflag on can result in "Error fetching results for query: " errors during upgrade. | |
| 3000 | Outputs beginning and ending times for DUMP operations. Use with 3604 to send output to client or 3605 to send output to errorlog | |
| 3100 | Allows the load of a master database which was dumped with a different sort order/charset to the sort order/charset currently in use on the server. | |
| 3199 | Mark non-clustered indexes on system tables suspect during cross-platform load database. This flag can to be used if an upgrade failed due to index-related errors, and needs to be turned on for both LOAD DATABASE and ONLINE DATABASE. The suspect indexes can then be fixed after the upgrade using sp_fixindex. | |
| 3412 | During recovery, do not use the strategy that minimizes the phase 1 redo pass. Rather, force the redo pass to visit every log record in phase 1. | |
| 3425 | Print diagnostic information during recovery. | |
| 3438 | Recover only the system databases and tempdb. No user databases are recovered. | |
| 3458 | Parallel recovery will spawn as many threads as indicated by the config parameter without verifying that there are at least that many engines available. Performance will degrade if the number of recovery threads is larger than the number of engines. | |
| 3477 | Used when (usually by accident) the master database has been set to "bypass recovery" mode. If this trace flag is on, recovery will clear the 'bypass recovery' bit for the master database and recover master normally. | |
| 3481 | Skip post commit optimization dune under CR 422687. | |
| 3499 | Allow recreation of sp_do_poolconfig during recovery. | |
| 3502 | Print a message to the console every time a checkpoint is written for a db. | |
| 3512 | The checkpoint process wakes up once a minute and looks at each database to determine if a checkpoint should be issued. In a normal online database, the checkpoint process decides this by looking at how many log records have been added since the most recent checkpoint and compares that to the value for the sp_configure setting "recovery interval" multiplied by 6,000. If this traceflag is set, the checkpoint process ignores the recovery interval setting and checkpoints the database even if there are fewer new log records. | |
| 3601 | Print a stack trace when any error is output using the internal ex_raise() function call. i.e, outputs stack traces for some errors, but not all. | |
| 3603 | Simulate connection receiving an interrupt signal while executing any command. | |
| 3604 | Send DBCC output to client (by default, output goes to ASE console [the terminal ASE was started from]) | |
| 3605 | Send DBCC output to errorlog (by default, output goes to ASE console [the terminal ASE was started from]) | |
| 3607 | Boot ASE without recovering any databases. (Used to get access to system table data, in their current state, when master recovery fails). | |
| 3608 | Boot ASE recovering only the master database. (Allows SA to resolve recovery issues on other databases). See also trace flag 3438 for recovering only system databases. | |
| 3610 | Cause division by zero to return a null value rather than fail with a 3607 error. A warning message that division by zero occurred is still output. | |
| 3647 | Disable datachange counter maintenance | |
| 3650 | Collect MDA related counters for system tables. By default, these counters are not maintained. See KBA 2751736 for an example of use. | |
| 3662 | Print text of SQL statement being executed when a Resource Governor limit is hit and the action is abort statement, abort transaction, or terminate session. | |
| 3686 | Disable improved stack overflow introduced under CR 753525 | |
| 3687 | System wide trace flag used to print full stack when aborting transaction due to guard page overflow. | |
| 3641 | Enable Job Scheduler task 'log everything' | |
| 3717 | Allows dropping the sysdams system table. Used when downgrading below 15.7 SP100 or using dbcc dam with the "drop" option. Not needed after 15.7 SP130 or 16.0 SP01. | |
| 3968 | Disable performance enhancement of runtime rollbacks and crash recovery of long running skewed transactions. This trace flag is a workaround for CR 778707. See KBA 2252721. | |
| 3973 | In 15.7 (SP100 and higher), disables 'Transaction (page, rowid) is being rolled back using an optimized scan' message. In 16.0 message is not output by default and this traceflag is used to enable the message. | |
| 3991 | Enable post commit shrink on older versions where is was disabled by default. See KBA 2276464. | |
| 3993 | Disable post commit shrink on newer versions where it is enabled by default.. During normal operations, if a transaction deletes the last entry off of an index leaf page, that page is put in a queue for the Housekeeper to clean up (deallocate). If traceflag 3993 is on, empty pages are not put in the housekeeper queue when the transaction commits. Note that the first leaf page of every index is never deallocated even if it becomes empty. See KBA 2276464. | |
| 4013 | Log the login packet (loginrec) in the ASE errorlog when users log in. | |
| 4016 | Log an informational message to ASE errorlog whenever an interrupt routine is interrupted (UNIX signal SIGURG and SIGALRM interaction) | |
| 4020 | Boot ASE without recovery. Used to access master database system table contents (in a possibly inconsistent state) for purposes such as manually rebuilding a server when recovery of the master database fails and no backups are available. | |
| 4044 | Obsolete as of ~12.5.0.3, still works but superseded by the more general dataserver -u loginname parameter. Allow sa to login in even if account is locked. The dataserver -u parameter works for any login that has sa_role or sso_role. | |
| 4062 | Enable new soundex() behavior. | |
| 4065 | Use alternative atof() function for converting strings to floating point numbers. | |
| 4067 | Disables some checks on floating point values that prevent insertion of non-number values such as INF, Nan, and negative zero. Largely for backward compatibility, by default ASE does not allow such values, nor does the ANSI SQL standard. | |
| 4072 | Boot-time flag. Disable global login trigger. | |
| 4084 | Disable console logging if nonblocking setup fails. See KBA 2022013. | |
| 4086 | Disable correct interpretation of closing brackets for LIKE, enabled under "enable functionality group" configuration setting. | |
| 4805 | Restore pre-407169 behavior i.e. bulk insert is fully logged if the table has indexes or triggers. (CR 407169 "SLOW bcp into heap tables i.e. all cases except APL tables with clustered index, will behave like FAST bcp and do minimally logged updates when the db option 'bulk copy' is turned on." was introduced in ASE 15.0.2 GA.) | |
| 4899 | Enables verification of the row format when data is read by netscan operator (rows formatted by the client) | |
| 5103 | Enables DISK RESIZE to accept a negative size value, causing the device to shrink if there is unused space at the end of the device. Introduced under CR 811919 in 16.0 SP02 PL08 and 16.0 SP03 PL06. | |
| 5304 | In 12.5.0.3 and higher, disables the large io and large allocation changes made under CR 279883-1. Description of CR "Performance enhancements made to SELECT INTO by making use of large I/O and extent allocation for the target table, wherever possible. As a result, SELECT INTO may choose the largest I/O size possible for the target table, based on the size of the buffer pools available in the target database cache. ASE 12.5.0.3 will have this option on by default. ASE 12.0.0.7 requires the use of -T5304 to activate this enhancement." | |
| 5612 | Do not set ASTC_DTX_RESOLVE_PENDING bit during boot-time. Must be used only in extraordinary circumstances when we know what we are doing. This trace flag has been added to provide work-arounds for situations that are otherwise impossible to resolve. Recommended that you only use this on the advice of SAP Product support. | |
| 7167 | Fix uninitialized TIPSA flags when reading text pages. Currently (16.0 SP03 timeframe) it can only clear garbage flag TX_TIPSA_COMPRESSED. | |
| 7120 | Disable the fix of CR 783192 that QPTXTPTR has RID. Currently, it is only used for NULL TXTPTR. See KBA 2878406. | |
| 7403 | Force the server to calculate unreserved pages for all segments at the end of recovery by scanning all the allocation pages. | |
| 7405 | Print a message whenever the status of dbt_threshstat changes. | |
| 7406 | Force the server to calculate the unreserved pages (free space accounting) at the end of recovery even when the serve had been shutdown politely, using newer (and usually faster) OAM page scan method to calculate the unreserved pages | |
| 7408 | Force the server calculate the unreserved pages (free space accounting) in the log segment at the end of recovery even when the serve had been shutdown politely, using a scan of the log segment allocation pages. | |
| 7409 | Force the server calculate the unreserved pages (free space accounting) in the data segment at the end of recovery even when the serve had been shutdown politely, using a scan of *data* segment allocation pages. | |
| 7436 | If a data free space recalculation is needed due to a non polite shutdown (vs using above forcing trace flag 7403, 7406 or 7409), then do not let the system decide which scan method to use; rather always perform a full AP scan. | |
| 7438 | The Allocation Page scan for free space recalculation can be optimized by skipping the read of allocation pages for which GAM bit is set. Use this trace flag to disable this optimization that was introduced by CR 775488. | |
| 7604 | Allows the audit process to skip getting the audit lock in case of running out of locks | |
| 7703 | Turn off CR 210688. Do not protect local variables being on resdom and source/qualification side. WARNING : should not be removed without providing some other way to obtain pre-210688 functionality. Documented behavior for variable assignment in a SELECT is that the variable should be assigned the last value returned. However, prior to the CR 210688, SELECT @x = @x + mycolumn from mytable would result in a sum of all the values in mycolumn being added to @x rather than just the previous value of @x plus the column value from the last row. CR 210688 made ASE behave as documented, this trace flag returns to the previous behavior. | |
| 7717 | When set, turn off large identifier capability checks that normally prevent pre-15.0 clients from connecting to 15.0 and later servers, which allow object names that are longer than the pre-15.0 limit. This trace flag allows such older clients to connect to ASE as they did before. If using this traceflag, do not allow creation of objects with long names if there is any chance of such older clients referencing the objects. The flag would allow the long name to be sent to the pre-15.0 client, which would not be able to handle it properly. | |
| 7719 | Force a temp table to be orphaned for testing that HK Chores garbage collects the table. Also used as a workaround for CR 767295 | |
| 7730 | Switch OFF deferred compilation. Used as a workaround for CR 490570, fixed in 15.0.2 ESD #3 | |
| 7739 | Enable literal parameterization for strings in LIKE patterns and escape clauses | |
| 7741 | Enable the fix for 497118 and 595550 so NULLs sent in host parameters are allowed even with incompatible datatypes and statement cache active. As a side effect, statements with NULL values in host parameterss are not entered in the statement cache. | |
| 7748 | Do not cache parameterized language statements having parameters with numeric values that do not match the parameter format. | |
| 7755 | Can be used with trace flag 214 to log the text of a procedural SQL statement which leads to the recompilation of a procedure. | |
| 7765 | Disable the fix for CR 644994 (" Under some conditions, for example reset or reconfigure 'parallel degree', statement cache clean up issues and memory leaks may occur and it could also make the purge statement cache entry impossible due to ssql_keep > 0.". This traceflag is listed as a possible workaround for CR 802852 (Infected with 11 in s_unsave_kept_stmts), see KBA 2453655 | |
| 7773 | Turn off the fix for CR 653511-1. | |
| 7786 | Do not parameterize literal for large IN LISTs ( > 128) and do not cache such statements. | |
| 7789 | Disable the fix made in CR 723847-1 to handle the cases SELECT INTO and INSERT SELECTs which use UNION statements terminated by UNION ALL. | |
| 7790 | Disable caching of large statements (16k<size<2M) in statement cache. This will help avoid fragmentation due to statements with IN clauses with many elements. . | |
| 7791 | Enable the fix for CRs 497118 and 595550. | |
| 7798 | Ignore 213 error under deferred compilation and revert to normal compilation. Introduced in 15.7 SP130, 16.0 SP01. | |
| 7822 | Disable NETMNE. | |
| 7845 | Suppress printing of TDS invalid header messages. See KBA 2184741 - Meaning of log messages including ksmask__rpacket & Invalid tdslength in ASE | |
| 7858 | Print RSA key pair pool management information | |
| 7862 | Disable feature that disables input checking on a socket which has pending network input, has no read posted and does not allow read ahead | |
| 7867 | Trace SSL handshake errors. See KBA 2633020 | |
| 7869 | Turns off reporting of 1608 errors to the ASE errorlog. New as of 16.0 SP03 PL07. Not available in 15.7 | |
| 8021 | Turns off the fix for CR 414943. This CR modifies the way parameters with a NULL value are passed by RPC to avoid unexpected 16130 errors. | |
| 8101 | Turn off Chargeback Accounting | |
| 8212 | Ignore absence of systabstats rows while getting table DEScriptor for update statistics command. Workaround for Error 15601 "Failed to cache systabstats contents for database 'db_name' (dbid), object 'table_name' (object_id). ". New as of 15.7 ESD 1 | |
| 8217 | Disable Pdes lookup structures handling for performance enhancement | |
| 8391 | Log in monSysSQLText the full text of strings passed to execute immediate. New as of ASE 15.7 SP130. | |
| 8399 | Obsolete as of 12.5.2 due to CR 323550. No longer has any effect. Previously used to populate the field_name column in the sysmonitors table, but this was made default behavior. | |
| 9104 | When enabled the catalogs are no longer accessed using dirty reads (which is the default mechanism). | |
| 9119 | RAT-CI trace flag to disable the auto-scaling and throttling feature for the buffer pool resource in CI. | |
| 9144 | Conditionally collect RAT counters related to execution time. | |
| 9166 | Causes ASE to not store a password in the XREC_BEGINXACT log record, normally done for replication of DDL (data definition language). This trace flag is for use with dbcc logtransfer only. However a task executing DDL doesn't know which scanner, either RAT or dbcc logtransfer, will be reading the log records. If this flag is on, RAT will encounter errors replicating operations such as DDL replication or request functions. | |
| 9199 | Used to activate additional trace outputs in some instrumented dataserver binaries. | |
| 9200 | Trace general Replication Agent execution path (most information traced). | |
| 9201 | Traces LTL commands sent o the Replication-Server. | |
| 9202 | Traces truncation point manipulation. | |
| 9203 | Traces log records encountered during scanning. Display markers and "logop". | |
| 9204 | Traces memory usage of the Replication-Agent thread. | |
| 9205 | Display "Open" transaction in the Replication-Agent thread's transaction context. Continue tracing insertions and deletions to the transaction context. | |
| 9206 | Traces execution of Replication-Agent thread in recovery mode. | |
| 9207 | Forces Replication-Agent thread's schema cache to be flushed. | |
| 9208 | Traces inserts and deletes from Replication-Agent thread's schema cache. | |
| 9209 | Displays all important fields of the Replication-Agent thread's context. | |
| 9210 | Traces when and why Replication-Agent thread sleeps and wakes up. | |
| 9211 | Traces the processing of update statements. Displays the page and row of the delete and insert log records that comprise the update. Displays the before and after images. | |
| 9212 | Traces the processing of the text and image replication. Display text and image context structures and display the page and row of the text and image log records that are processed. | |
| 9213 | Bypass ctlib calls | |
| 9214 | Scan updates using the old forward scan method | |
| 9215 | Trace log records skipped | |
| 9216 | Trace CT/Svr lib errors | |
| 9217 | If set, rat will not stop replication when finding a corrupt log record, instead it will print 9290 error and attempt to continue. | |
| 9240 | Disables the fix for CR 770237 | |
| 9415 | Traceflag to enable CR 586499. On the Solaris platforms, ASE may report "Unexpected AIO error(Invalid argument)returned from aiowait()." when LDAP User Authentication is enabled (configuration option 'enable ldap user auth' is set to 1 or 2) and ASE is performing multiple user authentication simultaneously. This bug can be resolved by installing patch for Solaris BugID 6972029 (contact Oracle for the OS patch, sun sparc: 144190-03, sun x64 144191-02). Alternatively, as a workaround, the issue can be resolved in ASE by enabling boot time trace flag 9415. | |
| 9526 | Dumps Lava operator execution statistics and resource use in a showplan-style format at the most basic level. | |
| 10301 | Print parameter values in in PCR tree. Used to check that the parameters have been correctly added to the tree. | |
| 10303 | Invoke set completion of object ownership methodology and object resolution from old procedural object ownership. (Not recommended). | |
| 11007 | Disable fix for CR 777215 if it is on. | |
| 11011 | With this traceflag ON, we revert to the older approach for handling autoparam with numeric datatypes in INSERT queries, which can use either a numeric (38,19) or a numeric(19,9). With trace OFF (i.e. the current default behavior) we cast all numeric autoparams to (38,19) Implemented under CR 805165 in ASE 15.7 SP140 and higher, ASE 16.0 SP02 PL06 and higher, ASE 16.0 SP03 and higher. | |
| 11013 | Disables the fix for CR 805165 and enables the fix for CRs 813535 and 812823. (Single cached plan for INSERT and UPDATE queries). | |
| 11014 | Enable bootcount support for renormalization of stored procedures. When ON, procedure referencing temp tables will be renormalized at first eecution after reboot of ASE. | |
| 11021 | Disables fix for CR 767440, causing the isnumeric() function to return 1 for an empty string. Added in 15.7 SP142. | |
| 11022 | Enable rounding of the numeric values in INSERT/UPDATE queries. | |
| 11024 | Used in instrumented EBF for CR 821464; Flag is not active in other releases. | |
| 11025 | Used in instrumented EBF for CR 821464; Flag is not active in other releases. | |
| 11227 | Boot time traceflag 11297 on the local ASE to change error handling in Open Client layer | |
| 11297 | Check if a pointer is null before make an open client library call. | |
| 11826 | Adds numeric IDs to POP operators in SHOWPLAN output when "show normal", "show long", or "XML" options are active. Used to make it easier to track POPs for very complex queries. | |
| 11836 | Use forceplan option to generate initial alternative greedy plan (CR 813639-1). This traceflag only works when alternative greedy option is on. | |
| 11838 | Avoids the generation of the join poplet during alternative greedy AP application phase. Workaround for CR 80608, see KBA 2593791. | |
| 11867 | Disable light-weight permission check for dynamic SQL statements. | |
| 11886 | Output reason why a lava plan is not qualified for being a simplified native access plan (SNAP) candidate or SNAP execution. | |
| 11908 | In 15.7 GA, functionality was added to the REORG COMPACT command to clean up the empty OAM entries reported by error 15082. However, this new functionality requires a new shared table lock. If this traceflag is turned on the new functionality is skipped to avoid taking a shared table lock and the empty OAM entries will not be cleaned up. This traceflag becomes available in15.7 ESD 2 and higher. | |
| "Omni Server / CIS" Traceflags 11201 - 11229 are formally documented in the Component Integration System (CIS) Users Guide | ||
| 11201 | Logs client connect events, disconnect events, and attention events. (global). Events sent to error log only. | |
| 11202 | Logs client language, cursor declare, dynamic prepare, and dynamic execute-immediate text (global). Text sent to error log only. | |
| 11203 | Logs client RPC events (global). Events sent to error log only. | |
| 11204 | Logs all messages routed to client (global). Messages sent to only the error log. | |
| 11205 | Logs all interaction with remote server (global). Interaction sent to error log only. | |
| 11206 | Logs file/directory processing steps. (global) | |
| 11207 | Logs text and image processing. (global) | |
| 11208 | Prevents the create index and drop index statements from being transmitted to a remote server. sysindexes is updated anyway. (spid) | |
| 11209 | Instructs update statistics to obtain only row counts rather than complete distribution statistics, from a remote table. (spid) | |
| 11211 | Prevents the drop table syntax from being forwarded to remote servers if the table was created using the create table at location syntax. | |
| 11212 | Prevents escape on underscores (“_”) in table names. (spid) | |
| 11213 | Prevents generation of column and table constraints. (spid) | |
| 11214 | Disables Component Integration Services recovery at start-up. (global) | |
| 11216 | Disables quickpass. (spid) | |
| 11217 | Disables quickpass. (global) | |
| 11218 | Makes cursors involving Component Integration Services tables updateable by default. | |
| 11220 | Disables constraint checking of remote tables on the local server. This avoids duplicate checking. Setting this trace flag on ensures that queries are not rejected by the quickpass mode because of constraints. (spid) | |
| 11221 | Disables alter table commands to the remote server when ON. This allows users to modify type, length, and nullability of columns in a local table without changing columns in the remote table. Use trace flag 11221 with caution. It may lead to tables that are “out of sync.” (spid) | |
| 11223 | Disables proxy table index creation during create existing table or create proxy_table command execution. If this flag is set on, no index metadata is imported from the remote site referenced by the proxy table, and no indexes for the proxy table are created. This trace flag should be used with care and turned off when no longer necessary. (global) | |
| 11228 | Disables proxy table mapping to RPCs. | |
| 11229 | Instructs Component Integration Services to use pre-Adaptive Server version 12.5.3 methods of gathering statistics data. | |
| 11297 | Enable ctlib api checking. Used at the direction of tech support to generate diagnostics for some issues. | |
| 11299 | Allows connection information to be logged when a connection to a remote server fails. | |
| 12314 | (introduced in 15.7 ESD 2, obsolete as of 15.7 ESD 4). Enables CR 610956 feature that enables caching of dbtables in the PSS structure to reduce contention on the dbt_spin spinlock. This feature was made default behavior in 15.7 ESD 4. | |
| 12315 | (introduced in 15.7 ESD 4) Disable optimization for caching dbtables in user processes. | |
| 12337 | Enable dirty read for insert if the current session isolation level is 0. All the source tables in an INSERT...SELECT... query will be dirty read. | |
| 12604 | Disables suffix compression on CREATE INDEX | |
| 12700 | Enable recovery fault isolation (RFI) module to handle all errors during recovery. | |
| 12806 | Print diagnostic information on in-memory growing column statistics | |
| 12807 | Disable online create index in parallel | |
| 12816 | Disable random temp table name generation. Added in 16.0 SP03 under CR 806272 | |
| 14325 | Provide additional validation for the free offset on APL table, before performing delete or update on the APL page. This validation is done by default on the diagserver binary, but must be turned on with TF 14325 for dataserver. If the validation encounters an issue, a message is output to the errorlog: "<function_name>: Object Id: <number>, Page number: <page number>, offset expected: <offset> offset found: <offset>" | |
| 15307 | Ignore 'set forceplan on' | |
| 15308 | ignore index/parallel/prefetch/LRU-MRU hints | |
| 15314 | Disable the fix for CR 589650 to correct semantic checks for whether a store_index operator is still required when the algorithm has been disabled. | |
| 15316 | If enabled, disables the fix made under CR 569947-1 and "opens up" the original fix 497066 to all ISNULL references. The CR 569947-1 fix was limiting the CR 497066 changes to only the ISNULLs under a SUBQOP transformed predicate under a subquery to avoid behavior regressions. | |
| 15335 | Rewrite the query into an EXISTS query for those tables which are not in referenced in the SELECT list | |
| 15336 | Disable fix for 497066, which infers the nullability of isnull() builtin by looking at the nullability of its parameterss. | |
| 15340 | Enables server wide, no matter advanced_aggregation (new in 15.0.2) | |
| 15341 | Disables server wide, no matter advanced_aggregation (new in 15.0.2) | |
| 15349 | Activate the fix for CR 767390 "There may be a performance difference between pre SAP ASE 15.0 servers and post SAP ASE 15.0 servers due to the evaluation order of multiple subqueries on the same operator. Optcriteria cr767390 was introduced to order the subquery evaluation, when evaluated on the same operator, in the order that they were written in the query." This fix was introduced in 15.7 SP131 around 2014. | |
| 15353 | Turn off intelligent index scan costing. | |
| 15363 | (server level) or set alternative_greedy_search off at session level | |
| 15381 | Suppress warning message regarding the ignoring of hints when using abstract plans. (introduced in 15.7 ESD 4.2, 15.7 SP100, and 16.0 GA) | |
| 15391 | If enabled, turn off internal heap inside OptPool (pheap_cpp) so some memory will not be reused during the optimization process. | |
| 15392 | If enabled do not add partial blocks (less than a full mempage) to the internal optimizer heap (pheap_cpp) | |
| 15393 | Turn off psuedo scan generation for large-IN lists during lop generation phase. | |
| 16912 | (introduced in 15.0.3 ESD 4, 15.5 ESD 2, and 15.7 GA). Turn on fix for CR 628845 to avoid table scans/sorts in parallel when top level bmo sorts can be consumed by an order by. | |
| 16939 | (introduced in 16.0 SP02 PL05). When enabled, suppresses showplan output when executing system stored procedures (procedures from either the master or sybystemprocs databases with a name that starts with "sp_" or "xp_". | |
| 16944 | Enable parameterized sql text in abstract plan load and dump. Parameterized SQL text with its abstract plan will be saved in sysqueryplans. For example: SQL text is: "select count(*) from sysobjects where id > 10". Parameterized text is: "select count(*) from sysobjects where id > @@@V0_INT". All SQLs with text "select count(*) from sysobjects where id > " will now share the same abstract plan in sysqueryplans. . (This traceflag was implemented under CR 779357 in 16.0 SP01 PL03 and higher, 16.0 SP02 PL03 and higher, 16.0 SP03 GA and higher.). | |
| 16948 | Disable the fix for CR 795426 "The built-in function REPLICATE (char_expr | uchar_expr, integer_expr) may return an incorrect result when turning TF 244 off and integer_expr is a constant expression." | |
| 16953 | Enables fix for CR 671437-1 which corrects over optimistic costing of serial query plans. Introduced in 15.5 ESD 5. | |
| 16967 | Disable CR 752225 change to allow alternative greedy algorithm to process queries with greater than 32 GROUP BY columns. See KBA 1991717 "Crash when SQL has many columns in its GROUP BY clause." | |
| 16976 | When enabled, will disable the removal of filter predicate for special OR scans implemented under CR696676-1. | |
| 16987 | Disable 669847 change to fix missing EXISTS requirement on producer lop in non-functional update. | |
| 16988 | Enable like optimization for patterns containing more than one parameterized value or variable ("? + ? pattern").(Introduced in 15.7 SP111 under CR 757177). See KBA 2854142 | |
| 17013 | Don't output parameters when printing sql text. Traceflag 17013 may be tried as a workaround to issues causing a stack trace containing function prE_PARAMs_buf(). Introduced in 16.0 SP03 PL12 and 16.0 SP04 PL03 under CR 825970. See KBA 3134686. | |
| 17014 | Disable CR 825859 "SAP Adaptive Server is enhanced on Linux platform, to support booting with variable percentages of available huge page memory and remaining regular page memory. |
- 15355
- 16872
More commonly used traceflags that aren't formally documented. Unsupported trace flagsTrace flags influence Adaptive Server behavior or correct issues. However, some trace flags display unexpected behavior, and all trace flags require caution. Many trace flags are specific to Adaptive Server release 12.5.x and are not necessary for Adaptive Server 15.0. Table 4-1 describes some of the commonly used trace flags and their relevance to Adaptive Server 15.0. Before you discontinue using a trace flag, check with Sybase Technical Support, because the trace flag may be used for purposes other than those listed here.
Table 4-1: Commonly usedtraceflagsTraceflag
Description
In 12.5.x?
In 15.0?
Additional remarks
| 291 | When enabled, predicates using the form col1 <relop> fn(col2)--where the datatype of col2 is listed higher than that of col1--cast the expression f(col2) to the datatype of the lower type (in this case, col1). | Yes | No | Functionality included in Adaptive Server 15.0 |
| 333 | Disables min-max optimization. | Yes | No | No longer supported |
| 364 | Uses range density instead of total density. | Yes | No | No longer supported |
| 370 | Uses min-max index as an alternative to the table scan for single-table queries. Does not perform aggregation optimization for joins. | Yes | No | No longer supported |
| 396 | Uses min-max optimization for single-table queries. | Yes | No | No longer supported |
| 526 | Prints semi-graphical execution operator tree when showplan is enabled. | No | Yes | Functionality included in Adaptive Server 15.0 |
Adaptive Server 15.0 discontinues the 300 series diagnostic trace flags (302, 305, 308, 310, 311, and so on), and these will be deprecated in a future release. They are replaced with showplan options, which provide better diagnostic and more readable output.For information about tools for investigating optimizer decisions, see Query Processing in Adaptive Server.