Create a new Trace
In the “Events Selection” tab, click on “Show all events” and select these…
- Stored Procedures -> RPC:Starting
- Stored Procedures -> RPC:Completed
- TSQL -> SQL:BatchStarting
- TSQL -> SQL:BatchCompleted
In the “Events Selection” tab, make sure the “TextData” is selected and click on “Column Filters”
In the TextData column add a filter:
Like %uspMySP01%
Note:
- Make sure the TextData is checked.
- Make sure you include your stored procedure name between %% because sometime the stored procedure name can be called with additional to other data.
- Include both RPC (SP execute from SSRS) and TSQL (SP executed from SSMS).
- Include “Starting” if you want to track when the SP started. The “Completed” will also include both Starting and Ending time.
TextData Examples:
declare @p1 dbo.MyUserTableType
insert into @p1 values(N'Test',N'',N'User1')
insert into @p1 values(N'Test',N'',N'User2')
exec dbo.uspMySP01 @tbMyTable=@p1
exec uspMyOtherSP02
exec uspMy03 @ipv_biTableId=123
exec uspMySP04 @ipv_Param1=N'test',@ipv_Param2=136
exec sp_executesql N'EXECUTE [msdb].[dbo].[sp_sqlagent_update_jobactivity_requested_date] @session_id = @P1, @job_id = @P2, @is_system = @P3, @run_requested_source_id = @P4',N'@P1 int,@P2 uniqueidentifier,@P3 int,@P4 int',4
Comments