How to trace a trace file?

SQL Server 2005 Enterprise 64-bit, clustered.
I've just discovered that there is a trace file being created on one of our
servers, but I can't figure out where it's being generated from. It's
tracing various Database Events (Data File Auto Grow, Data File Auto Shrink,
etc.), Error and Warning (Hash Warning, Missing Join Predicate, etc.), Full
Text (FT: Crawl Aborted, etc.), Objects (Object:Altered, etc.), Security
Audit (Audit Add DB User Event, etc.) and Server (Server Memory Change). I
can't see a process in the Activity Monitor that is from SQL Server
Profiler, so I'm a little stumped as to what this is.
I presume it could be caused by sp_trace_create but I'm not sure how I would
find which process, and more specifically which user, is running this.
Or is this possibly some kind of internal auditing capability that someone
has turned on, and if so, where do I find it?
Any help you can provide so I can track this down would be most gratefully
received.
Michael MacGregor
Database Architect
Perhaps it is the default trace? Is this trace in the SQL Server LOG directory (where the errorlog
file is)? If so, disable it using sp_configure (but remember that is it quite handy to have...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23jLsRaInHHA.1244@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2005 Enterprise 64-bit, clustered.
> I've just discovered that there is a trace file being created on one of our servers, but I can't
> figure out where it's being generated from. It's tracing various Database Events (Data File Auto
> Grow, Data File Auto Shrink, etc.), Error and Warning (Hash Warning, Missing Join Predicate,
> etc.), Full Text (FT: Crawl Aborted, etc.), Objects (Object:Altered, etc.), Security Audit (Audit
> Add DB User Event, etc.) and Server (Server Memory Change). I can't see a process in the Activity
> Monitor that is from SQL Server Profiler, so I'm a little stumped as to what this is.
> I presume it could be caused by sp_trace_create but I'm not sure how I would find which process,
> and more specifically which user, is running this.
> Or is this possibly some kind of internal auditing capability that someone has turned on, and if
> so, where do I find it?
> Any help you can provide so I can track this down would be most gratefully received.
> Michael MacGregor
> Database Architect
>
|||Hi Tibor,
Thanks for your prompt response.
Is there documentation on what the default trace contains, i.e. which events
it is monitoring? I took a quick look at BOL but there wasn't much in the
way of information about that under configuration options.
Thanks.
Michael MacGregor
Database Architect
|||> Is there documentation on what the default trace contains, i.e. which events it is monitoring?
Sure. Well, ...it depends on whether you want to call below "documentation" or not... ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23VTr9hInHHA.2596@.TK2MSFTNGP06.phx.gbl...
> Hi Tibor,
> Thanks for your prompt response.
> Is there documentation on what the default trace contains, i.e. which events it is monitoring? I
> took a quick look at BOL but there wasn't much in the way of information about that under
> configuration options.
> Thanks.
> Michael MacGregor
> Database Architect
>
|||So I presume then that there's not much in the way of "official"
documentation for this feature from Microsoft. Ain't that a surprise!
Thanks Tibor.
MTM
|||Michael
I wrote an article about this trace when I discovered it. You're right, that
there is not a lot of documentation about it.
http://www.sqlmag.com/Article/ArticleID/48939/Searching_for_a_Trace.html
You can open up any of the trace output files in the Profiler, and then when
you look at the properties of the trace you can see what is being traced.
As Tibor mentioned, you can disable it with sp_configure, if you decide that
it's the best thing to do.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uLFbVnInHHA.568@.TK2MSFTNGP02.phx.gbl...
> So I presume then that there's not much in the way of "official"
> documentation for this feature from Microsoft. Ain't that a surprise!
> Thanks Tibor.
> MTM
>