Loading Trace Files To A Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » Loading Trace Files To A Table

16 posts, Page 1 of 2

1 2 »»

Loading Trace Files To A Table

Rate Topic

Display Mode

Topic Options

Author

Message

chinn

Posted Tuesday, July 28, 2009 2:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:32 AM
Points: 127, Visits: 490

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

Post #761110

GSquared

Posted Tuesday, July 28, 2009 3:05 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #761130

Lowell

Posted Tuesday, July 28, 2009 3:29 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 11,614, Visits: 27,672

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #761153

Scott Coleman

Posted Tuesday, July 28, 2009 6:30 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:38 AM
Points: 2,818, Visits: 1,037

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%'

Post #761214

chinn

Posted Wednesday, July 29, 2009 6:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:32 AM
Points: 127, Visits: 490

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....

Post #761429

GSquared

Posted Wednesday, July 29, 2009 7:22 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #761528

chinn

Posted Wednesday, July 29, 2009 7:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:32 AM
Points: 127, Visits: 490

Hello,

Is there a way we can do that Programatically.
Ignore the file that is being used...

Thanks,

Post #761534

Lowell

Posted Wednesday, July 29, 2009 7:36 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:27 AM
Points: 11,614, Visits: 27,672

there is a script submission that loads all the trace files into a table here:
Default Trace Load of All 5 Files

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

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #761547

Jack Corbett

Posted Wednesday, July 29, 2009 10:17 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #761758

GSquared

Posted Thursday, July 30, 2009 6:50 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #762364

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions