June 13, 2017 at 9:37 am
All, I am running into issues when I run the trace. "Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070035(The network path was not found.).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file."
I am running it from my local machine so I was specifying the file name which I created on my local machine. So I thought maybe that was the issue. Then I created a trace file on the server but I am getting the same error. Can some help me figure this out?
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
DECLARE @EndTime DATETIME
set @EndTime = DateAdd(mi, 20, GetDate())
set @maxfilesize = 100
exec @rc = sp_trace_create
@TraceID output,
2,
N'\\D:\Trace.trc',
@maxfilesize,
@EndTime,
20 -- @filecount
--if (@rc != 0) goto error
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setstatus @TraceID, 1
June 13, 2017 at 9:46 am
Is d:\ a valid path on the server, and does the service account have rights? I later versions of SQL Server, usually root access is denied.
June 13, 2017 at 9:46 am
easy fix, the path for the file is wrong. it has slashes like a UNC path,a s well as a drive letter. it's one or the other. Maybe you mean \\MyWorkstationName\d$\SomeFolder\MyTrace.log or something? it might still fail, better if it is on the server.
N'\\D:\Trace.trc' should be a valid directory on the server, ie N'D:\AFolderNotTheRoot\Trace.trc'
I usually leave all trace files int eh default folder,w hich is where the default trace is stored.
declare @PathAndFile nvarchar(max);
select @PathAndFile = convert(nvarchar(max), value)
from ::fn_trace_getinfo(0) i
join sys.traces t on t.id = i.traceid
where t.is_default = 1 and i.property = 2;
set @PathAndFile = reverse(substring(reverse(@PathAndFile), charindex('\', reverse(@PathAndFile)), 500)) + N'MyNewTrace.trc'
--C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\MyNewTrace.trc
SELECT @PathAndFile
Lowell
June 13, 2017 at 9:54 am
Steve Jones - SSC Editor - Tuesday, June 13, 2017 9:46 AMIs d:\ a valid path on the server, and does the service account have rights? I later versions of SQL Server, usually root access is denied.
Silly question. Do I find out if I have rights on the folder since I am running the trace or the service account in which sql server instance is running under? I also removed \\ from the path and ran it again and now I am getting "Access is denied" error.
June 13, 2017 at 9:57 am
newdba2017 - Tuesday, June 13, 2017 9:54 AMSteve Jones - SSC Editor - Tuesday, June 13, 2017 9:46 AMIs d:\ a valid path on the server, and does the service account have rights? I later versions of SQL Server, usually root access is denied.Silly question. Do I find out if I have rights on the folder since I am running the trace or the service account in which sql server instance is running under? I also removed \\ from the path and ran it again and now I am getting "Access is denied" error.
The SQL Server service account is going to need rights on the target folder. At least write / read / modify (which will pull in a few other permissions,) or just grant the service account full control on the folder.
June 13, 2017 at 10:09 am
jasona.work - Tuesday, June 13, 2017 9:57 AMnewdba2017 - Tuesday, June 13, 2017 9:54 AMSteve Jones - SSC Editor - Tuesday, June 13, 2017 9:46 AMIs d:\ a valid path on the server, and does the service account have rights? I later versions of SQL Server, usually root access is denied.Silly question. Do I find out if I have rights on the folder since I am running the trace or the service account in which sql server instance is running under? I also removed \\ from the path and ran it again and now I am getting "Access is denied" error.
The SQL Server service account is going to need rights on the target folder. At least write / read / modify (which will pull in a few other permissions,) or just grant the service account full control on the folder.
Well, I just had system admin give me full rights as well as the service account with full rights but I am still getting access issues
June 13, 2017 at 10:27 am
the root of all harddrives are protected. N'D:\MyTrace.log' is invalid due to those protections.
you pretty much HAVE to put files in a subfolder.
Lowell
June 13, 2017 at 10:35 am
Permission problem solved and trace file has been created.
June 13, 2017 at 10:49 am
Lowell - Tuesday, June 13, 2017 10:27 AMthe root of all harddrives are protected. N'D:\MyTrace.log' is invalid due to those protections.
you pretty much HAVE to put files in a subfolder.
ARGH! Thanks Lowell, I completely forgot about that part (and I'd read your previous comment about it to boot.)
June 13, 2017 at 1:09 pm
Thanks everyone for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply