enable profiler / Traceon after SQL server restart automatically

Question

hi,

My client is facing some deadlocks while using our application. I want to track all the deadlocks for my research and to solve the deadlocks.

i am currently run the SQL profiler for the event deadlock graph to capture the deadlock scenario.

the actual problem is that the SQL server gets restarted every day at 2 am, and the profiler stops capturing the events after the restart. by the time i come to office at start the profiler at say 10 am, there could be deadlocks which i could have missed between 2 am and 10 am. so i am looking for a way so i can capture the deadlocks without me starting manually.

i thought i could use TRACEON(1204,-1) so that the deadlock events get captured in the SQL Server error logs. But i found that the TRACE capturing too gets disabled after the restart.

Is there a way i can capture the deadlocks either by SQL profiler or by using TRACEON without me manually starting the capturing?

Nikhil

Answer 1

Rather than running the trace or worrying about what is missed between those specific hours after downtime, ask the client what error the application returns. If they are at a specific area within the application you should be able to track down the statements that are causing the deadlock. Just my opinion =).