Profiler Trace

Question

Post reply

Profiler Trace

sizal0234

SSCrazy

Points: 2272
More actions
April 19, 2021 at 5:06 pm

#3873174

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

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

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

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 1

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.

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

ScottPletcher SSC Guru Points: 100948 More actions · Answer 2

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) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 3

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.