Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Loading Trace Files To A Table Expand / Collapse
Author
Message
Posted Tuesday, July 28, 2009 2:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
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
Posted Tuesday, July 28, 2009 3:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Tuesday, July 28, 2009 3:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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
Posted Tuesday, July 28, 2009 6:30 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:36 PM
Points: 2,850, Visits: 1,165
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
Posted Wednesday, July 29, 2009 6:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
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
Posted Wednesday, July 29, 2009 7:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Wednesday, July 29, 2009 7:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:37 PM
Points: 127, Visits: 553
Hello,

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

Thanks,





Post #761534
Posted Wednesday, July 29, 2009 7:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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
Posted Wednesday, July 29, 2009 10:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
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
Posted Thursday, July 30, 2009 6:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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 »

Add to briefcase 12»»

Permissions Expand / Collapse