Post reply

spid 59 (blocking) and spid 9 (blocked by 59)

  • September 19, 2007 at 5:49 am

    #70674

    My users are trying to use a program (created by another ex-employee - source code unavailable!!) and getting a Timeout error, so I decided to try and investigate.

    I'm no database administrator, so I have no idea what's going on here, but there are a couple of entries (as mentioned in the title) under the Locks/Process ID view in SQL Server Manager.

    When I run the sp_who stored proc, SPID 9 states AUTOSHRINK as the cmd and SPID 59 states KILLED/ROLLBACK

    When I try to look at the contents of one of the tables in the database, I eventually get a timeout...this table only has 88 records in it. So I'm making the assumtion that these SPIDs are locking the table.

    Amd I correct? If so, is there anyway to remove them so I can free the table/database?

    I appreciate all replies.

    Thx

    Billy

  • September 19, 2007 at 7:09 am

    #734568

    Spid 9 is involved in an autoshrink operation.

    This is general a 'bad idea' to have turned on, so you should consider to turn this option off for this database.

    Spid 59 is in rollback mode. This is a state that we can't do anyting about other than wait for it to finish.

    Should this turn out to be a zombie-spid (one that seems to have frozen) then afaik the only resort to get rid of it is to restart SQL Server.

    /Kenneth

     

  • September 19, 2007 at 7:12 am

    #734571

    Thanks Kenneth

    As far as Autoshrink goes, could you elaborate (if you have time) why it's a bad idea?

    Thx

  • September 19, 2007 at 7:26 am

    #734584

    It boils down to this, what shrinks also has to grow.  If your database is constantly growing and shrinking, it impacts system performance.  This may not be an issue for a desktop application using a local copy of MSDB or SQL Server 2005 Express Edition, but can severly impact the performance of a LOB application used by many users.

  • September 19, 2007 at 7:31 am

    #734588

    Nice one

    Thx very much. I'll keep that in mind.

  • September 19, 2007 at 11:55 am

    #734676

    You can use  DBCC INPUTBUFFER (59) to get a bit more information about what a spid is doing.

  • September 20, 2007 at 1:43 am

    #734846

    As an update

    SPID 59 would not finish, and therefore neither would SPID 9

    I restarted SQLServer services and they were gone. I then removed the AutoShrink option

    Thx for the help.

    Billy

Viewing 7 posts - 1 through 6 (of 6 total)

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