Post reply

urgent: Extended procedure hanged...blocking other extended procedures to run

  • October 30, 2007 at 10:58 am

    #180715

    There is a extended procedure which got stuck....even though it get killed still in killed/rollback state and stopping other processes...

    stuck procedure name is: xp_availablemedia

    Is there a way i can figure it out which windows process is running behind this and then kill that process to remove this locking from mssql??

    Constraints:

    Using MSSQL 2005 RTM, can't upgrade to 2005

    Restarting MSSQL is not an option...

    Couldn't find anything in google which help...Appricate any help/pointers.....

    Thanks...Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • October 30, 2007 at 11:58 am

    #746799

    Try -

    DBCC xp_availablemedia (free)

    GO

    Tommy

    Follow @sqlscribe

  • October 30, 2007 at 12:58 pm

    #746822

    TommyB (10/30/2007)

    Try -

    DBCC xp_availablemedia (free)

    GO

    wow...looks like it will work....will try and confirm...

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • October 30, 2007 at 1:02 pm

    #746828

    Good luck 🙂

    Tommy

    Follow @sqlscribe

  • October 30, 2007 at 5:31 pm

    #746888

    Prakash Heda (10/30/2007)

    TommyB (10/30/2007)

    Try -

    DBCC xp_availablemedia (free)

    GO

    wow...looks like it will work....will try and confirm...

    Tried but hard luck....this command says........

    "DBCC cannot free the DLL "xp_availablemedia.dll". The DLL is not loaded."

    "Session is still blocking others"

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • October 31, 2007 at 7:19 am

    #747051

    End that external process using Windows Task Manager.

  • October 31, 2007 at 7:27 am

    #747054

    Suresh

    How do ou end the external xp using Task Manager unless u know the process or the program name associated.

    the best part that i could see is check if there is any tape drive attached to your server. if so remove them and then try to kill tht pocess.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • October 31, 2007 at 7:43 am

    #747065

    Sugesh,

    Recently, I was in similar situation. I guessed the process name and ended it using Task Manager.

    It worked.

    Suresh B.

  • October 31, 2007 at 7:51 am

    #747074

    Suresh,

    You gussed and got it right. But what if the questionarrie gets the wrong process killed and hence going for a reboot. Unless we knw the right process name i advice not to do that.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • October 31, 2007 at 7:52 am

    #747075

    You have an OS command to check if there is any deadlock @ the OS level of so go ahead with that. You can get the list of commands in MS site pertaining to the version and series level.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • October 31, 2007 at 11:15 am

    #747181

    Thanks for your inputs...

    I did tried to find out which is that process...i think the name should be svchost....though there are serveral svchosts are running

    I did think about "process explorer" or something like that but its production box and we do not want to install something unless we know it will help...

    thus awaiting for inputs as this should be a common scenerio i think....I faced it many times before but not in production so use to restart sql server as a quick fix....

    but thats not the option i have here....so trying to know how its been fixed before by others in production db's

    Thanks...

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • October 31, 2007 at 11:59 am

    #747199

    Here is a handy tool for this type of situation.

    http://www.dependencywalker.com/

    Hope this helps,

    Phillip Cox

  • October 31, 2007 at 1:11 pm

    #747224

    Phillip Cox (10/31/2007)

    Here is a handy tool for this type of situation.

    http://www.dependencywalker.com/

    Hope this helps,

    Phillip Cox

    Thanks Phillip....I downloaded and tried...seems quite a tool to handle many things...

    tried running profiler on sqlserver.exe in dev environment...it try to start the module... and then get killed for some reason...

    needs more understanding on this...will keep doing research if i could find a way to catch that hung module...

    thanks again...Prakash

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • November 1, 2007 at 2:21 pm

    #747519

    Still open...not able to figure out with this tool....

    anymore ideas or just wait for sql server restart?

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • March 11, 2010 at 5:24 pm

    #1132398

    Have you tried getting the SPID of the process hung using the following command

    select hostprocess from master..sysprocesses where spid=<Process ID>

    Then kill that SPID in windows using task manager..

    Regards

    Frank Fernando

Viewing 15 posts - 1 through 15 (of 15 total)

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