Having trouble with server-side traces

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Having trouble with server-side traces

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-03 : 11:00:35

First problem:

I am using sp_trace_setevent to capture ServerName or Instance (ColumnID 26).
When I doublecheck with

SELECT	*
FROM	::fn_trace_geteventinfo(1)

the column with id 26 IS present for every event!

However, when I later open the trace file in SQL profiler, the ServerName column is disregarded...

Second problem:

I am using sp_trace_setfilter to filter on two database id's (ColumnID 3).
The @logical_operator is set to 1 (OR), @comparison_operator to 0 (=) and @value to the two database ID's I want to capture.
However, only the first database id is captured.

I can doublecheck with

SELECT	*
FROM	::fn_trace_getfilterinfo(1)

that both filters are included. However the first filter is turned into an AND!

The version is 8.00.2039 (which is Service Pack 4).

If I do the same trace in SQL Profiler, I get ServerName column and can filter for the two database ID's, as I want!

E 12°55'05.63"
N 56°04'39.26"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-03 : 13:45:35

Not sure how you are doing there Peso.. I got both of your situations working correctly..


--server name
exec sp_trace_setfilter @TraceID, 26, 1, 1, N'%servername%'
-- dbid filters
exec sp_trace_setfilter @TraceID, 3, 1, 0, 9
exec sp_trace_setfilter @TraceID, 3, 1, 0, 10

I opened up the trace files in 2000 Profiler and I could see the server name. However, in 2005 profiler I do not see the column.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-03 : 14:16:20

Thanks. This is the situation I am in too.
I am using SQL Profiler 2008 to open the 2000 created trace file.

Tomorrow I will try to use function fn_trace_gettableto see if I can open the complete trace file.

Other than that, I am using filter to only processs trace for two specific databases on a server.
This will be done regurlary on all machines, so the trace files need to include servername in them.
All trace files will later be imported into a datamart.

E 12°55'05.63"
N 56°04'39.26"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-02-03 : 14:34:47

The trace files do have that information. Its the profiler that isnt reading them, as you will find out when you query the trace file using fn_trace_Gettable.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 06:22:19

It worked!

However the number of columns returned from ::fn_trace_GetTable is fixed.
But that is easily solved with using only the column names we need.

Thank you.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources