THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Roman Rehak

Change in trace flags behavior in SQL Server 2005

The other day I was setting up trace flags when chasing a deadlock issue. The way it works is that after you run DBCC TRACEON, SQL Server outputs detailed log to the SQL Server event log. But for some reason this trace was just not showing. The only thing I could see in the event log was a message that DBCC TRACEON was executed, but nothing after that. I used Google and read some articles but still didn't find what the problem was. I went back to BOL and I finally noticed the reason why:

"In SQL Server 2000, a simple DBCC TRACEON (1204) is enough to enable deadlock reporting to the error log. In SQL Server 2005, you must enable the flag globally because the session-level flag is not visible to the deadlock monitor thread."

So as it turns out, it's a change in behavior from the previous version. Here is how you enable the trace globally:

DBCC TRACEON (1204, -1);

Published Tuesday, April 24, 2007 3:03 PM by roman

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Roman

Also in  SQL Serverc 2005 we can/should use DBCC TRACEON (1222, -1)” or adding “-T1222” as a SQL startup parameter.

https://blogs.msdn.com/sqlserverstorageengine/default.aspx

April 24, 2007 11:28 PM
 

ACALVETT said:

I normally have to use 3605 as well in 2000 to get the output to the error log otherwise it goes to the client.

April 27, 2007 9:29 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement