2

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior

More information on the trace flags are here

DBCC TRACESTATUS

this above, is the command that shows all the trace flags that are currently running on my system.

enter image description here

I have 2 questions:

1) is there any other way to find the list of trace flags that are currently running, other than DBCC TRACESTATUS?

2) It can be seen on the picture above the trace flag 1222 is active but I am not receiving any email when a deadlock occurs. What else needs to be done?

Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300
  • 1
    Trace Flag 1222 does not send an email when a deadlock occurs, it logs more information in the Log. http://www.brentozar.com/archive/2014/06/capturing-deadlock-information/ – Mark Sinkinson Sep 07 '15 at 15:12
  • You must now rely on extended events trace to capture deadlock information. Actually it will automatically capture. With T- 1222, it might miss some deadlock information – Shanky Sep 07 '15 at 15:50
  • @Shanky I got it here: http://dba.stackexchange.com/questions/114387/what-are-the-costs-of-enabling-extended-events – Marcello Miorelli Sep 07 '15 at 16:10

1 Answers1

5

The reason the server wasn’t sending emails is due to this flag not being set:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails. You can see the table here:

select * from master.sys.messages
where text like '%deadlock%'
Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300