October 18, 2011 at 5:41 am
Hi Team,
I m was creating Tracing through script in that i m using Events like this UserErrorMessages,Errorlog and Exception in this script any thing wrong please suggest me
2. after tracing is completed the output is saved in usertable xxx please guide me onthis.
declare @rc int
declare @TraceID int
declare @datetime dataetime
declare @stratDt Datetime
---select @startDt=getdate()
exec @rc = sp_trace_create @TraceID output,2
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 162,3,@on
exec sp_trace_setevent @TraceID 22,3, @on
exec sp_trace_setevent @TraceID 33,3 @on
exec sp_trace_setstatus @TraceID, 1
October 18, 2011 at 6:34 am
What i tend to do a lot is to create a view that will allow me to access the trace you created;
then i can simply do something as simple as SELECT * FROM vw_MyCustomTrace to review the data, and decide if i need to archive it to a table or not.
the code inside the view is how you query the trace file, adding a view just makes it convenient.
declare @rc int
declare @TraceID int
declare @datetime dataetime
declare @stratDt Datetime
---select @startDt=getdate()
exec @rc = sp_trace_create @TraceID output,2
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 162,3,@on
exec sp_trace_setevent @TraceID 22,3, @on
exec sp_trace_setevent @TraceID 33,3 @on
exec sp_trace_setstatus @TraceID, 1
--get the path of the trace file you created.
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
declare @path nvarchar(1024)
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),1024)) FROM sys.traces WHERE id = @TraceID --print @path
SET @sql = 'CREATE VIEW master.dbo.vw_MyCustomTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
Lowell
October 18, 2011 at 8:06 am
Hi Team,
I m Executing the above script
in MASTER DB
but i m getting below Error can you please suggest me.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'vw_MyCustomTrace'.
October 18, 2011 at 8:14 am
shiv-356842 (10/18/2011)
Hi Team,I m Executing the above script
in MASTER DB
but i m getting below Error can you please suggest me.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'vw_MyCustomTrace'.
it's a view, not a procedure.......did you try SELECT * FROM vw_MyCustomTrace'
Lowell
October 18, 2011 at 11:04 am
Hi Lowell,
as per your suggestion it is not working
any of team please Drive me on good direction its really helpful to me .
Tx
October 18, 2011 at 11:24 am
define 'not working'
post the exact code you're running. Did you run Lowell's script? Did it succeed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 19, 2011 at 12:41 am
Hi Gial,
i had worked as per your suggestions, can please see below code any thing wrong please correct me because i m new to developing side
Requrement was below
1. Trace is generating
2. that output also saved into one table
3.Im executing the below script but trace file was not generated
DECLARE @TraceID int
declare @rc int
declare @datetime datetime
declare @stratDt Datetime
declare @maxfilesize bigint
declare @startdate datetime
--declare @stoptime datetime
select @startDate=getdate()
exec @rc = sp_trace_create @TraceID output,
@options = 2,
@tracefile = N'C:\Tracefiles\TraceFileName.trc',
@maxfilesize = @maxfilesize,
@filecount=2;
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 162,3,@on
exec sp_trace_setevent @TraceID, 22,3, @on
exec sp_trace_setevent @TraceID, 33,3, @on
exec sp_trace_setstatus @TraceID, 1
select @TraceID as TraceID;
-- Populate a variable with the trace_id of the current trace
SELECT @TraceID = TraceID FROM fn_trace_getinfo(1) WHERE VALUE = N'C:\TraceFileName.trc';
-- Stop the trace.
IF @TraceID IS NOT NULL
EXEC sp_trace_setstatus @TraceID;
BEGIN TRANSACTION;
BEGIN TRY
-- Attempt to load the trace file.
IF OBJECT_ID(N'dbo.xx') IS NOT NULL
DROP TABLE dbo.xx;
SELECT * INTO TraceSQLError --Load into a new table
FROM fn_trace_gettable(N'C:\TraceFileName.trc', DEFAULT);
END TRY
BEGIN CATCH
-- Report any issues that may have arised from the attempt
SELECT
ApplicationName, ServerName,SPID,LoginName,StartTime from TraceSQLError
-- Kick back the transaction if there was a problem
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
Im executing the above script but trace file was not generated
October 19, 2011 at 12:55 am
Hi Lowelly,
as per your suggestion also i have worked but no use
i m getting below Errors
Msg 19065, Level 16, State 1, Procedure sp_trace_create, Line 1
The parameter is not valid.
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.
declare @rc int
declare @TraceID int
declare @datetime datetime
declare @stratDt Datetime
declare @sql nvarchar(300)
---select @startDt=getdate()
exec @rc = sp_trace_create @TraceID output,2
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 162,3,@on
exec sp_trace_setevent @TraceID, 22,3, @on
exec sp_trace_setevent @TraceID, 33,3, @on
exec sp_trace_setstatus @TraceID, 1
--get the path of the trace file you created.
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
declare @path nvarchar(1024)
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),1024)) FROM sys.traces WHERE id = @TraceID --print @path
SET @sql = 'CREATE VIEW master.dbo.vw_MyCustomTrace AS SELECT * FROM ::fn_trace_gettable(''' + @path + '.trc'', default)'
exec(@sql)
Can you any body please guide me as per my Requrement its really helpful tome
Thanks in Advance for your support guys.
October 19, 2011 at 3:19 am
--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:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\TraceFileName' -- 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
--filters
-- WHERE 1 = 1
---final step
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it
SELECT *
FROM fn_trace_gettable(N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\TraceFileName.trc', DEFAULT);
Lowell
October 19, 2011 at 4:37 am
Hi Lowell,
Thank you for your reply...
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.
October 19, 2011 at 5:35 am
you'll have to change the path in two palces;
mine was
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\TraceFileName'
on my machine, running SQLR2, i think you had a different one; change it and you should be fine.
When i tried your trace, i got a couple of other issues, so after i fixed it i scripted it out;
that's the resulting definition.
Lowell
October 19, 2011 at 6:50 am
Hi Team,
As per your suggestion i m working but i m not getting my Desired output please Guide me on good Direction.
October 19, 2011 at 7:31 am
The way you are helping Shiv is more than appreciated. You must have a very cool mind. I believe if Shiv is working on the trace output(a sort of advanced topic), he at-least should have been able to change the path himself after seeing the errors. Moreover, I think the default trace was not enabled on the instance Shiv is using. This is where
SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),1024)) FROM sys.traces WHERE id = @TraceID would have given NULL value.
Could you please make sure that the user have ALTER TRACE permissions as well as the SQL Server service account have Write permission on the trace file folder (the path you are going to change)
October 19, 2011 at 7:35 am
@Gail Shaw
Please if you have time, do add your thoughts on "TRACE_PRODUCE_BLACKBOX" option in sp_trace_create procedure, in order to write in the default directory. At least this will make sure that the SQL Server service account would be able to write the trace file. Thanks.
October 19, 2011 at 7:37 am
Hi Butt,
Thank your reply..
But i have changed the path as per my location the main prob is i m not good developer ok
But i know the systax where i have to do the changes
@ Yes its really appriciate to support to me
and i m getting Windows error also.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply