Extended Event To Capture Proc Execution

  • RonMexico

    Hall of Fame

    Points: 3340

    I configured an extended event to capture the time procedures are executed and below is what I have so far

    CREATE EVENT SESSION [PROC_EXEC] ON SERVER 
    ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1),collect_statement=(0)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.username)
    WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[database_name],N'master, model, tempdb, msdb, UserDB1, UserDB2')))
    ADD TARGET package0.event_file(SET filename=N'C:\PROC_EXEC.xel',metadatafile=N'C:\PROC_EXEC.xem')
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO

    I'm having a few issues with it.

    1. You can see I'm trying to exclude the system databases and a couple of user databases but the user databases aren't being filtered out. Is something wrong with the syntax?
    2. I would like to only capture user stored procedure execution. Is there a way to do that?
    3. I would also like to get the schema in which the proc is located. How would I add that?
  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply