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 2 of 2

««1 2

Profiler - Logging Results to a Table

Rate Topic

Display Mode

Topic Options

Author

Message

Grant Cribb

Posted Sunday, March 21, 2004 9:56 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 13, 2004 10:50 PM
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.

Post #107307

AndrewM-375946

Posted Monday, November 06, 2006 8:41 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 31, 2008 2:42 AM
Points: 209, 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

Post #320672

Ian Cannonier

Posted Wednesday, January 24, 2007 3:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 24, 2007 2:36 AM
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.

Post #339267

Jon Mitchell-464369

Posted Friday, July 13, 2007 8:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, October 15, 2008 7:49 AM
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

Post #381508

Michael-401546

Posted Monday, August 13, 2007 9:00 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 11:24 AM
Points: 10, Visits: 223

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

Post #390180

ALZDBA

Posted Monday, August 20, 2007 7:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:17 AM
Points: 6,862, Visits: 8,049

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

     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 #392052

Krop

Posted Monday, January 25, 2010 1:14 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 14, 2010 2:06 PM
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

Post #853263

Ranga N

Posted Wednesday, March 24, 2010 12:38 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 12:25 PM
Points: 67, Visits: 133

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.

Post #889258

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions