Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 13, 2000

SQL Server 7.0: Some Useful Trace Flags

By Alexander Chigrik


Introduction
Trace flags
Literature

Introduction

In this article I want to tell you, what you should know about trace flags, and how you can use some useful trace flags in SQL Server 7.0 for administering and monitoring.

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. You can set trace flags with DBCC TRACEON command or with the -T option with the sqlservr command- line executable. After activated, trace flag will be in effect until you restart server, or until you deactivate trace flag with DBCC TRACEOFF command.


Trace flags

You can use DBCC TRACESTATUS command to get the status information for a particular trace flag(s) currently turned on.

This is the syntax from SQL Server Books Online:

DBCC TRACESTATUS (trace# [,...n])

To get the status information for all trace flags currently turned on, you can use - 1 for trace#.

This is the example:

DBCC TRACESTATUS(-1)
GO
You can use DBCC TRACEON command to turn on the specified trace flag. This is the syntax from SQL Server Books Online:

DBCC TRACEON (trace# [,...n])

If you want to turn off the specified trace flag(s), you can use DBCC TRACEOFF command.

This is the syntax from SQL Server Books Online:

DBCC TRACEOFF (trace# [,...n])

1. Trace flag -1 (undocumented).

This trace flag sets trace flags for all client connections, rather than for a single client connection. Is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.

This trace flag was documented in SQL Server 6.5 Books Online, but not documented in SQL Server 7.0.

2. Trace flag 1807 (undocumented).

You cannot create a database file on a mapped or UNC network location. This opportunity is generally unsupported under SQL Server 7.0. You can bypass this by turn on trace flag 1807.

Look here for more details:
INF: Support for Network Database Files

3. Trace flag 2521 (undocumented).

Trace flag 2521 is needed to facilitate capturing a Sqlservr.exe user-mode crash dump for postmortem analysis. NOTE. This trace flag works only on SQL Server 7.0 with service pack 2 or later.

4. Trace flag 3604.

Trace flag 3604 sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.

5. Trace flag 3608 (undocumented).

This trace flag skips automatic recovery (at startup) for all databases except the master database.

Trace flag 3608 was documented in SQL Server 6.5 Books Online, but not documented in SQL Server 7.0.

6. Trace flag 3222 (undocumented).

If you drop, create or rebuild indexes at the time of a database or transaction log backup, then subsequent attempts to load the backup may fail. This is because recovery process can use the read ahead to increase the speed of recovery while rolling forward index maintenance activities.

Trace flag 3222 disables the read ahead that is used by the recovery operation during roll forward operations.

Look at here for more details:
BUG: Error Message 1511 Occurs When You Restore a Database or Transaction Log Dump

7. Trace flag 8202 (undocumented).

This trace flag used to replicate UPDATE as DELETE/INSERT pair. Let me to describe.

UPDATE commands at the publisher can be run as an "on-page DELETE/INSERT" or a "full DELETE/INSERT".

If the UPDATE command is run as an "on-page DELETE/INSERT," the Logreader send UDPATE command to the subscriber, If the UPDATE command is run as a "full DELETE/INSERT," the Logreader send UPDATE as DELETE/INSERT Pair.

If you turn on trace flag 8202, then UPDATE commands at the publisher will be always send to the subscriber as DELETE/INSERT pair.

8. Trace flag 8816 (undocumented).

SQL Server can convert two-digit year to a four-digit year, but Microsoft recommends to use the full four-digit year for all date operations. This trace flag logs every two-digit year conversion to a four-digit year. It can be useful to find Year 2000 bugs in older programs.

Look here for more details:
INF: Trace Flag 8816 to Help Year 2000 Conversion


Literature

1. Trace Flags (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/ta-tz_7.htm

2. DBCC TRACESTATUS (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/dbcc_27.htm

3. DBCC TRACEON (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/dbcc_26.htm

4. DBCC TRACEOFF (T-SQL)
http://msdn.microsoft.com/library/psdk/sql/dbcc_25.htm

5. FIX: Trace Flag 2521 Treat Exception as Unhandled, Plus Capturing Crash Dump
http://support.microsoft.com/support/kb/articles/Q248/1/15.ASP

6. INF: Trace Flag to Replicate UPDATE as DELETE/INSERT Pair
http://support.microsoft.com/support/kb/articles/Q160/1/81.asp

7. INF: Trace Flag 8816 to Help Year 2000 Conversion
http://support.microsoft.com/support/kb/articles/Q198/4/16.ASP

8. INF: Support for Network Database Files
http://support.microsoft.com/support/kb/articles/Q196/9/04.ASP

9. BUG: Error Message 1511 Occurs When You Restore a Database or Transaction Log Dump
http://support.microsoft.com/support/kb/articles/Q268/0/81.ASP

10.INF: Moving SQL Server 7.0 Databases to a New Location
http://support.microsoft.com/support/kb/articles/Q224/0/71.ASP


» See All Articles by Columnist Alexander Chigrik




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date