Enable Trace In SQL Server To View The Deadlock

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s