What is the Syntax for Multiple Trace Flags

Question

What is the Syntax for Multiple Trace Flags

NateT

SSC-Addicted

Points: 402
More actions
March 10, 2009 at 2:32 pm

#131604

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

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

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

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 1

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.

[font="Arial"]---

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]

How to ask for help .. Read Best Practices here[/url].

NateT SSC-Addicted Points: 402 More actions · Answer 2

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

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.

NateT SSC-Addicted Points: 402 More actions · Answer 4

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

george sibbald SSC Guru Points: 104210 More actions · Answer 5

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

---------------------------------------------------------------------

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

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.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

http://msdn.microsoft.com/en-us/library/ms187329.aspx

You can include 1..n trace flags with DBCC TRACEON

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

Steve Jones - Editor (3/11/2009)
http://msdn.microsoft.com/en-us/library/ms187329.aspx
You can include 1..n trace flags with DBCC TRACEON

OP was wanting solution at the startup steve:)

NateT SSC-Addicted Points: 402 More actions · Answer 9

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