Is there a list of the various DBCC TRACEON values?

(SQL Server 2000, SP3a)
Hello, all!
I'm interested to learn what the various Trace Flag values are for use with DBCC
TRACEON/OFF.
Thanks for any help you can provide!
John PetersonHi John,
You might find the following links useful:
Some Useful Undocumented SQL Server 2000 Trace Flags
http://www.sql-server-performance.com/ac_2000_trace_flags.asp
SQL Server 7.0 and 2000 Trace Flags
http://www.sql-server-performance.com/rd_traceflags.asp
-Sue
On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello, all!
>I'm interested to learn what the various Trace Flag values are for use with DBCC
>TRACEON/OFF.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem to
be an entirely comprehensive list. Do you know whether and where a full list might be
available?
Thanks again! :-)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> Hi John,
> You might find the following links useful:
> Some Useful Undocumented SQL Server 2000 Trace Flags
> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> SQL Server 7.0 and 2000 Trace Flags
> http://www.sql-server-performance.com/rd_traceflags.asp
> -Sue
> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello, all!
> >
> >I'm interested to learn what the various Trace Flag values are for use with DBCC
> >TRACEON/OFF.
> >
> >Thanks for any help you can provide!
> >
> >John Peterson
> >
>|||There is no full list and many of them are undocumented...so
there isn't a comprehensive documentation of all the trace
flags. Books online has a few of them documented under the
trace flags topic, you can find others by searching the
Knowledge base. In addition some books, links etc have some
listed but no one source of all of the trace flags.
-Sue
On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem to
>be an entirely comprehensive list. Do you know whether and where a full list might be
>available?
>Thanks again! :-)
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
>> Hi John,
>> You might find the following links useful:
>> Some Useful Undocumented SQL Server 2000 Trace Flags
>> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
>> SQL Server 7.0 and 2000 Trace Flags
>> http://www.sql-server-performance.com/rd_traceflags.asp
>> -Sue
>> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >(SQL Server 2000, SP3a)
>> >
>> >Hello, all!
>> >
>> >I'm interested to learn what the various Trace Flag values are for use with DBCC
>> >TRACEON/OFF.
>> >
>> >Thanks for any help you can provide!
>> >
>> >John Peterson
>> >
>|||Heh! That what I was afraid of. ;-)
Thanks again!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> There is no full list and many of them are undocumented...so
> there isn't a comprehensive documentation of all the trace
> flags. Books online has a few of them documented under the
> trace flags topic, you can find others by searching the
> Knowledge base. In addition some books, links etc have some
> listed but no one source of all of the trace flags.
> -Sue
> On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem
to
> >be an entirely comprehensive list. Do you know whether and where a full list might be
> >available?
> >
> >Thanks again! :-)
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> >> Hi John,
> >> You might find the following links useful:
> >> Some Useful Undocumented SQL Server 2000 Trace Flags
> >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> >> SQL Server 7.0 and 2000 Trace Flags
> >> http://www.sql-server-performance.com/rd_traceflags.asp
> >>
> >> -Sue
> >>
> >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> >> <j0hnp@.comcast.net> wrote:
> >>
> >> >(SQL Server 2000, SP3a)
> >> >
> >> >Hello, all!
> >> >
> >> >I'm interested to learn what the various Trace Flag values are for use with DBCC
> >> >TRACEON/OFF.
> >> >
> >> >Thanks for any help you can provide!
> >> >
> >> >John Peterson
> >> >
> >>
> >
>|||The 'full' list is only available internally in the SQL Server development
group. The reason for this is that the majority of trace flags are
undocumented and usually dangerous (i.e. they may cause SQL Server to crash,
shutdown, or behave in some strange way). You should not turn on trace flags
unless you're absolutely sure what the effect will be.
Thanks and regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Heh! That what I was afraid of. ;-)
> Thanks again!
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > There is no full list and many of them are undocumented...so
> > there isn't a comprehensive documentation of all the trace
> > flags. Books online has a few of them documented under the
> > trace flags topic, you can find others by searching the
> > Knowledge base. In addition some books, links etc have some
> > listed but no one source of all of the trace flags.
> >
> > -Sue
> >
> > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > <j0hnp@.comcast.net> wrote:
> >
> > >Thanks, Sue! That's exactly what I was looking for. Though, this
still doesn't seem
> to
> > >be an entirely comprehensive list. Do you know whether and where a
full list might be
> > >available?
> > >
> > >Thanks again! :-)
> > >
> > >
> > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > >> Hi John,
> > >> You might find the following links useful:
> > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > >> SQL Server 7.0 and 2000 Trace Flags
> > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > >>
> > >> -Sue
> > >>
> > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > >> <j0hnp@.comcast.net> wrote:
> > >>
> > >> >(SQL Server 2000, SP3a)
> > >> >
> > >> >Hello, all!
> > >> >
> > >> >I'm interested to learn what the various Trace Flag values are for
use with DBCC
> > >> >TRACEON/OFF.
> > >> >
> > >> >Thanks for any help you can provide!
> > >> >
> > >> >John Peterson
> > >> >
> > >>
> > >
> >
>|||Yeah...and I should have included some of what Paul posted
for you. Additionally, traces often change from release to
release so there really is no way to know what might be safe
and what might not be safe from version to version.
If the trace will server some purpose in general use, you
can typically find it in a KB article with instructions on
when it's appropriate to use.
-Sue
On Mon, 27 Oct 2003 14:01:08 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Heh! That what I was afraid of. ;-)
>Thanks again!
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
>> There is no full list and many of them are undocumented...so
>> there isn't a comprehensive documentation of all the trace
>> flags. Books online has a few of them documented under the
>> trace flags topic, you can find others by searching the
>> Knowledge base. In addition some books, links etc have some
>> listed but no one source of all of the trace flags.
>> -Sue
>> On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem
>to
>> >be an entirely comprehensive list. Do you know whether and where a full list might be
>> >available?
>> >
>> >Thanks again! :-)
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
>> >> Hi John,
>> >> You might find the following links useful:
>> >> Some Useful Undocumented SQL Server 2000 Trace Flags
>> >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
>> >> SQL Server 7.0 and 2000 Trace Flags
>> >> http://www.sql-server-performance.com/rd_traceflags.asp
>> >>
>> >> -Sue
>> >>
>> >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
>> >> <j0hnp@.comcast.net> wrote:
>> >>
>> >> >(SQL Server 2000, SP3a)
>> >> >
>> >> >Hello, all!
>> >> >
>> >> >I'm interested to learn what the various Trace Flag values are for use with DBCC
>> >> >TRACEON/OFF.
>> >> >
>> >> >Thanks for any help you can provide!
>> >> >
>> >> >John Peterson
>> >> >
>> >>
>> >
>|||Thanks, Paul!
My curiosity was piqued by this link that shows a lot of the undocumented DBCC commands,
as I was investigating the DBCC SETINSTANCE (which is the underlying call from the
undocumented sp_user_counter* SPs).
http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=9801
I noticed that this list was generated with a Trace Flag of 2520, which wasn't in that
list that Sue provided (the one by Randy Dyess). So I got to thinking, what *other* Trace
Flags are available, but undocumented?
In just some cursory browsing, I learned a number of things. For example, that you can
potentially log deadlock conditions to the error log for all connections, as indicated by
this link to dbforums:
http://65.61.175.198/t894916.html
I was interested to learn what other nifty "goodies" might be available. ;-)
Thanks for the information, and if you have any additional comments, I'd love to hear from
you.
Regards,
John Peterson
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ua$btgNnDHA.2500@.TK2MSFTNGP10.phx.gbl...
> The 'full' list is only available internally in the SQL Server development
> group. The reason for this is that the majority of trace flags are
> undocumented and usually dangerous (i.e. they may cause SQL Server to crash,
> shutdown, or behave in some strange way). You should not turn on trace flags
> unless you're absolutely sure what the effect will be.
> Thanks and regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Heh! That what I was afraid of. ;-)
> >
> > Thanks again!
> >
> >
> > "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > > There is no full list and many of them are undocumented...so
> > > there isn't a comprehensive documentation of all the trace
> > > flags. Books online has a few of them documented under the
> > > trace flags topic, you can find others by searching the
> > > Knowledge base. In addition some books, links etc have some
> > > listed but no one source of all of the trace flags.
> > >
> > > -Sue
> > >
> > > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > > <j0hnp@.comcast.net> wrote:
> > >
> > > >Thanks, Sue! That's exactly what I was looking for. Though, this
> still doesn't seem
> > to
> > > >be an entirely comprehensive list. Do you know whether and where a
> full list might be
> > > >available?
> > > >
> > > >Thanks again! :-)
> > > >
> > > >
> > > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > > >> Hi John,
> > > >> You might find the following links useful:
> > > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > > >> SQL Server 7.0 and 2000 Trace Flags
> > > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > > >>
> > > >> -Sue
> > > >>
> > > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > > >> <j0hnp@.comcast.net> wrote:
> > > >>
> > > >> >(SQL Server 2000, SP3a)
> > > >> >
> > > >> >Hello, all!
> > > >> >
> > > >> >I'm interested to learn what the various Trace Flag values are for
> use with DBCC
> > > >> >TRACEON/OFF.
> > > >> >
> > > >> >Thanks for any help you can provide!
> > > >> >
> > > >> >John Peterson
> > > >> >
> > > >>
> > > >
> > >
> >
> >
>|||You're welcome. Be even more vehemently warned away from playing with
unfamiliar, undocumented DBCC commands. Some of these (when used outside
specific conditions related to recovering corrupt or inaccessible databases)
can make your database unusable and unrecoverable.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e93MX#OnDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Thanks, Paul!
> My curiosity was piqued by this link that shows a lot of the undocumented
DBCC commands,
> as I was investigating the DBCC SETINSTANCE (which is the underlying call
from the
> undocumented sp_user_counter* SPs).
> http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=9801
> I noticed that this list was generated with a Trace Flag of 2520, which
wasn't in that
> list that Sue provided (the one by Randy Dyess). So I got to thinking,
what *other* Trace
> Flags are available, but undocumented?
> In just some cursory browsing, I learned a number of things. For example,
that you can
> potentially log deadlock conditions to the error log for all connections,
as indicated by
> this link to dbforums:
> http://65.61.175.198/t894916.html
> I was interested to learn what other nifty "goodies" might be available.
;-)
> Thanks for the information, and if you have any additional comments, I'd
love to hear from
> you.
> Regards,
> John Peterson
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ua$btgNnDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > The 'full' list is only available internally in the SQL Server
development
> > group. The reason for this is that the majority of trace flags are
> > undocumented and usually dangerous (i.e. they may cause SQL Server to
crash,
> > shutdown, or behave in some strange way). You should not turn on trace
flags
> > unless you're absolutely sure what the effect will be.
> >
> > Thanks and regards,
> >
> > Paul.
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > Heh! That what I was afraid of. ;-)
> > >
> > > Thanks again!
> > >
> > >
> > > "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > > > There is no full list and many of them are undocumented...so
> > > > there isn't a comprehensive documentation of all the trace
> > > > flags. Books online has a few of them documented under the
> > > > trace flags topic, you can find others by searching the
> > > > Knowledge base. In addition some books, links etc have some
> > > > listed but no one source of all of the trace flags.
> > > >
> > > > -Sue
> > > >
> > > > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > > > <j0hnp@.comcast.net> wrote:
> > > >
> > > > >Thanks, Sue! That's exactly what I was looking for. Though, this
> > still doesn't seem
> > > to
> > > > >be an entirely comprehensive list. Do you know whether and where a
> > full list might be
> > > > >available?
> > > > >
> > > > >Thanks again! :-)
> > > > >
> > > > >
> > > > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > > > >> Hi John,
> > > > >> You might find the following links useful:
> > > > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > > > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > > > >> SQL Server 7.0 and 2000 Trace Flags
> > > > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > > > >>
> > > > >> -Sue
> > > > >>
> > > > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > > > >> <j0hnp@.comcast.net> wrote:
> > > > >>
> > > > >> >(SQL Server 2000, SP3a)
> > > > >> >
> > > > >> >Hello, all!
> > > > >> >
> > > > >> >I'm interested to learn what the various Trace Flag values are
for
> > use with DBCC
> > > > >> >TRACEON/OFF.
> > > > >> >
> > > > >> >Thanks for any help you can provide!
> > > > >> >
> > > > >> >John Peterson
> > > > >> >
> > > > >>
> > > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, and some of the (like DBCC PSS) give very valuable information about
for instance logical I/O's per session to mention one, which is unavailable
otherwise (to my knowledge). It's about time that some of these commands
*are* well documented, preferably by the guys who build/maintain them..
--
regards,
Mario
http://www.sqlinternals.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ergphuRnDHA.964@.TK2MSFTNGP10.phx.gbl...
> You're welcome. Be even more vehemently warned away from playing with
> unfamiliar, undocumented DBCC commands. Some of these (when used outside
> specific conditions related to recovering corrupt or inaccessible
databases)
> can make your database unusable and unrecoverable.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e93MX#OnDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Paul!
> >
> > My curiosity was piqued by this link that shows a lot of the
undocumented
> DBCC commands,
> > as I was investigating the DBCC SETINSTANCE (which is the underlying
call
> from the
> > undocumented sp_user_counter* SPs).
> >
> > http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=9801
> >
> > I noticed that this list was generated with a Trace Flag of 2520, which
> wasn't in that
> > list that Sue provided (the one by Randy Dyess). So I got to thinking,
> what *other* Trace
> > Flags are available, but undocumented?
> >
> > In just some cursory browsing, I learned a number of things. For
example,
> that you can
> > potentially log deadlock conditions to the error log for all
connections,
> as indicated by
> > this link to dbforums:
> >
> > http://65.61.175.198/t894916.html
> >
> > I was interested to learn what other nifty "goodies" might be available.
> ;-)
> >
> > Thanks for the information, and if you have any additional comments, I'd
> love to hear from
> > you.
> >
> > Regards,
> >
> > John Peterson
> >
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:ua$btgNnDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > The 'full' list is only available internally in the SQL Server
> development
> > > group. The reason for this is that the majority of trace flags are
> > > undocumented and usually dangerous (i.e. they may cause SQL Server to
> crash,
> > > shutdown, or behave in some strange way). You should not turn on trace
> flags
> > > unless you're absolutely sure what the effect will be.
> > >
> > > Thanks and regards,
> > >
> > > Paul.
> > >
> > > --
> > > Paul Randal
> > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > > Heh! That what I was afraid of. ;-)
> > > >
> > > > Thanks again!
> > > >
> > > >
> > > > "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > > news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > > > > There is no full list and many of them are undocumented...so
> > > > > there isn't a comprehensive documentation of all the trace
> > > > > flags. Books online has a few of them documented under the
> > > > > trace flags topic, you can find others by searching the
> > > > > Knowledge base. In addition some books, links etc have some
> > > > > listed but no one source of all of the trace flags.
> > > > >
> > > > > -Sue
> > > > >
> > > > > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > > > > <j0hnp@.comcast.net> wrote:
> > > > >
> > > > > >Thanks, Sue! That's exactly what I was looking for. Though,
this
> > > still doesn't seem
> > > > to
> > > > > >be an entirely comprehensive list. Do you know whether and where
a
> > > full list might be
> > > > > >available?
> > > > > >
> > > > > >Thanks again! :-)
> > > > > >
> > > > > >
> > > > > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > > > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > > > > >> Hi John,
> > > > > >> You might find the following links useful:
> > > > > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > > > > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > > > > >> SQL Server 7.0 and 2000 Trace Flags
> > > > > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > > > > >>
> > > > > >> -Sue
> > > > > >>
> > > > > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > > > > >> <j0hnp@.comcast.net> wrote:
> > > > > >>
> > > > > >> >(SQL Server 2000, SP3a)
> > > > > >> >
> > > > > >> >Hello, all!
> > > > > >> >
> > > > > >> >I'm interested to learn what the various Trace Flag values are
> for
> > > use with DBCC
> > > > > >> >TRACEON/OFF.
> > > > > >> >
> > > > > >> >Thanks for any help you can provide!
> > > > > >> >
> > > > > >> >John Peterson
> > > > > >> >
> > > > > >>
> > > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>