March 10, 2009 at 2:32 pm
In SQL 2005 Configuration Manager, if I want to turn on multiple trace flags for sqlservr, what is the correct syntax? For example if I want to have trace # 1204 and 1222 both on when SQL Server starts, do I use -T1204 -T1222 or -T1204,1222 or something else?
Thanks for your input.
Nate
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
March 10, 2009 at 11:56 pm
I don't know if that is possible Nate...
But... isn't 1222 only isn't enough? They both return full information; only thing is 1222 returns information in XML format.
1204: Returns the resources and types of locks participating in a deadlock and also the current command affected.
Scope: global only
1222: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema.
Scope: global only
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 11, 2009 at 7:16 am
In the Books Online, under Trace flags [SQL Server]:deadlocks it says "It is possible to enable both trace flags to obtain two representations of the same deadlock event."
I'm trying to troubleshoot some deadlock issues and that is when I saw that comment. I am really just using these two as an example. Whether it is these two or combinations of any of the trace flags, I am curious as to what the actual syntax is to do multiple trace flags.
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
March 11, 2009 at 8:11 am
NateT (3/11/2009)
In the Books Online, under Trace flags [SQL Server]:deadlocks it says "It is possible to enable both trace flags to obtain two representations of the same deadlock event."I'm trying to troubleshoot some deadlock issues and that is when I saw that comment. I am really just using these two as an example. Whether it is these two or combinations of any of the trace flags, I am curious as to what the actual syntax is to do multiple trace flags.
Yes, you can use both but it is hard to analyze a deadlock issue with the graph generated by 1204. Enable 1222 will give you clear picture and also TSQL statements causing the deadlock issue. If you have any torubles, post the output of 1222, we would be able to help you out.
March 11, 2009 at 8:21 am
Yes, I have turned on one of the trace flags and I'm beginning to examine what is causing the deadlocks.
However, that is not what this post is about.
I would like to know how to turn on multiple global trace flags at startup with the -T option. I should have used a more generic example of mythical trace flags 1111 and 9999. How do I enable two (or more) of the trace flags at startup?
Thank you.
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
March 11, 2009 at 8:25 am
if doing it via configuration manager:
;-T1204;-T1222
add that to the end of current startup parameters. You should see the flags referred to in the errorlog when SQL is restarted
If you want to enable them without restarting SQL
DBCC TRACEON(1204,1222,-1)
the -1 means enable for all sessions.
to check use DBCC TRACESTATUS(-1)
to turn off
DBCC TRACEOFF(1204,1222,-1)
using 1204 and 1222 just as an example
---------------------------------------------------------------------
March 11, 2009 at 8:29 am
NateT (3/11/2009)
I'm trying to troubleshoot some deadlock issues and that is when I saw that comment.
My apologies, but I was trying to give more insight into your troubleshoot just an extra info.
March 11, 2009 at 8:38 am
http://msdn.microsoft.com/en-us/library/ms187329.aspx
You can include 1..n trace flags with DBCC TRACEON
March 11, 2009 at 8:41 am
Steve Jones - Editor (3/11/2009)
http://msdn.microsoft.com/en-us/library/ms187329.aspxYou can include 1..n trace flags with DBCC TRACEON
OP was wanting solution at the startup steve:)
March 11, 2009 at 8:42 am
Thank you, George. That's what I was looking for. Since I didn't see any specific syntax for the -T it would make sense that it would have to be added a second time with the next flag to turn on.
Nate TeWinkel
UFS Inc. - DBA / Operations Analyst / Programmer
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply