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: 1004324 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: 101096 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736173 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.