October 19, 2011 at 7:44 am
Have you checked the permissions are as it is desired?
October 19, 2011 at 7:52 am
What windows error you are getting?
You can also use UNC to a share (Everyone must have write permissions on it) e.g. (N'\\Servername\Sharename\Directory\trace.trc'). This would make sure that the SQL service account is able to write to the trace file. This would narrow down the potential issues.
October 19, 2011 at 8:13 am
Please also make sure that you are looking at the correct server's directory. I had same kind of a problem once, when I was trying to look into the folders of different server while working on a different server. I hope you are not doing the same mistake.
October 19, 2011 at 8:23 am
Hi Team,
As per your suggestion i have checked every thing but i m getting below Errors please suggest me
1. Trace is not creating
Windows error occurred while running SP_TRACE_CREATE. Error = 0x80070003(The system cannot find the path specified.).
Msg 19062, Level 16, State 1, Procedure sp_trace_create, Line 1
Could not create a trace file.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setevent, Line 1
Could not find the requested trace.
Msg 19059, Level 16, State 1, Procedure sp_trace_setstatus, Line 1
Could not find the requested trace.
October 19, 2011 at 8:33 am
Shiv,
Once again I would ask have you checked the required permissions? Does the specified folder already exists there? Are you looking at the same server where your SQL Server resides? Have you tried the UNC path? Please answer these questions and also mention how you did all these checks. Thanks.
October 19, 2011 at 8:59 am
Hi
Thank you for your help
the specified folder is there. yes i m looking same server only. Everything was in same folder only
I think that Sp_trace_create is prob can any body please suggest me.
Thanks In Advance.
October 20, 2011 at 12:00 am
Sorry to say, but you have not answered about the permissions. Can you please check that one as well. Can you please run the following script and send the output. Please do read the comments in the script before execution and act accordingly. You must replace the username 'sa' with the user you are trying to execute sp_trace_create
DECLARE @UserName sysname
SET @UserName = N'sa' --Replace it with the user's name you are using
SELECT 'IS_PERMITTED_TO_ALTER_TRACE', HAS_PERMS_BY_NAME(null, null, 'ALTER TRACE') BitValue;
SELECT 'Role: ' + SP1.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP
JOIN sys.server_role_members SRM
ON SP.principal_id = SRM.member_principal_id
JOIN sys.server_principals SP1
ON SRM.role_principal_id = SP1.principal_id
WHERE [SP].[name] = @UserName
UNION ALL
SELECT SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission' FROM sys.server_principals SP
JOIN sys.server_permissions SPerm
ON SP.principal_id = SPerm.grantee_principal_id
WHERE [SP].[name] = @UserName
/*==FOR BELOW LINES OF CODE TO BE EXECUTED SUCCESSFULLY, xp_cmdshell should be enabled ==*/
EXEC master..xp_cmdshell 'Dir C:\tracefileName.trc'
EXEC master..xp_cmdshell 'mkdir C:\testdirectoryxyz_123'
/*==CAUTION!!!!DELETE THE DIRECTORY BY EXECUTING THE BELOW COMMENTED LINE IF YOU ARE SURE IT IS NOT A WORKING DIRECTORY ==*/
--EXEC master..xp_cmdshell 'rmdir C:\testdirectoryxyz_123'
October 21, 2011 at 2:39 am
Hi Team,
i have SA permissions
the above code O/P is :
Role: sysadmin
GRANT CONNECT SQL
Thank your reply..
October 21, 2011 at 6:22 am
Shiv,
You have not appended the output of xp_cmdshell commands. There would have been some output if ran successfully. So please send the output (or any error) for below scripted lines
/*==FOR BELOW LINES OF CODE TO BE EXECUTED SUCCESSFULLY, xp_cmdshell should be enabled ==*/
EXEC master..xp_cmdshell 'Dir C:\tracefileName.trc'
EXEC master..xp_cmdshell 'mkdir C:\testdirectoryxyz_123'
EXEC master..xp_cmdshell 'Dir C:\testdirectoryxyz_123'
/*==CAUTION!!!!DELETE THE DIRECTORY BY EXECUTING THE BELOW COMMENTED LINE IF YOU ARE SURE IT IS NOT A WORKING DIRECTORY ==*/
--EXEC master..xp_cmdshell 'rmdir C:\testdirectoryxyz_123'
PS: You are not giving yourself any favor by not following the instructions entirely
October 21, 2011 at 10:31 am
Hi Team,
I got this o/p:-
Volume in drive C is Local Disk
Volume Serial Number is C0DC-EBB4
NULL
Directory of C:NULL
File Not Found
NULL
Volume in drive C is Local Disk
Volume Serial Number is C0DC-EBB4
NULL
Directory of C:\testdirectoryxyz_123
NULL
10/21/2011 04:33 AM <DIR> .
10/21/2011 04:33 AM <DIR> ..
0 File(s) 0 bytes
2 Dir(s) 60,614,381,568 bytes free
NULL
October 25, 2011 at 4:20 am
Can you please send the output (appended with any error if occurs) of this script
BEGIN TRY
IF OBJECT_ID('tempdb..#Output') IS NOT NULL
DROP TABLE #Output
CREATE TABLE #Output
(
Counter INT IDENTITY(1, 1)
,DirOutput VARCHAR(1000)
,[TextData] nvarchar(MAX)
,[DatabaseID] INT
,[LineNumber] INT
,[LoginName] NVARCHAR(256)
,[SPID] INT
,[Duration] BIGINT
,[StartTime] DATETIME
,[EndTime] DATETIME
)
INSERT [#Output]
(
[DirOutput],
[TextData]
)
SELECT 'IS_PERMITTED_TO_ALTER_TRACE', HAS_PERMS_BY_NAME(null, null, 'ALTER TRACE') BitValue;
INSERT [#Output]
(
[DirOutput]
)
EXEC master..xp_cmdshell 'Dir C:\testdirectoryxyz_123'
INSERT [#Output]
(
[DirOutput]
)
EXEC master..xp_cmdshell 'mkdir C:\testdirectoryxyz_123'
INSERT [#Output]
(
[DirOutput]
)
EXEC master..xp_cmdshell 'Dir C:\testdirectoryxyz_123'
--declare variables for parameterizing the command
declare @traceidout int
declare @options int
declare @path nvarchar(256)
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @stoptime datetime
declare @on bit
set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.
set @maxfilesize = 20 --size in MB
set @maxRolloverFiles = 5 --number of files; ie if 5 files, start rewriting on rollover
set @stoptime = NULL -- null if never ends, else a specific date
set @options = 2 -- TRACE_FILE_ROLLOVER = TRUE, SHUTDOWN_ON_ERROR = FALSE
set @path = 'C:\testdirectoryxyz_123\testTraceFileName' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting
--create the trace
exec sp_trace_create @traceidout output, @options, @path, @maxfilesize, @stoptime, @maxRolloverFiles
--for the Event Every SQL statement completed, capture columns of accessible data
exec sp_trace_setevent @traceidout,22,3,@on --ErrorLog,DatabaseID
exec sp_trace_setevent @traceidout,22,12,@on --ErrorLog,SPID
exec sp_trace_setevent @traceidout,33,3,@on --Exception,DatabaseID
exec sp_trace_setevent @traceidout,33,12,@on --Exception,SPID
exec sp_trace_setevent @traceidout,162,3,@on --User Error Message,DatabaseID
exec sp_trace_setevent @traceidout,162,12,@on --User Error Message,SPID
exec sp_trace_setevent @traceidout,40,1,@on --SQL:StmtStarting,TEXTDATA
--filters
-- WHERE 1 = 1
---final step
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
SELECT * FROM [#Output] AS O WHERE 1 = 2
WAITFOR DELAY '00:00:05'
exec sp_trace_setstatus @traceidout, 0 ---stop trace, you must know the traceid to stop it
exec sp_trace_setstatus @traceidout, 2 ---close trace you must know the traceid to delete it
INSERT [#Output]
(
[DirOutput]
,[TextData]
,[DatabaseID]
,[LineNumber]
,[LoginName]
,[SPID]
,[Duration]
,[StartTime]
,[EndTime]
)
SELECT 'TRACEOUT'
, [TextData]
, [DatabaseID]
, [LineNumber]
, [LoginName]
, [SPID]
, [Duration]
, [StartTime]
, [EndTime]
FROM fn_trace_gettable(N'C:\testdirectoryxyz_123\testTraceFileName.trc', DEFAULT);
INSERT [#Output]
(
[DirOutput]
)
EXEC master..xp_cmdshell 'Dir C:\testdirectoryxyz_123'
SELECT * FROM [#Output] AS O
DROP TABLE [#Output]
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_LINE()
SELECT * FROM [#Output] AS O
DROP TABLE [#Output]
END CATCH
Please note that this would create a folder "testdirectoryxyz_123" on your C drive, which should be deleted once you are done with the execution of the script.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply