Today I have been reported a problem in a SQLServer database, a process has failed, of course, it dead returning a deadlock error. The first problem I’ve found is that the trace was not activated to keep the record of what happened to a greater level of detail. Generally is good save or register such errors. Based in my experience in the 90% of cases is problem of designing the application. Normally speaking with people development and passing the information captured in the trace is more than enough to solve it.
To activate the trace and generate the trace when the problem occurs we execute the following code, remember that it is global, you can not activate a session level;
-- All together ---- DBCC TRACEON (1204, 1222) GO -- Other way is one to one ---- DBCC TRACEON (1204) GO DBCC TRACEON (1222) GO
This will make write the system log.
HTH – Antonio NAVARRO