Sql-server – Cannot open SQL Server profiler file

profilersql server

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.

Best Answer

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)"
Related Question