Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

2 Answers

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 http://msdn.microsoft.com/en-us/library/ms178104(v=SQL.90).aspx 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
add comment

You can also create an agent job that captures the deadlock graph. see: http://msdn.microsoft.com/en-us/library/ms186385%28v=SQL.90%29.aspx

share|improve this answer
add comment

Your Answer

 
discard

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.