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
Grant Cribb
Grant Cribb
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
On a different thought:

I am in the process of auditing security events using a profiler scripts with the output going directly to a file (for all the reasons previously mentioned in this forum).

The problem that I am running across is that the output to the trace file is being buffered (128K) and if a nasty outage occurs before the buffer is flushed (power supply, blue screen, whatever)then the audited security trace is being lost.

I am new to SQL Server 2000, is there something obvious that I have missed (e.g. bypass buffered writes)?

Any tips would be appreciated.
AndrewM-375946
AndrewM-375946
SSC Veteran
SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)SSC Veteran (269 reputation)

Group: General Forum Members
Points: 269 Visits: 6
To setup a trace that only views the last hour but does not run out of space requires the following:

15:00
JOB1-Step 1- The trace script (set to stop after an hour) approx 300Mb, with trc file saved with DATETIME stamp, i.e. 6NOV 2006 1500PM.trc

16:01
JOB 2 Step 1- DROP current table where trc was imported

step 2- use trace_table to import trc file
Ian Cannonier
Ian Cannonier
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
I have been asked to do a similar task to Grasshopper.

I have an MS Analysis Server which processes a cube in the early hours of the morning. Some times it takes an hour and others 4. I have been asked to provide an automated trace which saves the results to a table, so they can be reviewed later to find out why. However, I can not find a way to do this without writing the trace to a flat file first.

I like the idea of creating a similar function to that of Profiler so it can be reused with ease, but which tables / views would I apply the triggers too?

Any help would be gratefully received.
Jon Mitchell-464369
Jon Mitchell-464369
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
This should answer a lot of people's questions if they have SQL 2005.

http://msdn2.microsoft.com/en-us/library/ms345134.aspx
Michael-401546
Michael-401546
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 348
OK, one last time. Has anybody been able to trace to a table? Andy, in all these years since your article, were you able to get it working? I am stuck with 2000 for the time being
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30709 Visits: 8986

If you want to trace in batch, you need to trace to a file !

Afterward you can load the file to a given (empty) table using :

-- HOW TO: Programmatically Load Trace Files into Tables
-- http://support.microsoft.com/kb/270599
--
SELECT *
INTO myserver_mydb_CLOR_070322
FROM ::fn_trace_gettable('F:\myTraces\trace_myserver_mydb_070322.trc', 32)



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
Krop
Krop
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 64
When you create the trace, don't add ".trc' to the end of the name as sp_trace_create will also add it.

exec sp_trace_create @traceid = @traceid OUTPUT,
@options = 0,
@tracefile = 'C:\Temp\myTraceFile', -- DO NOT ADD .TRC HERE!
@maxfilesize = 10,
@stoptime = 0

SELECT * FROM ::fn_trace_gettable('C:\Temp\myTraceFile.trc', default)

If you have already added it, just amend the filename from "myTraceFile.trc.trc" to "myTraceFile.trc" or you'll only be able to open it using the Profiler GUI and not using fn_trace_gettable.

Rgds,
Krop
Ranga N
Ranga N
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 151
Dinesh Priyankara (7/24/2003)
<font face='Verdana'>
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
</font id='Verdana'>

MCP MCSE MCSD MCDBA


The fn_trace_gettable function looks for the trace file N'E:\Traces\monitor.trc' on the sql server you are connected in the management studio. For example if the trace file E:\Traces\monitor.trc exists on the SQL Server "SQLSERVerTest", connect to this SQL server thru mgmt studio and then try to query.



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