Post reply

Index creation blocking SP2 !

  • 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)

  • 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

  • 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)

  • 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)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply