Profiler Trace

  • Hi,

    I am aware of capturing the trace for a single user database by adding the DB name in the column filters. If I have to filter the trace only for 2 user databases, how can I add one more database name in the column filter which says LIKE 'TESTDB' ( example) ? or just capture for all and not have any filter. I am looking for deadlock events, too early to say what is the root cause.

    Thanks

     

  • Personally, I'd check the SQL Server Error Logs for deadlock events.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I've started creating a separate deadlock extended event myself.

    Here's an example of how to create a deadlock ext event.  Naturally your WITH options may vary.  You can then use SSMS / Management / Extended Event / Deadlocks to look at any deadlocks that do occur.

    CREATE EVENT SESSION [Deadlocks] ON SERVER ADD EVENT sqlserver.xml_deadlock_report
    ADD TARGET package0.event_file(SET filename=N'<path_and_file_of_your_extended_events_deadlock_file>',max_file_size=(50),max_rollover_files=(8))
    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=ON,STARTUP_STATE=ON)
    GO

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • If you use Profiler, and you hit enter after entering a value for database name, you get another box.

    However, you should start to look at Extended Events, as Scott listed above, as Trace is deprecated and doesn't capture events for many items. There is a "Profiler" in SSMS for Extended Events in later versions. For deadlocks, I'd use Scott's session above.

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

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