Preserve trace flags between server restarts

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Preserve trace flags between server restarts

Author

Topic

bassem_farouk
Starting Member

18 Posts

Posted - 2009-12-11 : 22:17:22

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?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-11 : 22:25:59

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.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

bassem_farouk
Starting Member

18 Posts

Posted - 2009-12-11 : 23:01:37

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.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-11 : 23:10:08

You don't need to script it, although you can for documentation purposes, as you can find it in the SQL Server Configuration Manager.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-12-12 : 04:11:25

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.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources