Sunday, June 3, 2012

1 Trace Flag

Trace flag is used to set up some specific server feature or behavior.  For example the 3605 would output the error information to the error log.

To check the trace flag status:

-- Show trace flag status in the current session
DBCC TRACESTATUS;
--   Show trace flag status for the global
DBCC TRACESTATUS(-1);
-- Show whether trace flag 3205 is enable at current session
DBCC TRACESTATUS( 3205);

We can enable the trace flag via 2 ways

1. Add the trace flag in the start up parameter list

Below example enable the trace flag 1204 for dead lock information (1204) and output to the error log (3605).

The trick for this is that “T” must be capital. Even MSFT support use –t, it can be only used if MSFT support advise to do so.

When enable it through the start up parameter, it is affect at global level and it would require the bounce of SQL Server to make it effective.

-T 1204;-T 3605

image

2. Use DBCC TRACEON

-- Enable 1222 and 3605 at current session
DBCC TRACEON (1222,3605);
-- Enable 1222 and 3605 at global level
DBCC TRACEON (1222,3605,-1)

When use DBCC TRACEON on the global level it would not affect any existing session.  Therefore MSFT suggest only use this to while application is not currently running on the server in case the the inconsistence for the application behavior.

Reference :

1 comments:

  1. Did you know that you can make cash by locking selected pages of your blog / website?
    To begin just join AdscendMedia and run their content locking tool.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates