Import trace file

• martin-210793

  Ten Centuries

  Points: 1083

  More actions

  October 23, 2007 at 8:14 am

  #153910

  Hi Folks,

  I have a 10 gb(don't ask) trace file and need to import it into SQL. I don't have a lot of space on my machine only 6 gbs and this is what I've tried

  - Load the trace in SQLProfiler

  result : windows error as I didn't have enough space on my machine

  - Import the file using the following

  SELECT * INTO trace_log_results

  FROM

  fn_trace_gettable (@filename,default)

  result : windows error as I didn't have enough space on my machine... tempdb starting filling up and used up all my space

  I am currently in the middle of just doing

  insert into trace_log_results

  SELECT *

  FROM

  fn_trace_gettable(table_name)

  and once again the tempdb file is filling up.

  Does anyone know how to do this any other way? BCP or something else. If I declare the cols I want will it bypass the tempdb.

  thanks in advance and I am looking forward to your reponse.

  martin

• Philip Kelley

  SSCertifiable

  Points: 6351

  More actions

  October 23, 2007 at 8:55 am

  #744582

  Here's some templates I've used in the past. They're somewhere between SQL 2000 and SQL 2005 compatible, as I haven't done much work with this yet in 2005, so be wary of the column names.

  First, I use

  SELECT top 10 *

  from ::fn_trace_gettable(' ', default)

  to get the sense of what columns there are to import. Depending on the trace, many will be blank, null, or unimportant. Next, I monkey with the following SELECT...INTO command, specifying only those columns I want to import (which should save you space and time!). Again, double-check the column names, I haven't tested this much on 2005:

  SELECT

  -- Key

  SPID

  ,EventClass

  ,ApplicationName

  ,DatabaseID

  ,DatabaseName -- It gets populated in 2005!

  ,HostName

  ,LoginName

  ,ObjectID

  ,StartTime

  -- Very Common

  ,ObjectName

  ,CPU

  ,Duration

  ,Reads

  ,Writes

  ,RowCounts

  -- Common

  ,EndTime

  ,IntegerData

  ,TextData

  -- -- Uncommon

  -- ,BinaryData

  -- ,Error

  -- ,EventSubClass

  -- ,IndexID

  -- ,Mode

  -- ,NestLevel

  -- ,ObjectType

  -- ,Severity

  -- ,State

  -- -- Unknown

  -- ,ClientProcessID

  -- ,ColumnPermissions

  -- ,FileName

  -- ,Handle

  -- ,LoginSid

  -- ,OwnerName

  -- ,Permissions

  -- ,RoleName

  -- ,Success

  -- ,TargetLoginName

  -- ,TargetLoginSid

  -- ,TargetUserName

  -- ,TransactionID

  -- -- Pointless

  -- ,DBUserName

  -- ,NTDomainName

  -- ,NTUserName

  -- ,ServerName

  into

  from ::fn_trace_gettable(' ', default)

  (I left in my personal comments--you're mileage will vary!)

  In your case, I don't see any reason not to stick a WHERE clause on this:

  ...

  into

  from ::fn_trace_gettable(' ', default)

  where color = 'red'

  ...or whatever (where textdata like '%red%', perhaps?) That should cut down on the number of rows you're importing.

  After I do that, if I really need to work it over I load it into a star schema for serious analysis--but that too I haven't really upgraded to 2005.

  Good luck!

  Philip