October 23, 2007 at 8:14 am
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
October 23, 2007 at 8:55 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply