存档
转自：http://blog.csdn.net/obuntu/archive/2010/06/08/5655494.aspx
谢谢obuntu
| flag | Trace Flag Description (underlined are sp_configure’able) |
| -1 | Sets trace flags for all connections. Used only with DBCC TRACEON and TRACEOFF. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems. |
| 105 | SQL Server 6.5 you can use maximum 16 tables or subqueries in a single select statement. There is no documented way, to avoid this restriction, but you can use undocumented trace flag 105 for this purpose. |
| 106 | Disables line number information for syntax errors. |
| 107 | Interprets numbers with a decimal point as float instead of decimal. |
| 110 | Turns off ANSI select characteristics. |
| 204 | A backward compatibility switch that enables non-ansi standard behavior. E.g. previously SQL server ignored trailing blanks in the like statement and allowed queries that contained aggregated functions to have items in the group by clause that were not in the select list. |
| 205 | Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat. |
| 206 | Provides backward compatibility for the setuser statement. |
| 208 | SET QUOTED IDENTIFIER ON. |
| 237 | Tells SQL Server to use correlated sub-queries in Non-ANSI standard backward compatibility mode. |
| 242 | Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired. |
| 243 | The behavior of SQL Server is now more consistent because null ability checks are made at run time and a null ability violation results in the command terminating and the batch or transaction process continuing. |
| 244 | Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multi-row UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables. |
| 246 | Derived or NULL columns must be explicitly named in a select….INTO or create view statement when not done they raise an error. This flag avoids that. |
| 253 | Prevents ad-hoc query plans to stay in cache. |
| 257 | Will invoke a print algorithm on the XML output before returning it to make the XML result more readable. |
在sql server 2000的错误日志文件中出现如下的错误：
2010-08-05 09:21:51.31 spid11 错误: 823，严重度: 24，状态: 2
2010-08-05 09:21:51.31 spid11 I/O error (torn page) detected during read at offset 0x0000116496a000 in file 'd:\Microsoft SQL Server\MSSQL\data\xxxxxxx_Data.MDF'.
======================================================
Description&Solution:
Torn_page_detection:
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.
Note Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent.
By default, TORN_PAGE_DETECTION is ON.