Trace Flag Status

  • Comments posted to this topic are about the item Trace Flag Status

  • Interesting question, thanks Steve.
    However, the answer is not entirely correct:

    trace#
    Is the number of the trace flag for which the status is displayed. If trace#, and -1 are not specified, all trace flags that are enabled for the session are displayed.
    n
    Is a placeholder that indicates multiple trace flags can be specified.
    -1
    Displays the status of trace flags that are enabled globally. If -1 is specified without trace#, all the global trace flags that are enabled are displayed.


    i.e., one needs to specifically provide parameter  "-1" to see all Global trace flags.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • I'm able to see all the trace flags enabled on my server just by running DBCC TRACESTATUS.

  • Will DBCC TRACESTATUS really show other users' session-based trace flag?  The documentation reads to me that you will only see global or your session's trace flags.

    Be still, and know that I am God - Psalm 46:10

  • Intuitively I estimated that I got it wrong...:crying: But I found how to check enabled Trace Flags for SQL Server.
    On my server I have enabled two global TF 7806 and TF 8017 with using the -T command-line startup option of Sqlservr.exe.

    In SSMS 2014, I opened two sessions. In the first session I executed this code:

    USE master;
    GO

    DBCC TRACEON (3205);
    GO

    DBCC TRACESTATUS;
    GO
    /*
    Results
    -------
    TraceFlag Status Global Session
    --------- ------ ------ -------
    3205  1  0  1
    7806  1  1  0
    8017  1  1  0

    (3 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    */

    In the second session I executed this code:

    USE master
    GO

    DBCC TRACESTATUS;
    GO
    /*
    Results
    -------
    TraceFlag Status Global Session
    --------- ------ ------ -------
    7806  1  1  0
    8017  1  1  0

    (2 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Session trace flag 3205 is not shown. It corresponds exactly to the MSDN documentation
    DBCC TRACESTATUS (Transact-SQL), see "If trace#, and -1 are not specified, all trace flags
    that are enabled for the session are displayed."

    The only option how to display trace flags IN ANY OTHER SESSION is, that you must search
    in the error log. Fortunately you can use an extended stored procedure xp_readerrorlog command.
    */

    EXEC xp_ReadErrorLog 0, 1, "DBCC TRACE"
    GO

    Thanks for this question. I learned somethig new. 🙂

  • Stewart "Arturius" Campbell - Thursday, March 9, 2017 11:16 PM

    Interesting question, thanks Steve.
    However, the answer is not entirely correct:

    trace#
    Is the number of the trace flag for which the status is displayed. If trace#, and -1 are not specified, all trace flags that are enabled for the session are displayed.
    n
    Is a placeholder that indicates multiple trace flags can be specified.
    -1
    Displays the status of trace flags that are enabled globally. If -1 is specified without trace#, all the global trace flags that are enabled are displayed.


    i.e., one needs to specifically provide parameter  "-1" to see all Global trace flags.

    That is actually a bug. Whether the bug is the documentation or in tracestatus, I don't know. With or without the -1, the results will be the same.

    I bring that up in this article here.
    http://bit.ly/ssgflagcapture

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • david.gugg - Friday, March 10, 2017 7:20 AM

    Will DBCC TRACESTATUS really show other users' session-based trace flag?  The documentation reads to me that you will only see global or your session's trace flags.

    It looks like it may have been fixed to prevent showing of other session trace flags that are not set globally.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Per MSFT documentation:
    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-tracestatus-transact-sql

    In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server.

    This indicates you can NOT see trace flags of another user session, only the global ones.

    Gimme back my point!! 
    {8-Q.......    (the wide-eyed stare with tongue out and drooling emoticon)

  • Answer changed and points awarded back.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply