March 9, 2017 at 8:35 pm
Comments posted to this topic are about the item Trace Flag Status
March 9, 2017 at 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.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
March 10, 2017 at 5:30 am
I'm able to see all the trace flags enabled on my server just by running DBCC TRACESTATUS.
March 10, 2017 at 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.
Be still, and know that I am God - Psalm 46:10
March 10, 2017 at 10:49 am
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. 🙂
March 10, 2017 at 3:24 pm
Stewart "Arturius" Campbell - Thursday, March 9, 2017 11:16 PMInteresting 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
March 10, 2017 at 3:25 pm
david.gugg - Friday, March 10, 2017 7:20 AMWill 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
July 6, 2017 at 6:59 am
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)
July 6, 2017 at 10:52 am
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