October 30, 2007 at 10:58 am
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
October 30, 2007 at 12:58 pm
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
Good luck 🙂
Tommy
Follow @sqlscribeOctober 30, 2007 at 5:31 pm
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
End that external process using Windows Task Manager.
October 31, 2007 at 7:27 am
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
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
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
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
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
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
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
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
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