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, March 21, 2004 9:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 6, 2006 8:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC 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
Posted Wednesday, January 24, 2007 3:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, July 13, 2007 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, August 13, 2007 9:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 9:39 AM
Points: 12, Visits: 295
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
Posted Monday, August 20, 2007 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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

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


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 #392052
Posted Monday, January 25, 2010 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, March 24, 2010 12:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 18, 2013 11:20 AM
Points: 67, Visits: 135
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 »

Add to briefcase ««12

Permissions Expand / Collapse