Read an SQL Trace file into a Trace table

[GaryMazzone]

I'm using SQL Server 2005 Enterprise Edition. What I need to do is read an unknown number of tracefiles into a database table (I can create the table to load if needed). I know I can use SQL Profiler to do this, open the trace file in profiler then select Save As--> Trace table. What I want to do is something that will automate this process.

I know there is the function fn_trace_gettable that will read from the first trace in a series to the last, but unfortunately the results are not sequential. The trace stops at a file size of 5 Meg and start a new one with a different name. Since the fn_trace_gettable function relies on the start trace and end trace markers in the files I can not use this.

I already have a method but that involves opening each file in SQL Profiler and then saving it. I then run an batch process to generate a summary report of the data in the traces.

Any help ideas would be greatly appreciated.

Gary

[NeedSomeAnswers]

I know this doesn't help with traces you have already done but for new traces why not just set the trace to directly record to a table when you start it ?

You can also run a Trace to table from code -

http://msdn.microsoft.com/en-us/libr...4(SQL.90).aspx

[GaryMazzone]

Saving as a trace table on the machine is not an option. The analysis will be done on a local machine and not the servers. So I will be getting the trace files sent to me to analyse and need to do the work locally. I have already thought of writing a utility that will read the contents of a folder and precess the result of the folder scan to a set of tables using fn_trace_gettable. I just want to avoid doing the work if someone already has a solution to this.

[NeedSomeAnswers]

Right i see what you mean, that makes things a lot more difficult !!

I don't know any way to do what you want automatically, i suppose you could save the file as XML, which you could then bulk insert into a table.

The only problem with this is you would have to have the table already set-up with the exact correct columns in it, and you may need a mapping file.

I don't know if you are going to find an easy way to do this unfortunately!

[GaryMazzone]

I got a start any way this will read the trace files and get the data I want to summarize

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim textData As String = String.Empty
        Dim dbId As Integer = 0
        Dim duration As Integer = 0
        Dim cpu As Integer = 0
        Dim reads As Integer = 0
        Dim writes As Integer = 0
        Dim sTime As Date
        Dim msg As String = String.Empty
 
        Dim myTraceFile As New Microsoft.SqlServer.Management.Trace.TraceFile
        Dim fbFolder As New FolderBrowserDialog
        fbFolder.ShowDialog()
        Dim fName As New System.IO.DirectoryInfo(fbFolder.SelectedPath.Trim())
        fbFolder.Dispose()
        Dim allFiles As IO.FileInfo() = fName.GetFiles("*.trc")
        For Each f As System.IO.FileInfo In allFiles
            myTraceFile.InitializeAsReader(f.FullName())
            Dim i As Integer = 0
            While myTraceFile.Read()
                'EventClass  0  String
                'BinaryData  1      Byte()
                'DatabaseID  2      Int32
                'SPID        3      Int32
                'Duration    4      Int64
                'StartTime   5      DateTime
                'Reads       6      Int64
                'Writes      7      Int64
                'CPU         8      Int32
                'TestData    9      String
                'ObjectId   10      Int32
                textData = myTraceFile.GetString(9)
                If textData IsNot Nothing Then
                    dbId = myTraceFile.GetInt32(2)
                    msg = "DBId = " & dbId & System.Environment.NewLine
                    duration = myTraceFile.GetInt64(4)
                    msg &= "Duration: " & duration.ToString() & System.Environment.NewLine
                    cpu = myTraceFile.GetInt32(8)
                    msg &= "CPU: " & cpu.ToString() & System.Environment.NewLine
                    sTime = myTraceFile.GetDateTime(5)
                    msg &= "StartTime: " & sTime & System.Environment.NewLine
                    reads = myTraceFile.GetInt64(6)
                    msg &= "Reads: " & reads.ToString() & System.Environment.NewLine
                    writes = myTraceFile.GetInt64(7)
                    msg &= "Writes: " & writes.ToString() & System.Environment.NewLine
                    msg &= "TextData: " & textData
                    Me.TextBox1.Text = msg
                End If
                i += 1
                If i = 15 Then
                    Exit While
                End If
                msg = String.Empty
            End While
            myTraceFile.Close()
        Next
        myTraceFile.Dispose()
 
    End Sub