October 27, 2003 at 2:30 pm
I have a SQL Server 7.0 that is having a bit of a problem. I have a PID doing a backup that has been hung for three days now. I cannot kill this process with Enterprise Manger or with Kill commands in Query Analyzer. It will not die.
The process is owned by 'sa'. It was kicked off by ArcheServes SQL Agent. The last bit of SQL that it ran was: siebel_db_prod to pipe = '\\.\pipe\dbasql70\dbagent0s0'
I am caught in a bit of a catch 22 here. My transaction log is full and I cannot truncate it or back it up, nor can I back up the database, all because of this backup command issued by a PID that I cannot Kill.
Does anyone know of a way to kill a process that the Kill command will not kill? HELP!
Any ideas would be appreciated.
Thanks;
Trea
October 27, 2003 at 2:38 pm
Unfortunately, Kill really only sets a flag for a spid to "KIll yourself" If it never comes back to see its "Kill yourself" flag set, it will just sit there.
Usually for true orphans cycling SQL Service is really your only recourse.
October 30, 2003 at 4:16 pm
I had the same problem and the only solution I found was to restart SQL Server, because system process (like backup) cannot be directly killed.
------------------------
Thiago Santinon
thisant@pop.com.br
ICQ# 4273335
------------------------
November 3, 2003 at 7:31 am
Thanks for the replies everyone. I ended up resolving the problem myself, but I thought that I'd post the details here incase someone else ran across this.
PROBLEM:
ArchServ kicked off a backup job to tape on Saturday. The drive has only a single DAT tape and slot. This tape ran out of room and the job halted. A message box more than likely popped up on the server running ArchServ and the tape drive warning of this. ArchServ is running on a separate server from the database.
This message would have been seen come Monday morning and a fresh tape inserted to finish the job if it wasn't for the GPF that caused the ArchServ server to reboot Sunday afternoon. As far as we can tell the GPF had nothing to do with the backup job.
Since the actual job that was still hung on the database server was kicked off locally by the ArchServ service agent the PID was not timed out and killed. Of course I couldn't KILL a PID that was in "the middle of a backup" so I was stuck in the "Catch 22" described above.
RESOLUTION:
Strangely enough, all it took to KILL the PID was to add an additional 500 MBs to the Transaction Log. Before I added that 500 MBs there was already 300 MBs free space in that same Transaction Log which I had freed up by removing some files from the drive where said logfile was located. But I guess the PID needed a total of 800 MBs free before I could KILL it. In the end all it took was for me to issue the KILL (SPID) for the 30th, and last, time to kill the process. I was then able to backup the Transaction Log.
Thanks again for the speedy replies everyone! I love this web site, especially for the community of fellow DBAs. Y'all are wonderful.
Many, many thanks;
Trea Johnson
DBA
Birmingham, AL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply