To create a stored procedure for the profiler:
Use [Xxxdb]GO/** * * * object:storedprocedure [dbo]. [CreateProfile] Script DATE:2015/12/16 17:23:32 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOCREATE proc [dbo].[CreateProfile] as--Create a QueueDeclare @rc intDeclare @TraceID intDeclare @maxfilesize bigintDeclare @tracefile nvarchar( the)Set @maxfilesize = - --set the maximum trace file size to 500MSet @tracefile = 'd:\tracefile\'+Convert(Char(8),getdate(), the)--Please replace the text insertfilenamehere, with an appropriate--filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The. trc extension--'ll is appended to the filename automatically. If you is writing from--remote server to local drive, sure server with UNC path and make--Write access to your network shareexec @rc =Sp_trace_create@TraceIDOutput0,@tracefile,@maxfilesize,NULL --Create a Traceif(@rc != 0)GotoError--Client side File and Table cannot be scripted--Set The Events--set up trace events--sp_trace_setevent [@traceid =] trace_id, [@eventid =] event_id, [@columnid =] column_id, [@on =] on which on= 1 Open on=0 stop on=2 offDeclare @on bitSet @on = 1execSp_trace_setevent@TraceID,Ten,1,@onexecSp_trace_setevent@TraceID,Ten,9,@onexecSp_trace_setevent@TraceID,Ten,2,@onexecSp_trace_setevent@TraceID,Ten,4,@onexecSp_trace_setevent@TraceID,Ten,8,@onexecSp_trace_setevent@TraceID,Ten, One,@onexecSp_trace_setevent@TraceID,Ten, A,@onexecSp_trace_setevent@TraceID,Ten, -,@onexecSp_trace_setevent@TraceID,Ten, -,@onexecSp_trace_setevent@TraceID,Ten, -,@onexecSp_trace_setevent@TraceID,Ten, -,@onexecSp_trace_setevent@TraceID,Ten, -,@onexecSp_trace_setevent@TraceID,Ten, *,@onexecSp_trace_setevent@TraceID,Ten, the,@onexecSp_trace_setevent@TraceID, A,1,@onexecSp_trace_setevent@TraceID, A,9,@onexecSp_trace_setevent@TraceID, A, One,@onexecSp_trace_setevent@TraceID, A,4,@onexecSp_trace_setevent@TraceID, A,8,@onexecSp_trace_setevent@TraceID, A, A,@onexecSp_trace_setevent@TraceID, A, -,@onexecSp_trace_setevent@TraceID, A, -,@onexecSp_trace_setevent@TraceID, A, -,@onexecSp_trace_setevent@TraceID, A, -,@onexecSp_trace_setevent@TraceID, A, -,@onexecSp_trace_setevent@TraceID, A, *,@onexecSp_trace_setevent@TraceID, A, the,@on--Set the FiltersDeclare @intfilter intDeclare @bigintfilter bigintSet @bigintfilter = 4000000execSp_trace_setfilter@TraceID, -,0,4,@bigintfilter--Set The trace status to startexecSp_trace_setstatus@TraceID,1--Display Trace ID for the future referencesSelectTraceid=@TraceIDGotoFinisherror:SelectErrorCode=@rcFinish:GO
To end the profiler's stored procedure:
Create proc [dbo].[Stopprofile] asDeclare @traceid intSelect @traceid =ID fromsys.traceswherePath like 'd:\tracefile%' --path is the storage route for the trace fileexecSp_trace_setstatus@traceid,0execSp_trace_setstatus@traceid,2
The time at which the profiler is turned off can be changed as needed in the SQL Server Agent's job.
Profiler joins a scheduled task