Bible — SQL Server Trace Flags Lookup

Here is the com­pre­hens­ive list of most trace­flags known. 🙂

flag Trace Flag Descrip­tion (under­lined are sp_configure’able)
‑1 Sets trace flags for all con­nec­tions. Used only with DBCC TRACEON and TRACEOFF. The set­ting of the Trace flag ‑1 is not vis­ible with DBCC TRACESTATUS com­mand, but work without problems.
105 SQL Serv­er 6.5 you can use max­im­um 16 tables or sub­quer­ies in a single select state­ment. There is no doc­u­mented way, to avoid this restric­tion, but you can use undoc­u­mented trace flag 105 for this purpose.
106 Dis­ables line num­ber inform­a­tion for syn­tax errors.
107 Inter­prets num­bers with a decim­al point as float instead of decimal.
110 Turns off ANSI select characteristics.
204 A back­ward com­pat­ib­il­ity switch that enables non-ansi stand­ard beha­vi­or. E.g. pre­vi­ously SQL serv­er ignored trail­ing blanks in the like state­ment and allowed quer­ies that con­tained aggreg­ated func­tions to have items in the group by clause that were not in the select list.
205 Report when a stat­ist­ics-depend­ent stored pro­ced­ure is being recom­piled as a res­ult of AutoStat.
206 Provides back­ward com­pat­ib­il­ity for the setuser statement.
237 Tells SQL Serv­er to use cor­rel­ated sub-quer­ies in Non-ANSI stand­ard back­ward com­pat­ib­il­ity mode.
242 Provides back­ward com­pat­ib­il­ity for cor­rel­ated sub­quer­ies where non-ANSI-stand­ard res­ults are desired.
243 The beha­vi­or of SQL Serv­er is now more con­sist­ent because null abil­ity checks are made at run time and a null abil­ity viol­a­tion res­ults in the com­mand ter­min­at­ing and the batch or trans­ac­tion pro­cess continuing.
244 Dis­ables check­ing for allowed inter­im con­straint viol­a­tions. By default, SQL Serv­er checks for and allows inter­im con­straint viol­a­tions. An inter­im con­straint viol­a­tion is caused by a change that removes the viol­a­tion such that the con­straint is met, all with­in a single state­ment and trans­ac­tion. SQL Serv­er checks for inter­im con­straint viol­a­tions for self-ref­er­en­cing DELETE state­ments, INSERT, and multi-row UPDATE state­ments. This check­ing requires more work tables. With this trace flag you can dis­al­low inter­im con­straint viol­a­tions, thus requir­ing few­er work tables.
246 Derived or NULL columns must be expli­citly named in a select….INTO or cre­ate view state­ment when not done they raise an error. This flag avoids that.
253 Pre­vents ad-hoc query plans to stay in cache.
257 Will invoke a print algorithm on the XML out­put before return­ing it to make the XML res­ult more readable.
260 Prints ver­sion­ing inform­a­tion about exten­ded stored pro­ced­ure dynam­ic-link lib­rar­ies (DLLs). For more inform­a­tion about _​_​GetXpVersion(), see Cre­at­ing Exten­ded Stored Pro­ced­ures. Scope: glob­al or session
262 SQL 7 — Trail­ing spaces are no longer trun­cated from lit­er­al strings in CASE state­ments. Used after hot­fix 891116
302 Should be used with flag 310 to show the actu­al join order­ing. Prints inform­a­tion about wheth­er the stat­ist­ics page is used, the actu­al selectiv­ity (if avail­able), and what SQL Serv­er estim­ated the phys­ic­al and logic­al I/​O would be for the indexes.
310 Prints inform­a­tion about join order. Index selec­tion inform­a­tion is also avail­able in a more read­able format using SET SHOWPLAN_​ALL, as described in the SET statement.
320 Dis­ables join-order heur­ist­ics used in ANSI joins. To see join-order heur­ist­ics use flag 310. SQL Serv­er uses join-order heur­ist­ics to reduce the no’ of per­muta­tions when using the best join order.
323 Reports on the use of update state­ments using UPDATE in place. Shows a detailed descrip­tion of the vari­ous update meth­ods used by SQL Serv­er 6.5.
325 Prints inform­a­tion about the cost of using a non-clustered index or a sort to pro­cess an ORDER BY clause.
326 Prints inform­a­tion about the estim­ated & actu­al costs of sorts. Instructs the serv­er to use arith­met­ic aver­aging when cal­cu­lat­ing dens­ity instead of a geo­met­ric weighted aver­age when updat­ing stat­ist­ics.  Use­ful for build­ing bet­ter stats when an index has skew on the lead­ing column.  Use only for updat­ing the stats of a table/​index with known skewed data.
330 Enables full out­put when using the SET SHOWPLAN_​ALL option, which gives detailed inform­a­tion about joins.
342 Dis­ables the cost­ing of pseudo-merge joins, thus sig­ni­fic­antly redu­cing time spent on the parse for cer­tain types of large, multi-table joins. One can also use SET FORCEPLAN ON to dis­able the cost­ing of pseudo-merge joins because the query is forced to use the order spe­cified in the FROM clause.
345 Increase the accur­acy of choice of optim­um order when you join 6 or more tables.
506 Enforces SQL-92 stand­ards regard­ing null val­ues for com­par­is­ons between vari­ables and para­met­ers. Any com­par­is­on of vari­ables and para­met­ers that con­tain a NULL always res­ults in a NULL.
610 SQL 10 – Enable the poten­tial for min­im­al-log­ging when:

·   Bulk load­ing into an empty clustered index, with no non­clustered indexes

·   Bulk load­ing into a non-empty heap, with no non­clustered indexes

611 After SQL 9 when turned on, each lock escal­a­tion is recor­ded in the SQL Serv­er error log along with the SQL Serv­er handle number.
652 Dis­ables read ahead for the server.
653 Dis­ables read ahead for the cur­rent connection.
661 Dis­ables the ghost record remov­al pro­cess. A ghost record is the res­ult of a delete oper­a­tion. When you delete a record, the deleted record is kept as a ghost record. Later, the deleted record is purged by the ghost record remov­al pro­cess. When you dis­able this pro­cess, the deleted record is not purged. There­fore, the space that the deleted record con­sumes is not freed. This beha­vi­or affects space con­sump­tion and the per­form­ance of scan oper­a­tions. SCOPE: Glob­al. If you turn off this trace flag, the ghost record remov­al pro­cess works correctly.
806 Cause ‘DBCC-style’ page audit­ing to be per­formed whenev­er a data­base page is read into the buf­fer pool. This is use­ful to catch cases where pages are being cor­rup­ted in memory and then writ­ten out to disk with a new page check­sum. When they’re read back in the check­sum will look cor­rect, but the page is cor­rupt (because of the pre­vi­ous memory cor­rup­tion). This page audit­ing goes some­way to catch­ing this — espe­cially on non-Enter­prise Edi­tion sys­tems that don’t have the ‘check­sum sniffer’.
809 SQL 8 – Lim­its the amount of Lazy Write activity.
815 Enables latch enforce­ment. SQL Serv­er 8 (with ser­vice pack 4) and SQL Serv­er 9 can per­form latch enforce­ment for data pages found in the buf­fer pool cache. Latch enforce­ment changes the vir­tu­al memory pro­tec­tion state while data­base page status changes from “clean” to “dirty” (“dirty” means mod­i­fied through INSERT, UPDATE or DELETE oper­a­tion). If an attempt is made to modi­fy a data page while latch enforce­ment is set, it causes an excep­tion and cre­ates a mini-dump in SQL Serv­er install­a­tion’s LOG dir­ect­ory. Microsoft sup­port can exam­ine the con­tents of such mini-dump to determ­ine the cause of the excep­tion. In order to modi­fy the data page the con­nec­tion must first acquire a modi­fic­a­tion latch. Once the data modi­fic­a­tion latch is acquired the page pro­tec­tion is changed to read-write. Once the modi­fic­a­tion latch is released the page pro­tec­tion changes back to read-only.
818 SQL 8 enables in memory ring buf­fer used to track last 2048 suc­cess­ful write operations.
830 SQL 9 – dis­able the report­ing of CPU Drift errors in the SQL Serv­er error­log like SQL Serv­er has encountered 2 occurrence(s) of I/​O requests tak­ing longer than 15 seconds to complete
834 Causes SQL Serv­er to use Win­dows large-page alloc­a­tions for the memory that is alloc­ated for the buf­fer pool. The page size var­ies depend­ing on the hard­ware plat­form, but the page size may be from 2 MB to 16 MB. Large pages are alloc­ated at star­tup and are kept through­out the life­time of the pro­cess. Trace flag 834 improves per­form­ance by increas­ing the effi­ciency of the trans­la­tion look-aside buf­fer (TLB) in the CPU.
Flag 834 applies only to 64-bit ver­sions of SQL Serv­er. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.
Trace flag 834 may pre­vent the serv­er from start­ing if memory is frag­men­ted and if large pages can­not be alloc­ated. There­fore, trace flag 834 is best suited for serv­ers that are ded­ic­ated to SQL Server.
For more inform­a­tion about large-page sup­port, http://​msdn2​.microsoft​.com/​e​n​-​u​s​/​l​i​b​r​a​r​y​/​a​a​3​6​6​7​2​0​.​a​spx(http://​msdn2​.microsoft​.com/​e​n​-​u​s​/​l​i​b​r​a​r​y​/​a​a​3​6​6​7​2​0​.​a​spx)
835 SQL 9 & 10. For 64 bit SQL Serv­er. This turns off Lock pages in memory.
836 Causes SQL Serv­er to size the buf­fer pool at star­tup based on the value of the max serv­er memory option instead of based on the total phys­ic­al memory. You can use trace flag 836 to reduce the num­ber of buf­fer descriptors that are alloc­ated at star­tup in 32-bit Address Win­dow­ing Exten­sions (AWE) mode.
Trace flag 836 applies only to 32-bit ver­sions of SQL Serv­er that have the AWE alloc­a­tion enabled. You can turn on trace flag 836 only at startup.
845 SQL 9 & 10. For 64 bit SQL Serv­er. This turns on Lock pages in memory.
1117 Grows all data files at once, else it goes in turns.
1118 Switches alloc­a­tions in tem­p­DB from 1pg at a time (for first 8 pages) to one extent. There is now a cache of temp tables. When a new temp table is cre­ated on a cold sys­tem it uses the same mech­an­ism as for SQL 8. When it is dropped though, instead of all the pages being deal­loc­ated com­pletely, one IAM page & one data page are left alloc­ated, then the temp table is put into a spe­cial cache. Sub­sequent temp table cre­ations will look in the cache to see if they can just grab a pre-cre­ated temp table. If so, this avoids access­ing the alloc­a­tion bit­maps com­pletely. The temp table cache isn’t huge (32 tables), but this can still lead to a big drop in latch con­ten­tion in tem­p­db. http://​www​.sql​skills​.com/​B​L​O​G​S​/​P​A​U​L​/​p​o​s​t​/​M​i​s​c​o​n​c​e​p​t​i​o​n​s​-​a​r​o​u​n​d​-​T​F​-​1​1​1​8​.​a​spx
1180 Forces alloc­a­tion to use free pages for text or image data and main­tain effi­ciency of stor­age. 1197 applies only in the case of SQL 7 – SP3. Help­ful in case when DBCC SHRINKFILE and SHRINKDATABASE com­mands may not work because of sparsely pop­u­lated text, ntext, or image columns
1200 Prints lock inform­a­tion (the pro­cess ID and type of lock requested).
1202 Insert blocked lock requests into syslocks.
1204 Returns resources and types of locks par­ti­cip­at­ing in a dead­lock and com­mand affected. Scope: glob­al only
1205 More detailed inform­a­tion about the com­mand being executed at the time of a dead­lock. This trace flag was doc­u­mented in SQL Serv­er 7.0 Books Online, but was not doc­u­mented in SQL Serv­er 8.
1206 Used to com­ple­ment flag 1204 by dis­play­ing oth­er locks held by dead­lock parties
1211 Dis­ables lock escal­a­tion based on memory pres­sure, or based on num­ber of locks. The SQL Serv­er Data­base Engine will not escal­ate row or page locks to table locks.

Using this trace flag can gen­er­ate excess­ive num­bers of locks. This can slow the per­form­ance of the Data­base Engine, or cause 1204 errors (unable to alloc­ate lock resource) because of insuf­fi­cient memory. For more inform­a­tion, see Lock Escal­a­tion (Data­base Engine).

If both trace flag 1211 and 1224 are set, 1211 takes pre­ced­ence over 1224. How­ever, because trace flag 1211 pre­vents escal­a­tion in every case, even under memory pres­sure, we recom­mend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used. Scope: glob­al or session

1216 SQL 7 — Dis­ables Health report­ing. Lock mon­it­or when detects a (work­er thread) resource level block­ing scen­ario. If a SPID that owns a lock is cur­rently queued to the sched­uler, because all the assigned work­er threads have been cre­ated and all the assigned work­er threads are in an un-resolv­able wait state, the fol­low­ing error mes­sage is writ­ten to the SQL Serv­er error log:

Error 1223: Pro­cess ID %d:%d can­not acquire lock “%s” on resource %s because a poten­tial dead­lock exists on Sched­uler %d for the resource. Pro­cess ID %d:% d holds a lock “%h” on this resource.

1222 Returns the resources and types of locks that are par­ti­cip­at­ing in a dead­lock and also the cur­rent com­mand affected, in an XML format that does not com­ply with any XSD schema. Scope: glob­al only
1224 Dis­ables lock escal­a­tion based on the num­ber of locks. How­ever, memory pres­sure can still activ­ate lock escal­a­tion. The Data­base Engine escal­ates row or page locks to table (or par­ti­tion) locks if the amount of memory used by lock objects exceeds one of the fol­low­ing conditions:

·   40% of the memory that is used by Db Engine, exclus­ive of memory alloc­a­tion using Address Win­dow­ing Exten­sion (AWE). This is applic­able when the locks para­met­er of sp_​configure is set to 0.

·   Forty per­cent of the lock memory that is con­figured by using the locks para­met­er of sp_​configure.

If both trace flag 1211 and 1224 are set, 1211 takes pre­ced­ence over 1224. How­ever, because trace flag 1211 pre­vents escal­a­tion in every case, even under memory pres­sure, we recom­mend that you use 1224. This helps avoid “out-of-locks” errors when many locks are being used.

Note:Lock escal­a­tion to the table- or HoBT-level gran­u­lar­ity can also be con­trolled by using the LOCK_​ESCALATION option of the ALTER TABLE state­ment.  Scope:glob­al or session

1261 SQL 8 — Dis­ables Health report­ing. Lock mon­it­or when detects a (work­er thread) resource level block­ing scen­ario. If a SPID that owns a lock is cur­rently queued to the sched­uler, because all the assigned work­er threads have been cre­ated and all the assigned work­er threads are in an un-resolv­able wait state, the fol­low­ing error mes­sage is writ­ten to the SQL Serv­er error log:

Error 1229: Pro­cess ID %d:%d owns resources that are block­ing pro­cesses on sched­uler %d.

1400 Enables the cre­ation of the data­base mir­ror­ing end­point, which is required for set­ting up and using data­base mir­ror­ing. This trace flag is allowed only when using the –T.
1462 Turns off log stream com­pres­sion and effect­ively reverts the beha­vi­or back to ver 9.
1603 Use stand­ard disk I/​O (i.e. turn off asyn­chron­ous I/​O).
1609 Turns on the unpack­ing and check­ing of remote pro­ced­ure call (RPC) inform­a­tion in Open Data Ser­vices. Used only when applic­a­tions depend on the old behavior.
1610 Boot the SQL data­serv­er with TCP_​NODELAY enabled.
1611 If pos­sible, pin shared memory — check error­log for success/​failure.
1704 Prints inform­a­tion when a tem­por­ary table is cre­ated or dropped.
1717 Causes new objects being cre­ated to be sys­tem objects.
1806 Dis­ables instant file initialization.
1807 Allows cre­at­ing a data­base file on a mapped or UNC net­work loc­a­tion. unsup­por­ted under SQL Serv­er 7 & 8.
2301 Enables advanced optim­iz­a­tions that are spe­cif­ic to decision sup­port quer­ies. This option applies to decision sup­port pro­cessing of large data sets.
2330 Stops the col­lec­tion of stat­ist­ics for sys.db_index_usage_stats.
2382 Stat­ist­ics col­lec­ted for sys­tem tables.
2389 SQL 9 – Tracks the nature of columns by sub­sequent stat­ist­ics updates. When SQL Serv­er determ­ines that the stat­ist­ics increase three times, the column is branded ascend­ing. The stat­ist­ics will be updated auto­mat­ic­ally at query compile.
2390 Does the same like 2389 even if ascend­ing nature of the column is not known and — nev­er enable without 2389.
2440 Par­al­lel query exe­cu­tion strategy on par­ti­tioned tables. SQL 9 – uses a single thread per par­ti­tion par­al­lel query exe­cu­tion strategy. In ver. 10, mul­tiple threads can be alloc­ated to a single par­ti­tion, thus improv­ing the query’s response time.
2505 Pre­vents DBCC TRACEON 208, SPID 10 errors from appear­ing in the error log.
2508 Dis­ables par­al­lel non-clustered index check­ing for DBCC CHECKTABLE.
2509 Used with DBCC CHECKTABLE.html to see the total count of ghost records in a table
2520 Force DBCC HELP to return syn­tax of undoc­u­mented DBCC state­ments. If 2520 is not turned on, DBCC HELP will refuse to give you the syn­tax stat­ing: “No help avail­able for DBCC state­ment ‘undoc­u­mented statement’ ”.
2528 Dis­ables par­al­lel check­ing of objects by DBCC CHECKDB, CHECKFILEGROUP and CHECKTABLE. By default, the degree of par­al­lel­ism is auto­mat­ic­ally determ­ined by the query pro­cessor. The max­im­um degree of par­al­lel­ism is con­figured just like that of par­al­lel quer­ies. For more inform­a­tion, see max degree of par­al­lel­ism Option.

Par­al­lel DBCC should typ­ic­ally be left enabled. For DBCC CHECKDB, the query pro­cessor ree­valu­ates and auto­mat­ic­ally adjusts par­al­lel­ism with each table or batch of tables checked. Some­times, check­ing may start when the serv­er is almost idle. An admin­is­trat­or who knows that the load will increase before check­ing is com­plete may want to manu­ally decrease or dis­able parallelism.

Dis­abling par­al­lel check­ing of DBCC can cause it to take much longer to com­plete and if DBCC is run with the TABLOCK fea­ture enabled and par­al­lel­ism set off, tables may be locked for longer peri­ods of time.

Scope: glob­al or session

2537 SQL 9 & 10. Allows func­tion ::fn_​dblog to look inside all logs (not just the act­ive log).
2542 SQL 8 – Used with Sqldumper.exe to get cer­tain dumps. In range 254x – 255x.
2551 Adds addi­tion­al inform­a­tion to the dump file.
2701 Sets the @@ERROR sys­tem func­tion to 50000 for RAISERROR mes­sages with sever­ity levels of 10 or less. When dis­abled, sets the @@ERROR sys­tem func­tion to 0 for RAISERROR mes­sages with sever­ity levels of 10 or less.
2861 Cache query plans for quer­ies that have a cost of zero or near to zero.
3001 Stops send­ing backup entries into MSDB.
3004 Gives out more detailed inform­a­tion about restore & backup activities.
3031 SQL 9 — will turn the NO_​LOG and TRUNCATE_​ONLY options into check­points in all recov­ery modes.
3104 Causes SQL Serv­er to bypass check­ing for free space.
3111 Cause LogMgr::ValidateBackedupBlock to be skipped dur­ing backup and restore operations.
3205 If a tape drive sup­ports hard­ware com­pres­sion, either the DUMP or BACKUP state­ment uses it. With this trace flag, you can dis­able hard­ware com­pres­sion for tape drivers. This is use­ful when you want to exchange tapes with oth­er sites or tape drives that do not sup­port com­pres­sion. Scope: glob­al or session
3213 Trace SQL Serv­er activ­ity dur­ing backup pro­cess so that we will come to know which part of backup pro­cess is tak­ing more time.
3222 Dis­ables the read ahead that is used by the recov­ery oper­a­tion dur­ing roll for­ward operations.
3226 With this trace flag, you can sup­press BACKUP COMPLETED log entries going to WIN and SQL logs.
3231 SQL 8 & 9 — will turn the NO_​LOG and TRUNCATE_​ONLY options into no-ops in FULL/​BULK_​LOGGED recov­ery mode, and will clear the log in SIMPLE recov­ery mode.
3282 SQL 6.5 – Used after backup res­tor­a­tion fails refer to microsoft for art­icle Q215458.
3422 Cause audit­ing of trans­ac­tion log records as they’re read (dur­ing trans­ac­tion roll­back or log recov­ery). This is use­ful because there is no equi­val­ent to page check­sums for trans­ac­tion log records and so no way to detect wheth­er log records are being cor­rup­ted e care­ful with these trace flags — I don’t recom­mend using them unless you are exper­i­en­cing cor­rup­tions that you can­’t dia­gnose. Turn­ing them on will cause a big CPU hit because of the extra audit­ing that’s happening.
3502 Tracks CHECKPOINT — Prints a mes­sage to the log at the start and end of each checkpoint.
3503 Indic­ates wheth­er the check­point at the end of auto­mat­ic recov­ery was skipped for a data­base (this applies only to read-only data­bases).
3504 For intern­al test­ing. Will raise a bogus log-out-of-space con­di­tion from checkpoint()
3505 Dis­ables auto­mat­ic check­points. May increase recov­ery time and can pre­vent log space reuse until the next check­point is issued. Make sure to issue manu­al check­points on all read/​write data­bases at appro­pri­ate time intervals.
Note does not pre­vent the intern­al check­points that are issued by cer­tain com­mands, such as BACKUP.
3601 Stack trace when error raised. Also see 3603
3602 Records all error and warn­ing mes­sages sent to the client.
3603 SQL Serv­er fails to install on tri­core, Bypass SMT check is enabled, flags are added via registry. Also see 3601.
3604 Sends trace out­put to the cli­ent. This trace flag is used only when set­ting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605 Sends trace out­put to the error log.  (if SQL Serv­er is star­ted from CMD out­put also appears on the screen)
3607 Trace flag 3607 skips the recov­ery of data­bases on the star­tup of SQL Serv­er and clears the Tem­p­DB. Set­ting this flag lets you get past cer­tain crashes, but there is a chance that some data will be lost
3608 Pre­vents SQL Serv­er from auto­mat­ic­ally start­ing and recov­er­ing any data­base except the mas­ter data­base. Data­bases will be star­ted and recovered when accessed. Some fea­tures, such as snap­shot isol­a­tion and read com­mit­ted snap­shot, might not work.
3609 Skips the cre­ation of the tem­p­db data­base at star­tup. Use this trace flag if the device or devices on which tem­p­db resides are prob­lem­at­ic or prob­lems exist in the mod­el database.
3610 SQL 9. Divide by zero to res­ult in NULL instead of error.
3625 Lim­its the amount of inform­a­tion returned in error mes­sages. For more inform­a­tion, see Metadata Vis­ib­il­ity Con­fig­ur­a­tion. Scope: glob­al only
3626 Turns on track­ing of the CPU data for the sys­pro­cesses table.
3640 Elim­in­ates the send­ing of DONE_​IN_​PROC mes­sages to the cli­ent for each state­ment in a stored pro­ced­ure. This is sim­il­ar to the ses­sion set­ting of SET NOCOUNT ON, but when set as a trace flag, every cli­ent ses­sion is handled this way.
3689 Logs exten­ded errors to error­log when net­work dis­con­nect occurs, turned off by default. Will dump out the sock­et error code this can some­times give you a clue as to the root cause.
3913 SQL 7/​8 – SQL Serv­er does not update the row­cnt column of the sys­in­dexes sys­tem table until the trans­ac­tion is com­mit­ted. When turned on the optim­izer gets row count inform­a­tion from in-memory metadata that is saved to sys­in­dexes sys­tem table when the trans­ac­tion commits.
4013 This trace flag writes an entry to the SQL Serv­er error log when a new con­nec­tion is estab­lished. For each con­nec­tion that occurs, the trace flag writes two entries that look like this:

Login: sa saSQL Query Analyzer(local)ODBCmaster, serv­er pro­cess ID (SPID): 57, ker­nel pro­cess ID (KPID): 57.
Login: sa XANADUsaSQL Query Analyzer(local)ODBCmaster, serv­er pro­cess ID (SPID): 57, ker­nel pro­cess ID (KPID): 57.

4022 If turns on, then auto­mat­ic­ally star­ted pro­ced­ures will be bypassed.
4029 Logs exten­ded errors to error­log when net­work dis­con­nect occurs, turned off by default. Will dump out the sock­et error code this can some­times give you a clue as to the root cause.
4030 Prints both a byte and ASCII rep­res­ent­a­tion of the receive buf­fer. Used when you want to see what quer­ies a cli­ent is send­ing to SQL Serv­er. You can use this trace flag if you exper­i­ence a pro­tec­tion viol­a­tion and want to determ­ine which state­ment caused it. Typ­ic­ally, you can set this flag glob­ally or use SQL Serv­er Enter­prise Man­ager. You can also use DBCC INPUTBUFFER.
4031 Prints both a byte and ASCII rep­res­ent­a­tion of the send buf­fers (what SQL Serv­er sends back to the cli­ent). You can also use DBCC OUTPUTBUFFER.
4032 Traces the SQL com­mands com­ing in from the cli­ent. The out­put des­tin­a­tion of the trace flag is con­trolled with the 3605/​3604 trace flags.
4101 SQL 9 — Query that involves an out­er join oper­a­tion runs very slowly. How­ever, if you use the FORCE ORDER query hint in the query, the query runs much faster. Addi­tion­ally, the exe­cu­tion plan of the query con­tains the fol­low­ing text in theWarn­ings column:  NO JOIN PREDICATE

Turn these trace flags after HOTFIX is applied (SP2 CUP4)

4606 Over comes SA pass­word by star­tup. Refer to Ms art­icle 936892.
4612 Dis­able the ring buf­fer log­ging — no new entries will be made into the ring buffer.
4613 Gen­er­ate a min­idump file whenev­er an entry is logged into the ring buffer.
4616 Makes serv­er-level metadata vis­ible to applic­a­tion roles. In SQL Serv­er, an applic­a­tion role can­not access metadata out­side its own data­base because applic­a­tion roles are not asso­ci­ated with a serv­er-level prin­cip­al. This is a change of beha­vi­or from earli­er ver­sions of SQL Serv­er. Set­ting this glob­al flag dis­ables the new restric­tions, and allows for applic­a­tion roles to access serv­er-level metadata. Scope: glob­al only
5302 Alters default beha­vi­or of select…INTO (and oth­er pro­cesses) that lock sys­tem tables for the dur­a­tion of the trans­ac­tion. This trace flag dis­ables such lock­ing dur­ing an impli­cit transaction.
6527 Dis­ables gen­er­a­tion of a memory dump on the first occur­rence of an out-of-memory excep­tion in CLR integ­ra­tion. By default, SQL Serv­er gen­er­ates a small memory dump on the first occur­rence of an out-of-memory excep­tion in the CLR. The beha­vi­or of the trace flag is as follows:

·   If this is used as a star­tup trace flag, a memory dump is nev­er gen­er­ated. How­ever, a memory dump may be gen­er­ated if oth­er trace flags are used.

·   If this trace flag is enabled on a run­ning serv­er, a memory dump will not be auto­mat­ic­ally gen­er­ated from that point on. How­ever, if a memory dump has already been gen­er­ated due to an out-of-memory excep­tion in the CLR, this trace flag will have no effect. Scope: glob­al only

7103 Dis­able table lock pro­mo­tion for text columns. Refer to Ms art­icle — 230044
7300 Retrieves exten­ded inform­a­tion about any error you encounter when you execute a dis­trib­uted query.
7501 Dynam­ic curs­ors are used by default on for­ward-only curs­ors. Dynam­ic curs­ors are faster than in earli­er ver­sions and no longer require unique indexes. This flag dis­ables the dynam­ic curs­or enhance­ments and reverts to ver­sion 6.0 behavior.
7502 Dis­ables the cach­ing of curs­or plans for exten­ded stored procedures.
7505 Enables ver­sion 6.x hand­ling of return codes when call­ing dbcurs­or­fetch­ex and the res­ult­ing curs­or pos­i­tion fol­lows the end of the curs­or res­ult set.
7525 Reverts to the SQL Serv­er 7 beha­vi­or of clos­ing non­stat­ic curs­ors regard­less of the SET CURSOR_​CLOSE_​ON_​COMMIT state in SQL Serv­er 8.
7601 Turns on full text index­ing. Togeth­er these four gath­er more inform­a­tion about full text search (index­ing pro­cess) to the error log.
7646 SQL 10. Avoids block­ing when using full text index­ing. An issue we exper­i­enced that full text can be slow when there is a high num­ber of updates to the index and is caused by block­ing on the docid­fil­ter intern­al table.
7806 Enables a ded­ic­ated admin­is­trat­or con­nec­tion (DAC) on SQL Svr Express. By default, no DAC resources are reserved on SQL Serv­er Express.
8004 SQL serv­er to cre­ate a mini dump once you enable 2551 and a out of memory con­di­tion is hit.
8011 Dis­ables the col­lec­tion of addi­tion­al dia­gnost­ic inform­a­tion for Resource Mon­it­or. You can use the inform­a­tion in this ring buf­fer to dia­gnose out-of-memory con­di­tions. Scope: GLOBAL.
8012 Records an event in the sched­ule ring buf­fer every time that one of the fol­low­ing events occurs:

·   A sched­uler switches con­text to anoth­er worker.

·   A work­er is sus­pen­ded or resumed.

·   A work­er enters the pree­mpt­ive mode or the non-pree­mpt­ive mode.

You can use the dia­gnost­ic inform­a­tion in this ring buf­fer to ana­lyze schedul­ing prob­lems. For example, you can use the inform­a­tion in this ring buf­fer to troubleshoot prob­lems when SQL Serv­er stops responding.
Trace flag 8012 dis­ables record­ing of events for sched­ulers. You can turn on trace flag 8012 only at startup.

8018 Dis­ables the cre­ation of the ring buf­fer, and no excep­tion inform­a­tion is recor­ded. The excep­tion ring buf­fer records the last 256 excep­tions that are raised on a node. Each record con­tains some inform­a­tion about the error and con­tains a stack trace. A record is added to the ring buf­fer when an excep­tion is raised.
8019 Dis­ables stack col­lec­tion dur­ing the record cre­ation, has no effect if trace flag 8018 is turned on. Dis­abling the excep­tion ring buf­fer makes it more dif­fi­cult to dia­gnose prob­lems that are related to intern­al serv­er errors. You can turn on trace flag 8018 and trace flag 8019 only at startup.
8020 SQL Serv­er uses the size of the work­ing set when SQL Serv­er inter­prets the glob­al memory state sig­nals from the oper­at­ing sys­tem. Trace flag 8020 removes the size of the work­ing set from con­sid­er­a­tion when SQL Serv­er inter­prets the glob­al memory state sig­nals. If you use this trace flag incor­rectly, heavy paging occurs, and the per­form­ance is poor. There­fore, con­tact Microsoft Sup­port before you use. You can turn on trace flag 8020 only at startup
8033 SQL 9 – dis­able the report­ing of CPU Drift errors in the SQL Serv­er error­log like time stamp counter of CPU on sched­uler id 1 is not syn­chron­ized with oth­er CPUs.
8202 Used to rep­lic­ate UPDATE as DELETE/​INSERT pair at the pub­lish­er. i.e. UPDATE com­mands at the pub­lish­er can be run as an “on-page DELETE/​INSERT” or a “full DELETE/​INSERT”. If the UPDATE com­mand is run as an “on-page DELETE/​INSERT,” the Logread­er send UDPATE com­mand to the sub­scriber, If the UPDATE com­mand is run as a “full DELETE/​INSERT,” the Logread­er send UPDATE as DELETE/​INSERT Pair. If you turn on trace flag 8202, then UPDATE com­mands at the pub­lish­er will be always send to the sub­scriber as DELETE/​INSERT pair.
8206 Sup­ports stored pro­ced­ure exe­cu­tion with a user spe­cified own­er name for SQL Serv­er sub­scribers or without own­er qual­i­fic­a­tion for het­ero­gen­eous sub­scribers in SQL Serv­er 8.
8207 Enables singleton updates for Trans­ac­tion­al Rep­lic­a­tion, released with SQL Serv­er 8 SP 1.
8501 Writes detailed inform­a­tion about Ms-DTC con­text & state changes to the log.
8599 Allows you to use a save­point with­in a dis­trib­uted transaction.
8602 Ignore index hints that are spe­cified in query/​procedure.
8679 Pre­vents the SQL Serv­er optim­izer from using a Hash Match Team operator.
8687 Used to dis­able query parallelism.
8721 Dumps inform­a­tion into the error log when Auto­Stat has been run.
8722 Dis­able all oth­er types of hints. This includes the OPTION clause.
8744 Dis­ables pre-fetch­ing for the Nes­ted Loops oper­at­or. Incor­rect use of this trace flag may cause addi­tion­al phys­ic­al reads when SQL Serv­er executes plans that con­tain the Nes­ted Loops oper­at­or. For more inform­a­tion about the Nes­ted Loops oper­at­or, see the “Logic­al and phys­ic­al oper­at­ors ref­er­ence” top­ic in SQL Serv­er 9 BOL.
You can turn on trace flag 8744 at star­tup or in a user ses­sion. When you turn on trace flag 8744 at star­tup, the trace flag has glob­al scope. When you turn on trace flag 8744 in a user ses­sion, the trace flag has ses­sion scope.
8755 Dis­able any lock­ing hints like READONLY. By set­ting this, you allow SQL Serv­er to dynam­ic­ally select the best lock­ing hint for the query.
8783 Allows DELETE, INSERT, and UPDATE state­ments to hon­or the SET ROWCOUNT ON set­ting when enabled.
8816 Logs every two-digit year con­ver­sion to a four-digit year.
9134 SQL 8 – Does addi­tion­al reads to test if the page is alloc­ated & linked cor­rectly this checks IAM & PFS. Fixes error 601 for quer­ies under Isol­a­tion level read uncommitted.
9268 SQL 8 – When SQL Serv­er runs a para­met­er­ized query that con­tains sev­er­al IN clauses, each with a large num­ber of val­ues, SQL Serv­er may return the fol­low­ing error mes­sage after a minute or more of high CPU utilization:

Serv­er: Msg 8623, Level 16, State 1
Intern­al Query Pro­cessor Error: The query pro­cessor could not pro­duce a query plan. Con­tact your primary sup­port pro­vider for more information.

http://​sup​port​.microsoft​.com/​k​b​/​3​2​5​658 Enabling this trace flag activ­ates the hotfix.

No Comments

Post a Comment

Your email is never shared. Required fields are marked *