2

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?

Richard
  • 105
  • 1
  • 2
  • 16

2 Answers2

2

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.

Chris J
  • 1,218
  • 18
  • 32
  • 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
0

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

Dee Yu
  • 88
  • 6