Index creation blocking SP2 !

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  May 10, 2007 at 3:27 am

  #117185

  Hi All,

   

  I keep noticing this problem with SQL 2005 SP2, whenever I’m creating an index, there is always another process blocking the create index statement, details of the process is as follows:

  SPID

  Status

  Login

  HostName

  BlkBy

  DBName

  Command

  CPUTime

  DiskIO

  LastBatch

  ProgramName

  SPID

  REQUESTID

  76

  SUSPENDED

  NT AUTHORITY\SYSTEM

  DELLCN05

  59

  Personprofile

  SELECT

  31

  69

  05/09/2007 09:16

  SQLDMO_1

  76

  0

   

  When you do dbcc inputbuffer(76), it shows that the statement is sp_spaceused..

   

  Anyone know how I can get rid of this problem.

  Thanks

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  May 10, 2007 at 10:49 am

  #705578

  find the PC/server and see what they are doing or script the index and change it online = on

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  May 13, 2007 at 3:13 pm

  #706069

  Hi

  I have checked and all indexes, and they all seem to be set to "ONLINE = OFF" 

  Do you reckon that all indexes be set = ON to stop this problem ?

  Thanks in advance

   

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  May 14, 2007 at 10:18 am

  #706280

  I am having the problem now and again:

  This time, it appears its a backgroud process blocking my query:

  SET STATISTICS XML OFF

  Thats all I see after running dbcc inputbuffer(51)

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)