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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy