4

Scenario

Debugging a failed transaction, I asked the DBA to run a trace on SQL Server. Eventually he send me a .trc file.

Issue

I use the fn_tracegettable() function to access the trace:

select * from sys.fn_trace_gettable('C:\path\to\myTraceFile.trc', default)

But I get the following error:

  Msg 567, Level 16, State 7, Line 1
  File 'C:\path\to\myTraceFile.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

Facts

  • The SQL Management Studio I am executing the fn_tracegettable from runs in my local machine.
  • The .trc file is in my local machine.
  • Both the server and the local machine run a SQL Server 2008 R2.

migrated from stackoverflow.com May 31 '13 at 13:16

This question came from our site for professional and enthusiast programmers.

  • I'm not sure if it makes a difference, but what is the version of the server instance where the trace was collected, and what is the version of your local instance? – Jon Seigel May 31 '13 at 13:26
  • @Jon Seigel: fact added in my question. – Alberto De Caro May 31 '13 at 13:34
5

I had the same issue. I ran an SQL trace on a remote server and transferred the trace files to a local directory on my workstation so that I load the data into a table on my local SQL Server instance for running queries against.

SELECT * INTO ProfileTracesTable
FROM ::fn_trace_gettable('C:\Users\anthony\Documents\SQL_traces\first.trc', default)

However, each time I tried, I got the following error:

File 'C:\Users\anthony\Documents\SQL_traces\first.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

At first I thought the error might be related permission but I ruled this out since I had no problem loading the .trc files directly into SQL Profiler or as a file into SSMS.

After trying a few other ideas, I thought about it a bit more and realised that it was due to permissions after all: the query was being run by the SQL Server process (sqlsrvr.exe) as the user NT AUTHORITY\NETWORK SERVICE – not my own Windows account.

The solution was to grant Read and Execute permissions to NETWORK SERVICE on the directory that the trace files were stored in and the trace files themselves.

You can do this by right-clicking on the directory, go to the Security tab, add NETWORK SERVICE as a user and then select Read & Execute for its Permissions (this should automatically also select Read and List folder contents). These file permissions (ACLs) should automatically propagate to the directory contents (if not, open the Properties of the folder and navigate to the Security → Advanced → Permissions tab and Enable Inheritance).

If you prefer to use the command line, you can grant the necessary permissions to the directory – and its contents – by running the following:

icacls C:\Users\anthony\Documents\SQL_traces /t /grant "Network Service:(RX)"
  • Had the same problem on Server2012 and this solution didn't work. You have to go to the properties of the folder > security > advanced > Permissions tab and click 'Enable Inheritance'. – John Alan Oct 20 '16 at 13:39
  • Thanks for the feedback @JohnAlan When a Windows ACE is created for an object, the default is for child objects to inherit all permissions from their parent. Since this can be changed, I intentionally used the word “should” in my answer. However, I omitted the instructions for enabling inheritance on the presumption that the user would know how (I've now included an extra sentence). Also, the command line includes the /T flag to ensure that the ACE recursively applies to all files in the directory tree. – Anthony Geoghegan Oct 20 '16 at 14:14
1

Why are you opening the file in the SQL Management Studio? Use the SQL Server Profiler tool instead to open .trc files.

  • Unfortunatly, SQL Server Profiler tool is not available in my SQL Management. – Alberto De Caro May 30 '13 at 12:52
  • You should install the Profiler tool then. In SQL Management Studio are you connecting to a local DB instance? – bastos.sergio May 30 '13 at 12:57
  • Just FYI you can open trace files like that, the columns & rows show up in the results pane just like a normal query. – Meff May 30 '13 at 13:37
1

You are running query on the database server. The path in the query is the path on the actual database server and not your local c drive.

Some of your options: 1. Have the DBA put the file on the server and give you the servers path. 2. Have a local instance of SQL Server and use the local path on your desktop. 3. Install SQL Profiler and open it there.

  • I edited my question: everything is running in my local machine. – Alberto De Caro May 30 '13 at 16:11
  • Where is the actual database server that Management Studio is connected to? To find out, run the statement: select @@SERVERNAME – Chris H May 30 '13 at 17:24
0

Simple way to import .trc file without any errors by opening file into SQL Profiler tool -> File -> Save as -> Trace Table -> SQL Table import option will pop up.

Good Luck

  • This is not about importing a trace file really, this is about accessing it in Transact-SQL (particularly, using the sys.fn_trace_gettable function). – Andriy M Aug 27 '15 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.