Trace file loading into Table

Question

Trace file loading into Table

John Burris

Right there with Babe

Points: 748
More actions
December 7, 2009 at 3:31 pm

#228397

I am creating a trace file for all SP that execute on my server. A new file is created daily. That part works. I am trying to load them into a table using this command.
insert INTO StoredProcedures
SELECT EventClass,
ApplicationName,
NTUserName,
LoginName,
CPU,
Reads,
Writes,
Duration,
SPID,
StartTime,
EndTime,
DatabaseID,
DatabaseName,
Error,
HostName,
IsSystem,
ObjectName,
RowCounts,
BinaryData
FROM ::fn_trace_gettable('\\xxx.xxx.xxx.xxx\F$\trace files\sp_trace4122009.trc', default)
I get this error
Msg 567, Level 16, State 7, Line 1
File '\\xxx.xxx.xxx.xxx\F$\trace files\sp_trace4122009.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
I am running sql 2008 enterprise on Windows server 2008 enterprise.
I first thought it was a permissions error, but I have opened the folder and file up to everybody, domain user, and the user account of the sql service to full rights.
anybody got a thought on why I am not able to load the file?
[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

The file could be corrupted. Have you tried opening it with profiler? If it opens there it is unlikely that it is corrupted.

I did notice you are using a default share, is the user trying to access the file (in this case SQL) in the administrators group on the machine? Better question, does SQL login as a user or is it something like LocalSystem or Network Service?

CEWII

John Burris Right there with Babe Points: 748 More actions · Answer 2

I am using a UNC Path because the examples all used them. I have also tried f:\trace files\sp_trace4122009.trc and F$\trace files\sp_trace4122009.trc to go directly to the file. The file is on the same machine.

I can open the file in Profiler it comes up just fine.

My sql server service is using a domain account. I have given the file and the folder full permissions for that user. I then gave that folder open permissions for everyone and domain users to test out the permissions. I am willing to try almost anything along the permissions line that is suggested.

I have tried using sa and setting this up in a sql job, both with the same result.

I hope this answers you questions and please ask more if you need more info.

John

[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Ok, so let me make sure I understand.

1. Using UNC but direct references (ie: F:\dir\file.trc) also fail.

2. Opens ok in profiler.

3. SQL/Agent login as domain account.

4. File and directory have wide open permissions.

5. Tried it as a job but no good.

What all that tells me is that it is unlikely to be a corrupted file (#2). But I'm wondering if you have tried a direct reference (F:\dir\file.trc) before or after #4. The reason I ask is that there could have been a permissions issue but was solved. Not sure, that might be barking up wrong tree. If you are using UNC I wouldn't use the default shares (C$, D$, etc.) because they are limited to what groups can access them and they can't be changed.

I would verify the filename, use a direct reference, verify the permissions on the file, and simplify the query, such as:

SELECT * FROM ::fn_trace_gettable('F:\trace files\sp_trace4122009.trc', 1)What I'm trying to do here is take as many variables out of the equation as possible. A file named sp_trace4122009.trc must exist at F:\trace files\ and have rights to be read either by Everyone or at least by the domain user that SQL logs in as. You are also only asking for a single file and all fields.

You can verify what login account SQL is using by executing:

EXEC master.dbo.xp_cmdshell 'SET'In the output you should see USERDOMAIN= and USERNAME= they will show you conclusively what account is being used.

CEWII

John Burris Right there with Babe Points: 748 More actions · Answer 4

I think you are seeing everything correct.

I have ran this statement

EXEC master.dbo.xp_cmdshell 'SET' and verified it is running as the user I thought it was.

I then ran this statement

SELECT * FROM ::fn_trace_gettable('F:\trace files\sp_trace4122009.trc', 1)

and I got this

Msg 567, Level 16, State 8, Line 2

File 'F:\trace files\sp_trace4122009.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

I have run all this in the past 5 minutes and still no luck.

Has sql 2008 changed something in the way it loads trace files?

Is there a switch in SQL that turns this ability on? I have not heard of one or came across one in my research, but that does not mean it is not out there.

[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

I'm not aware of a switch either.

The only thing I can think of is the most obvious to check. The file does exist with that name..

You have checked everything I would.. I'm sorry I don't have any more ideas.

I'm sure it is something so obvious and easy that we both just can't see it..

CEWII

John Burris Right there with Babe Points: 748 More actions · Answer 6

OK after racking my brain for about a week I figured out the problem. I did a DIR in dos only to discover that the trace program adds .trc to the already .trc making the file sp_trace4122009.trc.trc. In windows in only showed one .trc even when you rename it. Thank you DOS for showing the truth.

You were right it was to simple of a problem.

Problem solved.

[font="Tahoma"]John Burris:hehe:
MCITP Database Administrator[/font]

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Glad you figured it out, I was plain out of ideas..

CEWII

kk_tech Default port Points: 1492 More actions · Answer 8

I do have same issue and it says "is not a recognizable trace file", I did check with DIR on DOS prompt and it shows only .trc one time.

Any other idea why its causing to run the query. I have tried on local domain and on server but it seems same issue.

Any help?

ccejanhl SSC Journeyman Points: 78 More actions · Answer 9

Verify that the name not have two ".trc" extensions

Trace file loading into Table

Cookies on SQLServerCentral