June 3, 2009 at 3:13 am
So I have run a trace with profiler, which results in an almost 1Gb file. Next I wish to get this data into a table with fn_trace_gettable and get the message:
...either does not exist or is not a recognizable trace file. Or there was an error opening the file.
Googling the Net I read a lot about this error in SQL2K and that it is a known bug. One should use the appropriate stored procedures to create a tracefile and then I can use fn_trace_gettable... Using these sp's is a real pain, far easier to use Profiler. It seems this is still not solved?! Any1 knows anything about this?
EDIT: Found another way, but I am still curious why this does not work with a Profiler generated TRC.
Greetz,
Hans Brouwer
June 3, 2009 at 8:06 am
What OS is the SQL Server on? I have had this problem when testing on VISTA where it is a security issue.
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
June 3, 2009 at 9:33 am
Do you know what is trace #
close/drop that trace it is still running with the following command and then try.
select * from ::fn_trace_getinfo(1) -- to see is trace their
sp_trace_setstatus 1,0 -- to stop the trace 1 is trace #
sp_trace_setstatus 1,2 --to drop the trace 1 is trace #
then try with
select * from ::fn_trace_gettable(' path\tracenm.trc',-1)
HTH
Vin
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
June 4, 2009 at 7:32 am
Jack, I run profiler on my local pc with WXP, servers are W2003 EE with SQL2K5 SE. I believe it to be the known bug existing in SQL2K; if I am mistaken I would really like to hear this.
Vinay,
I can't try your fn_trace_gettable anymore, since I don't have the tracefile anymore. I scripted the trace and ran it in a job, seemed to me the easiest solution. I did not use yhe '-1' value, though. Maybe I'll try to see if it makes a difference.
Tnx for answering all.
Greetz,
Hans Brouwer
June 4, 2009 at 8:38 am
I found 2 bugs listed for fn_trace_gettable in SQL Server 2000. This one was fixed in SP3. This one does not have a fix listed.
I have not found anything listed for SQL Server 2005. I've tested the first one against 2005 and do not see the problem. I am working on test the second one against 2005 and will let you know the results.
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
June 4, 2009 at 8:40 am
FreeHansje (6/3/2009)
EDIT: Found another way, but I am still curious why this does not work with a Profiler generated TRC.
What's the other way you found?
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
June 4, 2009 at 9:47 am
I can't duplicate your error, of course I don't have a 1GB file either. I have included all the columns mentioned in the 2000 KB error and have multiple (53) small (1MB) files. I'm not getting any errors.
I'm wondering if the problem is the size of the file. Can you do the same Profiler trace that produces a smaller file and see if you have the same problem?
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
June 5, 2009 at 6:06 am
Jack,
I'll ttry a smaller filesize, I used 50 Mb.
The other way is crude but simple: I scripted the trace and ran it in a job. Had to stop the trace after the proces I was profiling for had ended, and then I could use fn_trace_gettable without problem.
Tnx for your effort.
Greetz,
Hans Brouwer
February 2, 2010 at 12:10 pm
Hi,
We have same issue still searching to figure out the issue. Some times the trace file data will be loaded into SQL table and some times not. We got error like: The trace files does not exist. Actually the trace files already exist on the folder. Is it bug for fn_trace_gettable function in SQL Server 2000/2005.
Any other solutions for this issue ?
February 2, 2010 at 12:16 pm
I use fn_trace_gettable in SQL 2005 and 2008 all the time. I've never yet had a problem with it. On the other hand, I always create my traces via scripts and procs, so there may be a difference in that.
If profiler is still open, it will have a lock on the file, and the function won't be able to access it (usual Windows rules on file locks). Is it possible that's the problem?
I've parsed some pretty huge trace files this way, and the only problem I run into is that it takes a long time.
- 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
February 2, 2010 at 4:59 pm
I am aslo using batch files(scripts) and OSQL to load the data into table. The trace files data will be loaded in other server(Remote Server). Here i am giving the Trace file location like \\RemoteServername\d$\trace\.
error like: The trace files does not exist. Actually the trace files already exist on the folder
Is it the problem while loading remote files ? Is there any folder level locks on *.trc files?
Is there any ideas or suggestions to resolve the issue.
Thanks in advance.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply