trace

  • hello experts, i am huge confuge about traces, i found that we can create trace through sp_crete_trace or we can ON trace through dbcc traceon(?), thrs a term server side tracing i heard, and some trace event i have seen in sys.traces, i am totally confuged wid traces, i wanna clear that whole concept n which trace how work pls help

  • Server-side traces (sp_create_trace) and traceflags (DBCC traceon) are completely different concepts.

    Server-side traces are essentially profiler traces without the GUI, they log when events occur on the instance being traced, such as login failures, stored procedure executions, errors, etc. You can use the profiler GUI to generate the script to create the trace for you.

    Traceflags can be enabled or disabled (there are lots and lots of different traceflags) and they change certain aspects of SQL's behaviour.

    For server-side traces, try a google search. Lots of information turns up with a simple search.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ya i have seen so many trace flags list even i can ON any trace by dbcc but dont knw after that hw can i get benefit of that, will you please explain me some more abt trace flags and how they are completely differentfrom server side traces

  • As I said, server-side traces (just like Profiler) lets you see when various things happen in SQL (stored procedure begins, stored procedure ends, login succeeds, login fails, etc)

    Traceflags change the behaviour of the SQL engine (eg if traceflag 1222 is enabled, information on deadlocks is written to the SQL error log. )

    There are lots of documented traceflags, there are lots of undocumented traceflags, I don't know if there's a comprehensive list anywhere. Some are in books Online. Start there.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • i have seen in the path 'Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG'

    thr are multiple errorlog file how wud i knw in which file my trace flag is being saved?

  • The latest is always called ERRORLOG

    And not all traceflags cause things to be written to the error log. 1222 was one specific example that I gave. There are traceflags that do many, many other things, from disabling lock escalation to changing how SQL reads from the disks. You should never enable a traceflag without knowing exactly what it will do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sachinsoni84 (7/3/2010)


    i have seen in the path 'Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG'

    thr are multiple errorlog file how wud i knw in which file my trace flag is being saved?

    are you referring to the files for the default trace. There will be 5 files at any one time and hold most recent logged info

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Zeal-DBA (7/3/2010)


    ya i have seen so many trace flags list even i can ON any trace by dbcc but dont knw after that hw can i get benefit of that, will you please explain me some more abt trace flags and how they are completely differentfrom server side traces

    You may like to check http://www.sqlservercentral.com/articles/trace+flags/70131/ for a list of trace flags.

  • Dude

    This post is well over a year old 😉

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Oops....

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

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