SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Profiler - Logging Results to a Table


Profiler - Logging Results to a Table

Author
Message
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14897 Visits: 2730
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
jG
jG
SSC Veteran
SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)SSC Veteran (249 reputation)

Group: General Forum Members
Points: 249 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
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14897 Visits: 2730
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
Dinesh Priyankara
Dinesh Priyankara
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 115

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
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14897 Visits: 2730
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
Dinesh Priyankara
Dinesh Priyankara
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 115

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
Andy Warren
Andy Warren
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: Moderators
Points: 14897 Visits: 2730
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
Dinesh Priyankara
Dinesh Priyankara
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 115
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
mccork
mccork
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6052 Visits: 444
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
ALZDBA
ALZDBA
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15715 Visits: 8968

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


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont 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 :-D


Need a bit of Powershell? How about this

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search