Profiler - Logging Results to a Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Profiler - Logging Results to a Table

18 posts, Page 1 of 2

1 2 »»

Profiler - Logging Results to a Table

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Sunday, December 23, 2001 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Comments posted to this topic are about the content posted at profilerloggingresultstoatable.asp

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #2026

Posted Tuesday, January 08, 2002 8:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 04, 2010 9:59 AM
Points: 137, Visits: 14

From my experience; is'nt it optimal to send the results to a file, then save it to a SQL table? Seems profiler writes faster to a file than a table. -JG

-JG

Post #25399

Andy Warren

Posted Tuesday, January 08, 2002 9:04 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Maybe! Havent timed honestly, though you have to figure if you have indexes on the table that might add to a bit more time than just appending to the end of a text file (basically anyway). Whether the load/time matters depends on the volume, hardware, what you're logging etc. I just think it would be nice to log directly to a table - after all, SQL is ALL about tables. Especially for a quick perf tune type scenario it would be handy.

More importantly, I'd like to have the option. Stick warning labels on it if you must, but why not let us do it?

Of course once you know the deal its not bad, so the intent here was to maybe save someone new to Profiler a few minutes of head scratching...and let me air my complaint too!

Thanks for reading the article and taking time to comment.

Andy

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #25400

Dinesh Priyankara

Posted Wednesday, July 23, 2003 10:00 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:17 AM
Points: 174, Visits: 95

Hi Andy,
It is today I read this article. I am having a problem with fn_trace_gettable. I have created my trace through sp_trace_create with option 2.
Error is :
Server: Msg 567, Level 16, State 2, Line 1
File 'E:\Traces\monitor.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

I surfed on various sites and found in microsoft that says this is known bug in SQL Server.
Did you get this error? Or you know how to overcome this.
I can read this if I stop the server and restart. Untill restart, file size remain 0.

Please reply soon on this 'cause I have to create the trace through SPs.

Dinesh

MCP MCSE MCSD MCDBA

Post #25401

Andy Warren

Posted Thursday, July 24, 2003 3:45 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

I don't recall any issues. What bug#?

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #25402

Dinesh Priyankara

Posted Thursday, July 24, 2003 4:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:17 AM
Points: 174, Visits: 95

Ok. What I want is : create a trace using sp_trace_create with option 2. That's what I have done. But I cannot read the tracefile untill I stop and restart the server. I am getting an error when I call fn_trace_gettable function.
<b>
SELECT * FROM :: fn_trace_gettable (N'E:\Traces\monitor.trc', 1)

Output:
Server: Msg 567, Level 16, State 2, Line 1
File 'E:\Traces\monitor.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.[/b]

As per microsoft site http://support.microsoft.com/support/kb/articles/q273/9/72.asp , This is bug. And they have given a workaround but no luck.

I highly appreciate if you can help me on this.

Dinesh

MCP MCSE MCSD MCDBA

Post #25403

Andy Warren

Posted Thursday, July 24, 2003 5:54 PM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

Could you post what you're running? I can try to repro it here.

Andy
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #25404

Dinesh Priyankara

Posted Saturday, July 26, 2003 1:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:17 AM
Points: 174, Visits: 95

This is what I did.
creation and starting:
------------------------------------------

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

exec @rc = sp_trace_create @TraceID output, 2, N'E:\Traces\monitor', @maxfilesize, NULL

declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 54, 1, @on
exec sp_trace_setevent @TraceID, 54, 3, @on
exec sp_trace_setevent @TraceID, 54, 6, @on
exec sp_trace_setevent @TraceID, 54, 9, @on
exec sp_trace_setevent @TraceID, 54, 10, @on
exec sp_trace_setevent @TraceID, 54, 11, @on
exec sp_trace_setevent @TraceID, 54, 12, @on
exec sp_trace_setevent @TraceID, 54, 13, @on
exec sp_trace_setevent @TraceID, 54, 14, @on
exec sp_trace_setevent @TraceID, 54, 16, @on
exec sp_trace_setevent @TraceID, 54, 17, @on
exec sp_trace_setevent @TraceID, 54, 18, @on

declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 8
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

exec sp_trace_setstatus @TraceID, 1
---------------------------------------

Did some transactions and tried to load data with function:

select * from :: fn_trace_gettable( N'E:\Traces\monitor.trc',default)

output: error, what I mentioned.
----------------------------------------
check the file but size is still o KB.

----------------------------------
stopped the SQL Server:
check the file, now the size is 128KB.
-----------------------------------
restarted the SQL Server:
run the function again:
got the result.

What's wrong with code?

MCP MCSE MCSD MCDBA

Post #25405

mccork

Posted Saturday, July 26, 2003 4:34 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 9:51 PM
Points: 4,591, Visits: 320

Dinesh,

Is your problem possibly that you're trying to read the trace file while it's still open and Profiler is writing to it? My understanding is that you can only read it if a) the trace is stopped, or b) if the rollover threshold has been reached and the trace has closed the file and started a new one.

Rather than stopping SQL Server, I usually use a time threshold and/or a max file size threshold (with no rollover) or sp_trace_setstatus to stop non-interactive traces.

Edited by - mccork on 07/26/2003 4:34:58 PM

Cheers,
- Mark

Post #25406

ALZDBA

Posted Wednesday, January 07, 2004 11:55 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 6,861, Visits: 8,045

Nice article.

What I would like to do is to profile a sqlservers activities, store that trace-info in a table on another sqlserver and viewing only the latest hour of activity in profiler itself. I've got this problem that occurs every once in a while, wich I want to repro, but can't get a clue.

When I run profiler, it stops after a day or so, because the computer which runs profiler runs out of space on it's C-drive.

Any ideas ?

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #94028

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions