April 19, 2021 at 5:06 pm
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
April 19, 2021 at 6:04 pm
Personally, I'd check the SQL Server Error Logs for deadlock events.
--Jeff Moden
April 19, 2021 at 9:58 pm
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
April 22, 2021 at 2:18 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply