Hi i am doing deadlock detection flags dbcc traceon (1204 , -1) dbcc traceon (1222 , -1)
i want to reservef those flags between server restarts.
i want to make sure that this is persistent , i may forget to start using the -T flag 1-any other way to do it ? 2- also is trace flags overload a busy server?
To preserver the deadlock trace flags after a restart, you must set it up as a startup parameter using the -T switch. Using the DBCC command only lasts until SQL Server is restarted. So when you want it to happen in the current session as well as future sessions, you issue the DBCC command and also setup the trace flag.
Please see BOL for details.
I don't see any issues with overloading the server for just the deadlock trace flag. We have it enabled on many of our systems, some of them even see a few thousand queries per second.
Thanks very much i think i need to script the sqlserver cmmand with -T , so if i restart the server after 1 year, i will forget which flags was on for sure.
You don't need both 1204 and 1222. On SQL 2005 and 2008, the information that traceflag 1222 returns is a superset of the information that 1204 returns. Just use 1222 on SQL 2005/2008 and just use 1204 on SQL 2000.
Preserve trace flags between server restarts
Posted - 12/11/2009 : 22:17:22
