Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a reindex job in a maintenance plan which I run on every table in a given database at 3am so it won't interrupt any other work.

However it is failing and reporting the reason as being the victim of a deadlock on the tables - how can I work out what has locked the tables other than it?

share|improve this question
up vote 2 down vote accepted

Have you got the deadlock trace flags enabled (1222, etc)? These will give you a dump of the deadlock in SQL Server's ERRORLOG file (normally in the logs directory) and thus should help you get to the bottom of it. See for more details.

It can be enabled for the current service with DBCC TRACEON (-1, 1222). However if you want the traceflag to be a permanent change then you'll need to put the traceflag onto SQL Server's startup parameters (-T1222) and then stop and restart SQL Server.

share|improve this answer
using the 'DBCC TRACEON (-1, 1222)' method, will trace flag be enabled until the service is next restarted? – Richard Jul 15 '11 at 12:50

You can also create an agent job that captures the deadlock graph. see:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.