Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Profiler - Logging Results to a Table Expand / Collapse
Author
Message
Posted Sunday, December 23, 2001 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:06 PM
Points: 6,779, Visits: 1,855
Comments posted to this topic are about the content posted at profilerloggingresultstoatable.asp


Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #2026
Posted Tuesday, January 8, 2002 8:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 4, 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
Posted Tuesday, January 8, 2002 9:04 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:06 PM
Points: 6,779, Visits: 1,855
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
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #25400
Posted Wednesday, July 23, 2003 10:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 3, 2014 1:41 AM
Points: 175, Visits: 106

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
Posted Thursday, July 24, 2003 3:45 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:06 PM
Points: 6,779, Visits: 1,855
I don't recall any issues. What bug#?

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




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #25402
Posted Thursday, July 24, 2003 4:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 3, 2014 1:41 AM
Points: 175, Visits: 106

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
Posted Thursday, July 24, 2003 5:54 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 1:06 PM
Points: 6,779, Visits: 1,855
Could you post what you're running? I can try to repro it here.

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




Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #25404
Posted Saturday, July 26, 2003 1:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 3, 2014 1:41 AM
Points: 175, Visits: 106
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
Posted Saturday, July 26, 2003 4:34 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:44 AM
Points: 5,068, Visits: 378
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
Posted Wednesday, January 7, 2004 11:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439

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


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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #94028
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse