Loading Trace Files To A Table

Question

Loading Trace Files To A Table

chinn

SSCrazy

Points: 2840
More actions
July 28, 2009 at 2:36 pm

#96093

Hello,
We run profiler traces for auditing logins and i am using server side tracing and everything works great.
I just want to automate loading of trace files into a table without stopping trace..i know i cant use this...
SELECT * INTO temp_trc
FROM ::fn_trace_gettable(c:\my_trace.trc", default)
because one file will be always be used..i some how need to get info of the file being used or capture the file sizes so that i can get the name of the files...and load into the table....something...on those lines...any lead would be greatly appreciated...Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

You can use xp_cmdshell to query the files in a directory. Would that allow you to do what you want?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323504 More actions · Answer 2

why not create a view, so it is always ready on demand?

CREATE VIEW MyTrace As

Select * from FROM ::fn_trace_gettable(c:\my_trace.trc", default)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

You can get the trace file names in use from fn_trace_getinfo, assuming you know the traceid.

SELECT value

FROM fn_trace_getinfo( @traceid )

WHERE property = 2

Or if the traceid is uncertain but the base filename is known you could try

SELECT value

FROM fn_trace_getinfo(0)

WHERE property = 2 AND value LIKE '%filename%'

chinn SSCrazy Points: 2840 More actions · Answer 4

Hello All....

Thanks For Your Inputs...

But my question is how do i Know..which one is currently being used or written to because i want to ignore that and load the rest of them into a table.

For example...

C:\trace\logins.trc---current one 0 bytes..

c:\trace\logins1.trc

C:\trace\logins2.trc...i want to ignore logins.trc file because if i include that it(loading of trace files) will fail.

Thanks....

GSquared SSC Guru Points: 260824 More actions · Answer 5

If you query a list of the files, it would be very easy to not use the last one in the list. That's why I suggested that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

chinn SSCrazy Points: 2840 More actions · Answer 6

Hello,

Is there a way we can do that Programatically.

Ignore the file that is being used...

Thanks,

Lowell SSC Guru Points: 323504 More actions · Answer 7

there is a script submission that loads all the trace files into a table here:

Default Trace Load of All 5 Files[/url]

you could easily change the command from '*.trc" to logins*.trc" to get all the specific ones you were looking for.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184393 More actions · Answer 8

Lowell,

That script is 2005/2008 specific and this is a 2000 forum. You'd have to modify the script to use Scott's solution to get the path.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 9

Try this for a list of the files:

create table #T (

ID int identity primary key,

FName varchar(256));

--

insert into #T (FName)

exec master..xp_cmdshell 'dir MyTraceDirectory\*.trc /b';

--

select *

from #T;

You'll have to put in the actual directory, of course.

Once you have that list, it should be very easy to select everything except the last file, and to load up the trace table from that. It's easier in SQL 2005, because you could construct a simple Except argument, but it's not that difficult in 2000 to set up a comparable outer join or Where Not In.

That should give you what you need, right?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

chinn SSCrazy Points: 2840 More actions · Answer 10

Thanks...Very Much!

I got an idea from your previous post...Thanks for you help!

chinn SSCrazy Points: 2840 More actions · Answer 11

Guys,

One quick question...

When running Server Side Tracing...if we restart sql server ..Do i need to just start the existing trace

or

Recreate the Trace

In other words..will the trace be stopped or closed(deletes the definition)

Thanks,

Jack Corbett SSC Guru Points: 184393 More actions · Answer 12

The trace definition is deleted. You will need to recreate it and restart it. If this is something you always want running you may want to encapsulate it in a stored procedure and mark the stored procedure for startup. Here's a link for that. Or you can put it in a job that runs on startup.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

GSquared SSC Guru Points: 260824 More actions · Answer 13

You'll have to re-create it. I set them to do that in an auto-start proc that fires off when the SQL service starts.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

chinn SSCrazy Points: 2840 More actions · Answer 14

Thanks Very Much For You Prompt replies.

Thanks Again...